Oracle PL/SQL/PL SQL/SQLERRM

Error code and message

   <source lang="sql">
   

SQL> SQL> CREATE TABLE myTable(col1 NUMBER); Table created. SQL> SQL> DECLARE

 2      v_sqlcode NUMBER;
 3      v_sqlerrm VARCHAR2(100);
 4  BEGIN
 5      FOR i IN 1..10000 LOOP
 6        INSERT INTO myTable VALUES (i);
 7      END LOOP;
 8      commit;
 9
10  EXCEPTION
11      WHEN OTHERS THEN
12        v_sqlcode := SQLCODE;
13        v_sqlerrm := substr(SQLERRM,1,80);
14        dbms_output.put_line(v_sqlcode || v_sqlerrm);
15    END;
16  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped.

 </source>
   
  

Error code: sql%bulk_exceptions(i).error_code

   <source lang="sql">
   

SQL> create table myTable (

 2   x number not null );

Table created. SQL> SQL> set serverout on SQL> declare

 2   type numlist is table of number
 3   index by binary_integer;
 4   n numlist;
 5   begin
 6       for i in 1 .. 50 loop
 7           n(i) := i;
 8       end loop;
 9
10       n(37) := null; -- will cause a problem
11
12       forall i in 1 .. 50 save exceptions
13       insert into myTable values (n(i));
14
15       exception when others then
16       dbms_output.put_line("Errors:"||sql%bulk_exceptions.count);
17       for i in 1 .. sql%bulk_exceptions.count loop
18           dbms_output.put_line("index:"||sql%bulk_exceptions(i).error_index);
19           dbms_output.put_line("code:"||sql%bulk_exceptions(i).error_code);
20           dbms_output.put_line("message:");
21           dbms_output.put_line(sqlerrm(sql%bulk_exceptions(i).error_code));
22       end loop;
23   end;
24  /

Errors:1 index:37 code:1400 message: -1400: non-ORACLE exception PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped.

 </source>
   
  

Reference sql%bulk_exceptions(i).error_index

   <source lang="sql">
   

SQL> create table myTable (

 2   x number not null );

Table created. SQL> SQL> set serverout on SQL> declare

 2   type numlist is table of number
 3   index by binary_integer;
 4   n numlist;
 5   begin
 6       for i in 1 .. 50 loop
 7           n(i) := i;
 8       end loop;
 9
10       n(37) := null; -- will cause a problem
11
12       forall i in 1 .. 50 save exceptions
13       insert into myTable values (n(i));
14
15       exception when others then
16       dbms_output.put_line("Errors:"||sql%bulk_exceptions.count);
17       for i in 1 .. sql%bulk_exceptions.count loop
18           dbms_output.put_line("index:"||sql%bulk_exceptions(i).error_index);
19           dbms_output.put_line("code:"||sql%bulk_exceptions(i).error_code);
20           dbms_output.put_line("message:");
21           dbms_output.put_line(sqlerrm(sql%bulk_exceptions(i).error_code));
22       end loop;
23   end;
24  /

Errors:1 index:37 code:1400 message: -1400: non-ORACLE exception PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped.

 </source>
   
  

SQLERRM function.

   <source lang="sql">
   

SQL> SQL> set serveroutput on SQL> SQL> BEGIN

 2    DBMS_OUTPUT.PUT_LINE("SQLERRM(0): " || SQLERRM(0));
 3    DBMS_OUTPUT.PUT_LINE("SQLERRM(100): " || SQLERRM(100));
 4    DBMS_OUTPUT.PUT_LINE("SQLERRM(10): " || SQLERRM(10));
 5    DBMS_OUTPUT.PUT_LINE("SQLERRM: " || SQLERRM);
 6    DBMS_OUTPUT.PUT_LINE("SQLERRM(-1): " || SQLERRM(-1));
 7    DBMS_OUTPUT.PUT_LINE("SQLERRM(-54): " || SQLERRM(-54));
 8  END;
 9  /

SQLERRM(0): ORA-0000: normal, successful completion SQLERRM(100): ORA-01403: no data found SQLERRM(10): -10: non-ORACLE exception SQLERRM: ORA-0000: normal, successful completion SQLERRM(-1): ORA-00001: unique constraint (.) violated SQLERRM(-54): ORA-00054: resource busy and acquire with NOWAIT specified PL/SQL procedure successfully completed. SQL> SQL>

 </source>
   
  

