Error handling in PLSQL – SQLERRM and SQLCODE

Error Handling / Exception Handling in Plsql

While named or anonymous plsql block execution, will fail because of some error in between of execution because of dml error. We can capture the error and can also handle them by adding "EXCEPTION" block inside the plsql block, which leads to the successful execution of plsql block.

declare
    v_var varchar2(1000);
    TABLE_MISSING EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_MISSING,-942);
begin
    execute immediate ' select count(*) from v$datafile' into v_var;
    dbms_output.put_line(v_var);
    EXCEPTION
        WHEN TABLE_MISSING THEN
        NULL;
end;
/

We can also print the error or can store them into the variable, and can use them for further investigation. For print the error we need to use SQLCODE and SQLERRM funtion

declare
    v_var varchar2(1000);
    TABLE_MISSING EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_MISSING,-942);
begin
    execute immediate ' select count(NAME) from v$datafile' into v_var;
    dbms_output.put_line(v_var);
    EXCEPTION
        WHEN TABLE_MISSING THEN
        DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || SQLERRM );
        raise;
end;
/