SQL SQLCODE

What is SQLCODE in SQL?

SQLCODE returns the numeric status code of the most recently executed SQL statement within the current session or PL/SQL block.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SQLCODE: Oracle Database, Oracle Autonomous Database, IBM Db2 LUW, IBM Db2 z/OS, IBM Informix. Not supported in MySQL, PostgreSQL, SQL Server, or SQLite.

SQL SQLCODE Full Explanation

SQLCODE is a built-in context function (Oracle) or host variable (IBM Db2, Informix) that exposes the return code from the last SQL operation. A value of 0 indicates success, a positive value signals a warning, and a negative value represents an error. In Oracle PL/SQL, SQLCODE is primarily referenced inside EXCEPTION blocks to capture the exact error number so it can be logged, re-raised, or handled conditionally. The function only reflects the most recent SQL statement in the same session or call stack and resets after each new statement. Unlike SQLERRM, which returns the full error message, SQLCODE returns only the numeric code, making it ideal for programmatic branching. SQLCODE does not work in pure SQL; it is evaluated in procedural code or embedded SQL. Outside an error context, Oracle returns 0, while Db2 preserves the last status even when it is positive or warning. Because SQLCODE is vendor-specific, portable applications should favor the ANSI-standard SQLSTATE or GET DIAGNOSTICS where available.

SQL SQLCODE Syntax

-- Oracle PL/SQL
SQLCODE

-- Db2 embedded SQL (C/COBOL)
:SQLCODE

SQL SQLCODE Parameters

Example Queries Using SQL SQLCODE

-- Oracle example 1: Log error code
DECLARE
  err_code NUMBER;
BEGIN
  INSERT INTO users(id) VALUES (1); -- assume PK violation
EXCEPTION
  WHEN OTHERS THEN
    err_code := SQLCODE; -- captures -1 for ORA-00001
    INSERT INTO error_log(error_no,logged_at) VALUES (err_code,SYSTIMESTAMP);
    RAISE; -- rethrow if needed
END;
/

-- Oracle example 2: Conditional retry on deadlock
DECLARE
  err_code NUMBER;
  tries    NUMBER := 0;
BEGIN
  LOOP
    BEGIN
      UPDATE accounts SET balance = balance - 100 WHERE id = 10;
      COMMIT;
      EXIT; -- success
    EXCEPTION
      WHEN OTHERS THEN
        err_code := SQLCODE;
        IF err_code = -60 AND tries < 3 THEN -- ORA-00060 deadlock detected
          tries := tries + 1;
          DBMS_LOCK.SLEEP(1);
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
END;
/

Expected Output Using SQL SQLCODE

SQLCODE evaluates to the numeric Oracle error code (negative on error) or 0 on success
In the first example, err_code will hold -1 and an entry is written to ERROR_LOG
In the second example, the block retries up to three times if SQLCODE equals -60

Use Cases with SQL SQLCODE

Error logging inside PL/SQL or stored procedures
Conditional logic based on specific Oracle error numbers (e.g., deadlock retry, duplicate suppression)
Debugging and auditing failures in batch jobs
Mapping Oracle error codes to user-friendly messages in application middleware
Detecting warnings or successful completion codes in Db2 host applications

Common Mistakes with SQL SQLCODE

Calling SQLCODE in plain SQL outside procedural context (returns ORA-00904 invalid identifier)
Assuming SQLCODE persists across autonomous transactions
Confusing SQLCODE (numeric) with SQLERRM (message) or SQLSTATE (ANSI)
Forgetting that positive SQLCODE values are warnings, not errors, in Db2
Using SQLCODE for portability across databases

First Introduced In

Oracle Database 6 (PL/SQL 1.0) and IBM DB2 2.3 (SQLCA)

Frequently Asked Questions

What value does SQLCODE return on success?

It returns 0, indicating the last SQL statement completed without errors.

Can I use SQLCODE in a SELECT statement?

No. SQLCODE is evaluated only in procedural contexts like PL/SQL blocks or host languages with embedded SQL. Referencing it in standalone SQL raises an error.

How do SQLCODE and SQLERRM differ?

SQLCODE provides just the numeric error code. SQLERRM returns the corresponding message text. Use SQLCODE for branching logic and SQLERRM for logging or user messages.

Is SQLCODE available in PostgreSQL or MySQL?

No. Those systems use the ANSI-standard SQLSTATE and GET DIAGNOSTICS for error handling. SQLCODE is specific to Oracle, Db2, and Informix.