Can I get SQLSTATE value in an EXCEPTION handler in Oracle database?

user_4592248 0
Viewed 326

This Oracle documentation page says that Oracle fully supports E171 SQLSTATE.

In PostgreSQL I can read this Standard SQL SQLSTATE variable (see it in db<>fiddle):

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

But when I try to read it in Oracle (see it in db<>fiddle):

BEGIN
  DBMS_OUTPUT.PUT_LINE(1/0);
EXCEPTION
  WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLSTATE || ' - ' || SQLERRM);
END;
/

it isn't recognized:

ORA-06550: line 4, column 59:
PLS-00201: identifier 'SQLSTATE' must be declared
ORA-06550: line 4, column 25:
PL/SQL: Statement ignored

The SQLCODE, deprecated by SQL-92, works in Oracle:

BEGIN
  DBMS_OUTPUT.PUT_LINE(1/0);
EXCEPTION
  WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

I get:

dbms_output: Error: -1476 - ORA-01476: divisor is equal to zero

How can I obtain SQLSTATE in Oracle?

plsql oracle
2 Answers

The documentation isn't really very clear about this, but at the top in your link, it says:

Oracle Database server, release 12.2
OTT (Oracle Type Translator), release 12.2
Pro*C/C++, release 12.2
Pro*COBOL, release 12.2

The first one means that the Oracle database supports SQLSTATE, and the other 3 are SQL clients. But you're using the PL/SQL client, which isn't listed. Unfortunately, common Oracle SQL clients like PL/SQL, sql*plus, and sqlcl don't seem to support the SQLSTATE feature. Drivers for other languages (JDBC, ODBC) often include it, however.

If you really need SQLSTATE codes in PL/SQL, I'd suggest writing a function to map the Oracle SQLCODEs to SQLSTATEs, based on this Oracle table.

user_30618520

Connect via JDBC and when you get an exception use the SQLException class's getSQLState method.

You can do this internally to Oracle using CREATE JAVA SOURCE:

CREATE AND COMPILE JAVA SOURCE NAMED test_sqlstate_java AS
import java.sql.*;

public class TestSQLState
{
  public static String test (int numerator, int denominator) throws SQLException
  {
    String sql = "SELECT ?/? FROM DUAL";
    try
    {
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, numerator);
      pstmt.setInt(2, denominator);
      ResultSet rset = pstmt.executeQuery();
      pstmt.close();
      
    }
    catch (SQLException e) 
    {
      return e.getSQLState();
    }
    return null;
  }
}

Then a PL/SQL wrapper function:

CREATE FUNCTION test_sqlstate( numerator IN NUMBER, denominator IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'TestSQLState.test( int, int ) return java.lang.String';
/

Then you can call it as any SQL function:

SELECT test_sqlstate( 1, 0 ) AS sqlstate FROM DUAL;

Which outputs:

| SQLSTATE |
| :------- |
| 22012    |

db<>fiddle here

user_15092640
Related