SQLERRM function returns the error message associated with the most recently raised error exception

   <source lang="sql">
  

SQL> -- SQLERRM function returns the error message associated with the most recently raised error exception SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION

---

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

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> CREATE OR REPLACE PROCEDURE add_new_employee

 2          (employee_id_in IN NUMBER, salary_in IN NUMBER)
 3      IS
 4          no_salary EXCEPTION;
 5
 6      BEGIN
 7          IF salary_in = 0 THEN
 8              RAISE no_salary;
 9          ELSE
10              INSERT INTO employee (id, salary ) VALUES ( employee_id_in, salary_in );
11      END IF;
12
13      EXCEPTION
14           WHEN no_salary THEN
15              raise_application_error (-20001,"You must have salary in order to insert the employee.");
16
17           WHEN OTHERS THEN
18              --raise_application_error (-20002,"An error has occurred inserting an employee.");
19              raise_application_error(-20001,"An error was encountered - "||SQLCODE||" -ERROR- "||SQLERRM);
20      END;
21      /

Procedure created. SQL> SQL> exec add_new_employee("99",0); BEGIN add_new_employee("99",0); END;

ERROR at line 1: ORA-20001: You must have salary in order to insert the employee. ORA-06512: at "sqle.ADD_NEW_EMPLOYEE", line 15 ORA-06512: at line 1

SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>

 </source>
   
  

SQLERRM has the error message

   <source lang="sql">
   

SQL> create table myTable(acolumn number); Table created. SQL> set serveroutput on SQL> SQL> DECLARE

 2      v_sqlcode NUMBER;
 3      v_sqlerrm VARCHAR2(100);
 4  BEGIN
 5      FOR i IN 1..10000 LOOP
 6        INSERT INTO myTable VALUES (i);
 7      END LOOP;
 8      commit;
 9  EXCEPTION
10      WHEN OTHERS THEN
11        v_sqlcode := SQLCODE;
12
13        v_sqlerrm := substr(SQLERRM,1,80);
14        dbms_output.put_line(v_sqlcode || v_sqlerrm);
15    END;
16  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped. SQL>

 </source>
   
  

sqlerrm(sql%bulk_exceptions(i).error_code)

   <source lang="sql">
   

SQL> create table myTable (

 2   x number not null );

Table created. SQL> SQL> set serverout on SQL> declare

 2   type numlist is table of number
 3   index by binary_integer;
 4   n numlist;
 5   begin
 6       for i in 1 .. 50 loop
 7           n(i) := i;
 8       end loop;
 9
10       n(37) := null; -- will cause a problem
11
12       forall i in 1 .. 50 save exceptions
13       insert into myTable values (n(i));
14
15       exception when others then
16       dbms_output.put_line("Errors:"||sql%bulk_exceptions.count);
17       for i in 1 .. sql%bulk_exceptions.count loop
18           dbms_output.put_line("index:"||sql%bulk_exceptions(i).error_index);
19           dbms_output.put_line("code:"||sql%bulk_exceptions(i).error_code);
20           dbms_output.put_line("message:");
21           dbms_output.put_line(sqlerrm(sql%bulk_exceptions(i).error_code));
22       end loop;
23   end;
24  /

Errors:1 index:37 code:1400 message: -1400: non-ORACLE exception PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped.

 </source>
   
  

Trim the error message and then output

   <source lang="sql">
   

SQL> SQL> CREATE TABLE myTable

 2  (col1 NUMBER,
 3   col2 NUMBER,
 4   col3 NUMBER);

Table created. SQL> SQL> DECLARE

 2      v_sqlcode NUMBER;
 3      v_sqlerrm VARCHAR2(100);
 4  BEGIN
 5      FOR i IN 1..1000 LOOP
 6        INSERT INTO myTable VALUES (i,i*10,i*100);
 7      END LOOP;
 8      commit;
 9  EXCEPTION
10      WHEN OTHERS THEN
11        v_sqlcode := SQLCODE;
12        v_sqlerrm := substr(SQLERRM,1,80);
13        dbms_output.put_line(v_sqlcode || v_sqlerrm);
14    END;
15  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped.

 </source>