This content has been marked as final. Show 6 replies
If you like to insert..say 10 million of rows... you should do it in parts.
CURSOR cur IS
FETCH cur BULK COLLECT INTO t_array LIMIT 100 ;
FORALL i IN 1..t_array.LAST SAVE EXCEPTIONS
EXIT WHEN cur%NOTFOUND;
Do it in this performance way and forget about the limitations of the SAVE EXCEPTIONS.
You can use an error logging table (as of 10g I think) :
INSERT INTO targettable
SELECT ... FROM sourcetable
LOG ERRORS INTO errlogtable REJECT LIMIT ...
for a complete example: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ
Should be better for performance reasons than trying to do it in plsql.
Thanks for your replies, but i think you've misunderstood. I am using FORALL ....
SAVE EXCEPTIONS, but when i execute it i get those ORA-06502 message!
In another thread i've read that for these exceptions a collection is used. My question is whether there is a maximum for this collection.
We're using 10.1.0.4 (forgot to mention that).
The maximum of the collection depends of your PGA memory.
My recomendation is to do it with a CURSOR and BULK COLLECT with the LIMIT clause.
this is how we execute it:
FETCH curCustomer BULK COLLECT
INTO VarCustomer LIMIT lLimit; <== 1000
FORALL i IN 1 .. VarCustomer.COUNT
INSERT INTO CUSTOMER VALUES VarCustomer(i);
lCount := lCount + VarCustomer.COUNT;
EXCEPTION WHEN OTHERS THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..errors LOOP
'CUSTOMER', SQLCODE, error_mesg,0);
lCount := lCount + VarCustomer.COUNT - errors;
EXIT WHEN curCustomer%NOTFOUND;
After approximately 8000 exceptions (avg. rowlength 305) the error appears.
You say that when you execute the code, you get the ORA-06502 error.
ORA-06502: PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
What is the procedure G99PR_ERROR_HANDLING?? Why do you pass to this procedure the parameter SQLCODE? If you like the error code of the FORALL you must pass -SQL%BULK_EXCEPTION(i).ERROR_CODE.