Home » SQL & PL/SQL » SQL & PL/SQL » Error in Bulk insert
Error in Bulk insert [message #321343] |
Tue, 20 May 2008 00:06  |
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 #321382 is a reply to message #321355] |
Tue, 20 May 2008 02:17   |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 20:53:02 CST 2025
|