Home » RDBMS Server » Performance Tuning » Trapping error in FORALL statement
Trapping error in FORALL statement [message #65443] Wed, 22 September 2004 01:44 Go to next message
Geeta Vinai
Messages: 1
Registered: September 2004
Junior Member
How to trap error in FORALL statement of Bulk Binding since exception cannot be written for that
Re: Trapping error in FORALL statement [message #65446 is a reply to message #65443] Wed, 22 September 2004 07:16 Go to previous message
shoblock
Messages: 325
Registered: April 2004
Senior Member
The following example shows how useful the cursor attribute %BULK_EXCEPTIONS can be:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
BEGIN
...
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE * FROM emp WHERE sal > 500000/num_tab(i);
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
Previous Topic: Speed becomes slow after some days
Next Topic: too slow select.
Goto Forum:
  


Current Time: Thu Feb 29 08:07:31 CST 2024