Как получить значение SQLSTATE в обработчике EXCEPTION?

Question

В этой главе документации говорится, что Oracle полностью поддерживает E171, SQLSTATE.

В PostgreSQL можно прочитать стандартный код ошибки через переменную SQLSTATE (на db<>fiddle):

DO $$
DECLARE
    v_1 int;
BEGIN
    v_1 := 1/0;
EXCEPTION WHEN DIVISION_BY_ZERO THEN 
    RAISE EXCEPTION '<%> - % ', SQLSTATE, SQLERRM;
END;
$$

Но в Oracle (на db<>fiddle) эта переменная не известна:

BEGIN
    DBMS_OUTPUT.PUT_LINE(1/0);
EXCEPTION WHEN ZERO_DIVIDE THEN 
    DBMS_OUTPUT.PUT_LINE('Error: '||SQLSTATE||' - '||SQLERRM);
END;
/
ORA-06550: line 4, column 59:
PLS-00201: identifier 'SQLSTATE' must be declared

SQLCODE, который объявлен устаревшим в SQL-92, работает в Oracle:

BEGIN
    DBMS_OUTPUT.PUT_LINE(1/0);
EXCEPTION WHEN ZERO_DIVIDE THEN 
    DBMS_OUTPUT.PUT_LINE('Error: '||SQLCODE||' - '||SQLERRM);
END;
/
dbms_output: Error: -1476 - ORA-01476: divisor is equal to zero

Как получить SQLSTATE в Oracle?

_{Свободный перевод вопроса Can I get SQLSTATE value in an EXCEPTION handler in Oracle database? от участника @iwis}

Answer 1

Документация на самом деле не очень понятна, но верху по ссылке, написано:

Oracle Database server, release 12.2

OTT (Oracle Type Translator), release 12.2

Pro*C/C++, release 12.2

Pro*COBOL, release 12.2

Первый пункт означает, что БД Oracle поддерживает SQLSTATE, а остальные 3-и пункта являются клиентами SQL. В вопросе используете клиент PL/SQL, которого нет в списке. К сожалению, распространенные SQL-клиенты Oracle, такие как PL/SQL, SQL*Plus и SQLcl, похоже, не поддерживают переменную SQLSTATE. Однако, драйверы для других языков, например: JDBC, ODBC, включают ее.

Если действительно нужны коды SQLSTATE в PL/SQL, то рекомендуемым решением будет, написать функцию мапирования SQLCODE и SQLSTATE, например, на основе этой таблицы.

_{Свободный перевод ответа от участника kfinity}

Answer 2

Через JDBC соединение можно получить SQLSTATE методом getSQLState класса SQLException.

Исходник Java калсса:

create or replace and compile java source named testsqlstate AS
import java.sql.*;

public class TestSQLState
{
    public static String exec (String qry) throws SQLException {
        try {
            Connection conn = DriverManager.getConnection ("jdbc:default:connection:");
            PreparedStatement ps = conn.prepareStatement (qry);
            ResultSet rs = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            return e.getSQLState();
        }
        return null;
    }
}
/

Его надо обернуть в PL/SQL функцию:

create or replace function getsqlstate (qry varchar2) return varchar2
as language java name 'TestSQLState.exec (java.lang.String) return java.lang.String';
/

Вызов и результат:

exec dbms_output.put_line ('result='||getsqlstate ('select 1/0 from dual'));

result=22012

_{Свободный перевод ответа от участника MT0}

БЛОГ НА HUSL

Как получить значение SQLSTATE в обработчике EXCEPTION?

Ответы (2 шт):