SQLCODE and SQLERRM - Oracle

Those two are called as error reporting functions in PL/SQL. SQLCODE and SQLERRM give the code and error message respectively. Those values can be directly used within the PL/SQL program during the handling of exceptions. The following example demonstrates this.

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line('Name: '||v_ename1);

Exception
  When no_data_found then
    dbms_output.put_line('Employee not found with '||v_sal1);
  When others then
    dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);

End;

The above example does not handle the TOO_MANY_ROWS exception. Indeed, it gets automatically handled with the OTHERS exception. Within that exception, we are displaying both our own message along with the message given by the system for that error. SQLERRM and SQLCODE are generally used in the OTHERS exception, when you really don't know what type of exception gets raised during execution.

SQLERRM already includes SQLCODE within the message. So you need not use SQLCODE in the above scenario. But if you really want to use it, you can follow the example given below:

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line('Name: '||v_ename1);

Exception
  When no_data_found then
    dbms_output.put_line('Employee not found with '||v_sal1);
  When others then
    if SQLCODE = -1422 then
      dbms_output.put_line('More than one employee exists with the same salary');
    else
      dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);
    end if;

End;

In the above program -1422 is the SQL error code pre-assigned to the exception TOO_MANY_ROWS (which gets shown in the SQLERRM). And I hope the rest is same.