Errors and messages v16
Reporting messages
Use the DBMS_OUTPUT.PUT_LINE
statement to report messages:
DBMS_OUTPUT.PUT_LINE ( <message> );
Where message
is any expression evaluating to a string.
This example displays the message on the user’s output display:
DBMS_OUTPUT.PUT_LINE('My name is John');
The special variables SQLCODE
and SQLERRM
contain a numeric code and a text message, respectively, that describe the outcome of the last SQL command issued. If any other error occurs in the program such as division by zero, these variables contain information pertaining to the error.
SQLCODE and SQLERRM functions
SQLCODE and SQLERRM functions are now available in EDB Postgres Advanced Server.
In an exception handler, the SQLCODE
function returns the numeric code of the exception being handled. Outside an exception handler, SQLCODE
returns 0
.
The SQLERRM
function, returns the error messaage associated with an SQLCODE
variable value. If the error code value is passed to the SQLERRM
function, it returns an error message associated with the passed error code value, regardless of the current error raised.
A SQL statement can't invoke SQLCODE
and SQLERRM
functions.
Examples:
declare l_var number; begin l_var:=-1476; dbms_output.put_line(sqlerrm(l_var::int)); l_var:=0; dbms_output.put_line(sqlerrm(l_var::int)); l_var:=12; dbms_output.put_line(sqlerrm(l_var::int)); l_var:=01403; dbms_output.put_line(sqlerrm(l_var::int)); end;
division_by_zero normal, successful completion message 12 not found message 1403 not found
DECLARE Balance integer := 24; BEGIN IF (Balance <= 100) THEN Raise_Application_Error (-20343, 'The balance is too low.'); END IF; exception when others then dbms_output.put_line('sqlcode ==>'|| sqlcode); dbms_output.put_line('sqlerrm ==>'|| sqlerrm); dbms_output.put_line('sqlerrm(sqlcode) ==>'|| sqlerrm(sqlcode)); END;
sqlcode ==>-20343 sqlerrm ==>EDB-20343: The balance is too low. sqlerrm(sqlcode) ==>EDB-20343: The balance is too low.
- On this page
- Reporting messages
- SQLCODE and SQLERRM functions