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!