SQL Reference - SQLSTATE

Example

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
    @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    SET @full_error = CONCAT('ERROR', @errno, ' (', @sqlstate, '): ', @text);
    SELECT @full_error;
    ROLLBACK;
END;

Output

ERROR1146 (42S02): Table 'test.non_existant_table' doesn't exist

Explanation

In this code block, a SQL exception handler is declared. This handler is supposed to act when a SQL error occurs. The GET DIAGNOSTICS STATEMENT retrieves information about errors. The details of the error are stored in the variables @sqlstate, @errno, and @text. The error message is then composed by concatenating the message components and assigned to the @full_error variable. The error message (@full_error) is returned with a SELECT statement. After the error message is returned, the transaction is rolled back with ROLLBACK. This ensures that partial transactions resulting from an error are not committed to the database.

Example

DO $$
BEGIN
  RAISE NOTICE 'An example of SQLSTATE.';
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'The SQLSTATE was: %', SQLSTATE;
END $$;

Output

NOTICE:  An example of SQLSTATE.
NOTICE:  The SQLSTATE was: 00000

Explanation

The provided SQL code is an anonymous code block that raises a notice in PostgreSQL with the message ‘An example of SQLSTATE’. The EXCEPTION block is designed to handle any exceptions that are raised within the BEGIN and END block, capturing the SQLSTATE of the exception. However, because no explicit error is raised, the OTHERS exception handler captures the success SQLSTATE, which is ‘00000’ in PostgreSQL.

Example

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    -- Get information about the error
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Output

ErrorNumber    ErrorSeverity   ErrorState ErrorProcedure ErrorLine ErrorMessage
8134           16              1          NULL           3         Divide by zero error encountered.

Explanation

The example demonstrates SQLSTATE usage by purposely generating a divide-by-zero error. The error is caught in the CATCH block where error information is then selected and returned. The ERROR_STATE() function specifically retrieves the state number of the error.

Example

DECLARE
    example_number NUMBER := 100;
BEGIN
    IF example_number < 100 THEN
        IF SQLCODE != 0 THEN
           dbms_output.put_line('SQLCODE: '||SQLCODE);
           dbms_output.put_line('SQLERRM: '||SQLERRM);
        END IF;
    END IF;
END;

Output

Statement processed.

Explanation

The code above is a basic example of how the SQLCODE and SQLERRM functions can be used in Oracle SQL to return the SQLSTATE status. However, as there is no error in this code, no output related to SQLSTATE occurs. The SQLCODE function returns the numeric code of the exception and the SQLERRM function returns the message associated with the exception. SQLSTATE is a standard set of error codes for SQL that are returned in these kinds of situations (errors, warnings, etc.).