Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Capture complete error message

vpolasaMar 11 2025 — edited Mar 11 2025

Scenario:

I tried to insert a null record into a not null column. I get below error:

CREATE TABLE testnull (col1 NUMBER NOT NULL);
INSERT INTO testnull(col1) VALUES (NULL);
Error starting at line : 2 in command -
INSERT INTO testnull(col1) VALUES (NULL)
Error at Command Line : 2 Column : 36
Error report -
SQL Error: ORA-01400: cannot insert NULL into ("TESTSCHEMA"."TESTNULL"."COL1")
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:    An attempt was made to insert NULL into previously listed objects.
*Action:   These objects cannot accept NULL values.

From below code, SQLERRM displays just the ORA message. Is there a way to capture complete error message above (including Cause and Action)?

SET SERVEROUTPUT ON
BEGIN
    insert into testnull(col1) values (null);
EXCEPTION
    WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Using:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Windows 11 Enterprise

This post has been answered by Frank Kulash on Mar 11 2025
Jump to Answer
Comments
Post Details
Added on Mar 11 2025
3 comments
170 views