CODEWITHSUNDEEP
oracle-databaseexceptionbulkinsertoracle11gr2
Gaurav Soni
Gaurav Soni

Reputation: 6346

more specific error message with bulk insert save exception

    declare
      e_forall_error exception;
      pragma exception_init(e_forall_error,-24381)
          ;
      type t_numbers is table of emp.empno%type;
      l_numbers t_numbers := t_numbers(null,2,3,4,5,6,7,8,9,10)
          ;
    begin
      forall i in 1..l_numbers.count save exceptions
  --note:empno is primary key     
      insert into emp
       ( empno      , ename
       )
       values
       ( l_numbers(i)
       , 'Name' || to_char(l_numbers(i))
       )
         ;
   exception
   when e_forall_error then
     for i in 1..sql%bulk_exceptions.count
     loop
       dbms_output.put_line('SQLCODE: ' || sql%bulk_exceptions(i).error_code);
       dbms_output.put_line('SQLERRM: ' || sqlerrm(-sql%bulk_exceptions(i).error_code));
       dbms_output.new_line;
     end loop;
   end;
   /

HI all , i have an exisiting procedure which is inserting the value of empno and empname into emp table as shown above,but the problem is that the error mentioned by the procedure is 

SQLCODE: 1400
SQLERRM: ORA-01400: cannot insert NULL into ()

I want a more specific error ,what it usually throw like 

SQLERRM: ORA-01400: cannot insert NULL into empno column

How can this been possible in bulk insert?

Upvotes: 2

Views: 2060

Answers (1)

Sebas
Sebas

Reputation: 21542

CREATE OR REPLACE TABLE logs (logcode NUMBER, logmsg VARCHAR2(1024));
/

CREATE OR REPLACE PROCEDURE logme(I_logcode IN NUMBER, I_logmsg IN VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    insert into logs (logcode, logmsg) values (I_logcode , I_logmsg);
    commit;
END;
/

CREATE OR REPLACE PROCEDURE insertmyrow(I_prm1 IN VARCHAR2, I_prm2 IN VARCHAR2, ...) IS
BEGIN
    insert into emp  (empno, ename, ...) values (I_prm1, I_prm2, ...);
EXCEPTION WHEN OTHERS THEN                
    logme(sqlcode, sqlerrm);
    RAISE;
END;
/

forall i in 1..l_numbers.count save exceptions
    insertmyrow(l_numbers(i), ...);

This is the main idea and I guess a bit heavy maybe but why not?

Upvotes: 1

Related Questions