Home » SQL & PL/SQL » SQL & PL/SQL » Error in Bulk insert
Error in Bulk insert [message #321343] Tue, 20 May 2008 00:06 Go to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
I am facing problem in bulk inserrt. I have handled exception in certain case or ora error and want to continue the forall loop so i have used save exception. But problem is that if there handled error are in large number then code fails. Other wise for less data it works fine.
Is there any limit upto which oracle can save the bulk_exceptio errors?

sqlplus ${DB_LOGIN} > $sqlFileOutput 2>&1 <<-ENDSQL
set sqlprompt ''
set sqlnumber off
set serveroutput on
set heading off
set feedback off
spool $tmpSpoolFile

whenever sqlerror exit failure

DEFINE SUCCESS = 0
DEFINE FAILURE = 1

--*******************************************************************
--Declare bind variables
--*******************************************************************
VARIABLE v_ins_rows NUMBER
VARIABLE v_rej_rows NUMBER

--*******************************************************************
--PL/SQL
--*******************************************************************
DECLARE

x_RECORD_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT(x_RECORD_ALREADY_EXISTS, -1);
x_PARTITION_DOES_NOT_EXIST EXCEPTION;
PRAGMA EXCEPTION_INIT(x_PARTITION_DOES_NOT_EXIST, -14400);
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);


TYPE get_scripts_cur_typ IS REF CURSOR;
cv_get_scripts get_scripts_cur_typ;

v_FETCH_ROWS NUMBER := $COMMIT_CNT;

TYPE FILL_DT_tab IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.FILL_DT%TYPE INDEX BY PLS_INTEGER;
TYPE STORE_NBR_tab IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.STORE_NBR %TYPE INDEX BY PLS_INTEGER;
TYPE RX_NBR_tab IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.RX_NBR%TYPE INDEX BY PLS_INTEGER;
TYPE SIG_SEQ_NBR_tab IS TABLE OF PAS.PAS_SIG_CD.SIG_SEQ_NBR%TYPE INDEX BY PLS_INTEGER;
TYPE INSERT_DT_TME_tab IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.INSERT_DT_TME%TYPE INDEX BY PLS_INTEGER;

fill_dt_arr FILL_DT_tab;
store_nbr_arr STORE_NBR_tab;
rx_nbr_arr RX_NBR_tab;
sig_seq_nbr_arr SIG_SEQ_NBR_tab;
insert_dt_tme_arr INSERT_DT_TME_tab;

BEGIN

--****************************************************************
--Initialize bind variables
--****************************************************************
:v_rej_rows := 0;
:v_ins_rows := 0;

OPEN cv_get_scripts
FOR 'SELECT FILL_DT,
STORE_NBR ,
RX_NBR,
SIG_SEQ_NBR,
SCT.INSERT_DT_TME
FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC
WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';

LOOP
FETCH cv_get_scripts BULK COLLECT INTO fill_dt_arr,store_nbr_arr, rx_nbr_arr, sig_seq_nbr_arr, insert_dt_tme_arr LIMIT 100;

BEGIN
FORALL i IN store_nbr_arr.FIRST .. store_nbr_arr.LAST SAVE EXCEPTIONS
INSERT INTO PAS.PAS_SIG_SCRIPT(
SIG_SCRIPT_SEQ_NBR,
FILL_DT,
STORE_NBR,
RX_NBR,
SIG_SEQ_NBR,
INSERT_DT_TME,
CUR_IND)
VALUES(
PAS.SIG_SCRIPT_SEQ_NBR.NEXTVAL,
fill_dt_arr(i),
store_nbr_arr (i),
rx_nbr_arr (i),
sig_seq_nbr_arr(i),
insert_dt_tme_arr(i),
'N');
EXCEPTION

WHEN x_RECORD_ALREADY_EXISTS OR x_PARTITION_DOES_NOT_EXIST OR ex_dml_errors THEN
:v_rej_rows := :v_rej_rows + SQL%BULK_EXCEPTIONS.COUNT;

WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ERROR IN BULK INSERT OPERATION: ' || SQLERRM);
END;

--*************************************************************
--Gather metrics
--*************************************************************
IF (SQL%FOUND = TRUE) THEN
:v_ins_rows := :v_ins_rows + SQL%ROWCOUNT;
END IF;
COMMIT;
EXIT WHEN cv_get_scripts%NOTFOUND;
END LOOP;
CLOSE cv_get_scripts;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
ROLLBACK;
END;
/

--*******************************************************************
--PL/SQL
--*******************************************************************
BEGIN
--Display metrics
DBMS_OUTPUT.put_line ('Inserted Rows:' || :v_ins_rows);
DBMS_OUTPUT.put_line ('Rejected Rows:' || :v_rej_rows);

END;
/

ANALYZE TABLE PAS.PAS_SIG_SCRIPT ESTIMATE STATISTICS;

quit
ENDSQL

[Updated on: Tue, 20 May 2008 00:08]

Report message to a moderator

Re: Error in Bulk insert [message #321355 is a reply to message #321343] Tue, 20 May 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any limit upto which oracle can save the bulk_exceptio errors?

Process memory.

For the rest, I didn't read as:
- it is not formatted
- you talk abort errors but don't post them
- you didn't mention your Oracle version
...

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

By the way, "WHEN OTHERS THEN" without RAISE is a code bug.

Regards
Michel
Re: Error in Bulk insert [message #321382 is a reply to message #321355] Tue, 20 May 2008 02:17 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Michel Cadot

Thanks for your suggestion. Now I am RAISE the exception.I am using Oracle 9.2.0.6.0
Error Message is:

ERROR at line 1:
ORA-00600: internal error code, arguments: [kdtdelrow-2], [1], [1], [], [], [],
[], []
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at line 95

Formatted code is:
/*Notice: Formatted SQL is not the same as input*/

DECLARE
  x_Record_Already_Exists  EXCEPTION;
   PRAGMA EXCEPTION_INIT(x_Record_Already_Exists,- 1);
   x_Partition_Does_Not_Exist  EXCEPTION;
   PRAGMA EXCEPTION_INIT(x_Partition_Does_Not_Exist,- 14400);
   ex_dml_Errors  EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_dml_Errors,- 24381);
   TYPE Get_Scripts_Cur_typ IS REF CURSOR;
   cv_Get_Scripts     GET_SCRIPTS_CUR_TYP;
   v_Fetch_Rows       NUMBER := $Commit_cnt;
   

   TYPE Fill_dt_Tab           IS TABLE OF Pas.Pas_sig_Script_tmp.Fill_dt%TYPE   INDEX BY PLS_INTEGER;
   
   TYPE Store_nbr_Tab         IS TABLE OF Pas.Pas_sig_Script_tmp.Store_nbr %TYPE  INDEX BY PLS_INTEGER;
   
   TYPE rx_nbr_Tab            IS TABLE OF Pas.Pas_sig_Script_tmp.rx_nbr%TYPE   INDEX BY PLS_INTEGER;
   
   TYPE sig_seq_nbr_Tab       IS TABLE OF Pas.Pas_sig_cd.sig_seq_nbr%TYPE  INDEX BY PLS_INTEGER;
   
   TYPE Insert_dt_tMe_Tab     IS TABLE OF Pas.Pas_sig_Script_tmp.Insert_dt_tMe%TYPE  INDEX BY PLS_INTEGER;
   Fill_dt_arr        FILL_DT_TAB;
   Store_nbr_arr      STORE_NBR_TAB;
   rx_nbr_arr         RX_NBR_TAB;
   sig_seq_nbr_arr    SIG_SEQ_NBR_TAB;
   Insert_dt_tMe_arr  INSERT_DT_TME_TAB;
BEGIN
--****************************************************************
--Initialize bind variables
--****************************************************************
  :v_rej_rows := 0;
  
  :v_ins_rows := 0;
  
  OPEN cv_Get_Scripts FOR 'SELECT FILL_DT,
            STORE_NBR ,
            RX_NBR,
            SIG_SEQ_NBR,
            SCT.INSERT_DT_TME
       FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC
       WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';
  
  LOOP
            FETCH cv_Get_Scripts 
            BULK COLLECT INTO Fill_dt_arr,Store_nbr_arr, rx_nbr_arr, sig_seq_nbr_arr, Insert_dt_tMe_arr  
            Limit 100;
    
    BEGIN
      
               FORALL i IN Store_nbr_arr.FIRST .. Store_nbr_arr.LAST Save EXCEPTIONS
                  INSERT INTO Pas.Pas_sig_Script(
                                sig_Script_seq_nbr,
                                Fill_dt,
                                Store_nbr,
                                rx_nbr,
                                sig_seq_nbr,
                                Insert_dt_tMe,
                                Cur_Ind)
                           VALUES(
                           Pas.sig_Script_seq_nbr.Nextval,
                           Fill_dt_arr(i),
                           Store_nbr_arr (i),
                           rx_nbr_arr (i),
                           sig_seq_nbr_arr(i),
                           Insert_dt_tMe_arr(i),
                           'N');
    EXCEPTION
      WHEN x_Record_Already_ExistsOR
      x_Partition_Does_Not_ExistOR
      ex_dml_Errors THEN
        
          :v_rej_rows := :v_rej_rows + SQL%Bulk_Exceptions.COUNT;
      WHEN OTHERS THEN
        RAISE;
        ROLLBACK;
        dbms_Output.Put_Line('ERROR IN BULK INSERT OPERATION: '
                             ||SQLERRM);
    END;
    --*************************************************************
    --Gather metrics
    --*************************************************************
    
    IF (SQL%FOUND = True) THEN
      :v_ins_rows := :v_ins_rows + SQL%ROWCOUNT;
    END IF;
    
    COMMIT;
    
    EXIT WHEN cv_Get_Scripts%NOTFOUND;
  END LOOP;
  
  CLOSE cv_Get_Scripts;
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
    
    dbms_Output.Put_Line('ERROR: '
                         ||SQLERRM);
    
    ROLLBACK;
END;

[Updated on: Tue, 20 May 2008 02:40] by Moderator

Report message to a moderator

Re: Error in Bulk insert [message #321390 is a reply to message #321382] Tue, 20 May 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use code tags and limit your line width to 80 characters as I did it for you.

ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

ORA-14400: inserted partition key does not map to any partition
 *Cause:  An attempt was made to insert a record into, a Range or Composite
          Range object, with a concatenated partition key that is beyond
          the concatenated partition bound list of the last partition -OR-
          An attempt was made to insert a record into a List object with
          a partition key that did not match the literal values specified
          for any of the partitions.
 *Action: Do not insert the key. Or, add a partition capable of accepting
          the key, Or add values matching the key to a partition specification


Regards
Michel

[Updated on: Tue, 20 May 2008 02:42]

Report message to a moderator

Re: Error in Bulk insert [message #321394 is a reply to message #321390] Tue, 20 May 2008 02:56 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Michel Cadot

Yes this is the my need of code that take the rows from PAS.PAS_SIG_SCRIPT_TMP table and insert into PAS.PAS_SIG_SCRIPT table using bulk collect. If row is already exist in PAS.PAS_SIG_SCRIPT table (Oracle error : ORA-00001) or inserted partition key does not map to any partition (Oracle error : ORA-14400) then increment the rejected counter and if it is successfully inserted then increament the inserted counter.
But it is showing error.


Thanks
Re: Error in Bulk insert [message #321406 is a reply to message #321394] Tue, 20 May 2008 03:23 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel
Previous Topic: DBMS_JOBS - Executed time report.
Next Topic: control spooled html output
Goto Forum:
  


Current Time: Wed Dec 07 20:41:35 CST 2016

Total time taken to generate the page: 0.07649 seconds