Monday, July 04, 2005

SQLCODE and SQLERRM in INSERTs

Here is an interesting foible. Normally you can build strings on the fly from functions and insert them into a table. Like so:

CREATE TABLE LogTable (logString VARCHAR2(128));

CREATE OR REPLACE FUNCTION MyFunc RETURN VARCHAR2
AS
BEGIN
RETURN 'Hello';
END;

CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (MyFunc || ' ' || MyFunc);
END;

But you can't do this with SQLCODE and SQLERRM.

CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (SQLCODE || ' ' || SQLERRM);
END;

PL/SQL: ORA-00984: column not allowed here

You can always put the values in a string for a workaround.

CREATE OR REPLACE PROCEDURE MyProc
AS
errString LogTable.logString%TYPE;
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
errString := SQLCODE || ' ' || SQLERRM;
INSERT INTO LogTable (logString) VALUES (errString);
END;

Procedure created.

For reference, look up SQLCODE and SQLERRM in the PL/SQL User's Guide and Reference.

Bonus. I found the following blog while reviewing OraBlogs:
http://blog.niftypaint.nl/

Which pointed me to Eddie Awad's great blog:
http://awads.net/wp/

Enjoy!

Comments:
Same for update's too.

So as usual has to code like below

l_error_status := SQLERRM;

UPDATE temp_tanmoy_271799
SET update_status = 'N',
error_status = l_error_status
WHERE ee# = my ee;
 
good work, and thank you to google for helping me find stuff like this
 
This comment has been removed by a blog administrator.
 
And, for those of you who want to keep the error code in a numeric format, try to multiply it by one, or add zero to it ;-)
ie (in an exception block):

declare
le_errcode number := 0;
begin
le_errcode := sqlcode * 1;
insert into err_log(le_errcode,dbms_utility.format_error_stack);
commit;
end;
 
this article is really a good one AND ITS A EXPERIMENTAL RESULT.
I WANT SOME NOTE ON THESE TWO FUNCTIONS.WIIL U HELP?
 
Sorry, ambit. No one wants to help with your EXPERIMENTAL RESULT. Maybe you should try offering a cash reward.
 
Thanks! This saved me time on researching.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?