Oracle 9i 9.2 – linux – documentation about the PL/SQL RAISE_APPLICATION_ERROR:

message is a character string up to 2048 bytes long

Unfortunately SQLERRM only returns the first 510 characters of it…

but we are lucky DBMS_UTILITY.FORMAT_ERROR_STACK at least returns the first 1899 characters of it…

Of course we are on a Unicode database, were a character might take more than 1 byte, but the 510 characters are independend of the real message string…. even with stupid “A” characters :-(

Thanks again Oracle for cutting off our error messages!

repcase:

DECLARE
vErrorStack VARCHAR2;
vCallStack VARCHAR2;

BEGIN
RAISE_APPLICATION_ERROR(-20123, RPAD, FALSE);

EXCEPTION WHEN OTHERS THEN
vErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
vCallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
DBMS_OUTPUT.Put_Line(’*** exception was raised:’ );
DBMS_OUTPUT.Put_Line(’ sqlcode=’ || SQLCODE );
DBMS_OUTPUT.Put_Line(’ sqlerrm=”’ || SQLERRM, 1, 20">SUBSTR || ‘”’);
DBMS_OUTPUT.Put_Line(’ length(sqlerrm)=’ || LENGTH );
DBMS_OUTPUT.Put_Line(’ error_stack=”’ || SUBSTR || ‘”’);
DBMS_OUTPUT.Put_Line(’ length(error_stack)=’ || LENGTH );
DBMS_OUTPUT.Put_Line(’ call_stack=”’ || SUBSTR || ‘”’);
DBMS_OUTPUT.Put_Line(’ length(call_stack)=’ || LENGTH );

END;

produces:

• exception was raised:

sqlcode=-20123
sqlerrm=“ORA-20123: AAAAAAAAA”
length(sqlerrm)=510
error_stack=“ORA-20123: AAAAAAAAA”
length(error_stack)=1899
call_stack=”——- PL/SQL Call St”
length(call_stack)=120