CODEWITHSUNDEEP
postgresqloracle-database
Henry
Henry

Reputation: 13

Convert SQLERRM( SQLCODE ) in Oracle to PostgreSQL

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;

Upvotes: 0

Views: 1200

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246533

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);

Upvotes: 2

Related Questions