Oracle PL/SQL/PL SQL/sqlcode

Get error count from sql%bulk_exceptions.count

   
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.

sql code and sql errm

 

SQL> set serverout on;
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>
SQL> -- sql code and sql errm
SQL>
SQL>  declare
  2      l_emp employee%rowtype;
  3    begin
  4      select * into l_emp from employee;
  5
  6      dbms_output.put_line("EMPNO: " || l_emp.id);
  7      dbms_output.put_line("ENAME: " || l_emp.first_name);
  8    exception
  9      when others then
 10        dbms_output.put("Exception encountered! (");
 11        dbms_output.put_line(sqlcode || "): " || sqlerrm);
 12        raise;
 13    end;
 14    /
Exception encountered! (-1422): ORA-01422: exact fetch returns more than requested number of rows
 declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 12

SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>

This script demonstrates error functions SQLERRM and SQLCODE

   
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     v_error VARCHAR2(10);
  3  BEGIN
  4     SELECT dummy INTO v_error FROM dual WHERE 1=2;
  5  EXCEPTION
  6     WHEN OTHERS
  7     THEN
  8        DBMS_OUTPUT.PUT_LINE("SQLERRM: "||SQLERRM);
  9        DBMS_OUTPUT.PUT_LINE("SQLCODE: "||SQLCODE);
 10  END;
 11  /
SQLERRM: ORA-01403: no data found
SQLCODE: 100
PL/SQL procedure successfully completed.
SQL>
SQL>