SQL CODE AND SQLERRM IN ORACLE PL/SQL

 What is Exception?

   Ø Exceptions are run time errors that are raised in the PLSQL block. 

Definition of SQLCODE:

   Ø SQLCODE returns the error number associated with the most recently raised exception.

Definition of SQLERRM(SQL Error Message):

   Ø SQLERRM returns the error message associated with the recently raised SQLCODE.

Example:

1. DECLARE

      p_job   VARCHAR2 (50);

   BEGIN

      SELECT job

        INTO p_job

        FROM emp

       WHERE job = 'MANAGER';

   END;

Result will be,

PLSQL block results shows error code with message, Which is known as Runtime Error.

PLSQL block with exception handling:

1. DECLARE

      p_job   VARCHAR2 (50);

   BEGIN

      SELECT job

        INTO p_job

        FROM emp

       WHERE job = 'MANAGER';

   Exception

   when others then

    Dbms_output.put_line(SQLCODE);   

   END;

Now the result will be,

We can able to get error code.

SQLERRM:

1. DECLARE

      p_job   VARCHAR2 (50);

   BEGIN

      SELECT job

        INTO p_job

        FROM emp

       WHERE job = 'MANAGER';

   Exception

   when others then

    Dbms_output.put_line(SQLCODE);   

    Dbms_output.put_line(SQLERRM);

   END;

The result will be,

• No need to declare any variables just give the Exact name (SQLERRM)
• Now we get SQLCODE with error message in the output block.

Example: 2

v If you want to store a error message in the log table and feel like message is too long then we can use substr() as per the column in the log table.

1.   DECLARE

      p_job   VARCHAR2 (50);

     BEGIN

      SELECT job

        INTO p_job

        FROM emp

       WHERE job = 'MANAGER';

     Exception

     when others then

    Dbms_output.put_line(SQLCODE);   

    Dbms_output.put_line(substr(SQLERRM,1,50));

    END;

Now, the length of the error message is reduced.

Share: