SQLERRM and SQLCODE IN ORACLE SQL/PLSQL

SQLERRM and SQLCODE are two functions we can use for trapping the error-message and error-code in the exception handler when any exception is raised.  They cannot be used directly in a print statement.  They have to be stored in a variable and the variable is used for the print statement.

CREATE TABLE TEMP (ENAME VARCHAR2 (25), SOMENO NUMBER);

DECLARE

pe_ratio NUMBER(3,1);

A VARCHAR2(100);

B NUMBER;

NAME VARCHAR2(25);

BEGIN

NAME:='&NAME';

SELECT SAL/NVL(COMM, 0) INTO pe_ratio FROM EMP WHERE ENAME = NAME;

     INSERT INTO TEMP (ENAME, SOMENO) VALUES (NAME, pe_ratio);

     EXCEPTION

     WHEN No_data_found THEN

     A:=SQLERRM;

     B:=SQLCODE;

     dbms_output.put_line('Error number = ' || B);

     dbms_output.put_line('Error message = ' || A);

     WHEN ZERO_DIVIDE THEN

   A:=SQLERRM;

   B:=SQLCODE;

  INSERT INTO TEMP (ENAME, SOMENO) VALUES (NAME, pe_ratio);

  dbms_output.put_line('Error number = ' || B);

  dbms_output.put_line('Error message = ' || A);

  END;

 /

Enter value for name: SCOTT

old   7: NAME:='&NAME';

new   7: NAME:='SCOTT';

Error number = -1476

Error message = ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

/

Enter value for name: KUMAR

old   7: NAME:='&NAME';

new   7: NAME:='KUMAR';

Error number = 100

Error message = ORA-01403: no data found

PL/SQL procedure successfully completed.

/

Enter value for name: MARTIN

old   7: NAME:='&NAME';

new   7: NAME:='MARTIN';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEMP;

ENAME                         SOMENO

------------------------- ----------

SCOTT

MARTIN                            .9