Convert SQLERRM( SQLCODE ) in Oracle to PostgreSQL

Question

I'm converting Oracle to PostgreSQL, I haven't alternative for SQLERRM( SQLCODE ) like Oracle in PostgreSQL.

DECLARE
       name employees.last_name%TYPE;
       v_code NUMBER;
       v_errm VARCHAR2(64);
    BEGIN
       SELECT last_name INTO name FROM employees WHERE employee_id = 1000;
       EXCEPTION
          WHEN OTHERS THEN
             v_code := SQLERRM( SQLCODE );
             v_errm := SUBSTR(SQLERRM, 1 , 64);
             DBMS_OUTPUT.PUT_LINE('The error code is ' || v_code || '- ' || v_errm);
    END;

It is bad practice to catch OTHERS. Catch the exceptions you expect to occur (such as NO_DATA_FOUND or TOO_MANY_ROWS) and then let the code fail on the unexpected errors so that it can be debugged and so that any subsequent code does not have expectations that will not have been met if this code silently fails. — MT0
– MT0, Commented Dec 16, 2021 at 10:29

Laurenz Albe · Accepted Answer · 2021-12-16 10:44:24Z

Look at the documentation:

Within an exception handler, the special variable SQLSTATE contains the error code that corresponds to the exception that was raised (refer to Table A.1 for a list of possible error codes). The special variable SQLERRM contains the error message associated with the exception. These variables are undefined outside exception handlers.

Within an exception handler, one may also retrieve information about the current exception by using the GET STACKED DIAGNOSTICS command, which has the form:

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

Each item is a key word identifying a status value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are shown in Table 43.2.

So that could be

v_code := SQLSTATE;
v_errm := substr(SQLERRM, 1, 64);

Collectives™ on Stack Overflow

Convert SQLERRM( SQLCODE ) in Oracle to PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related