Can anybody help me with this? In fact can anybody reproduce this?
Given the following tables and noting the foreign key constraint.



CREATE TABLE PSC_PARENT
(
NUM NUMBER NULL,
CH VARCHAR2(10 BYTE) NULL,
CONSTRAINT PK PRIMARY KEY (NUM)
)

CREATE TABLE PSC_CHILD
(
AWARD_KEY NUMBER(11) NOT NULL,
CONSTRAINT FK FOREIGN KEY (AWARD_KEY)
REFERENCES PSC_PARENT (NUM)
)



the following simple piece of PL/SQL



CREATE OR REPLACE PROCEDURE psc_bug
IS
TYPE t_psc_child IS TABLE OF psc_child%ROWTYPE INDEX BY PLS_INTEGER;
la_psc_child t_psc_child;
ln_failed_index NUMBER ;

BEGIN

la_psc_child(1).AWARD_KEY := 1;
la_psc_child(2).AWARD_KEY := 2;
la_psc_child(3).AWARD_KEY := 3;

dbms_output.put_line('start = ' || la_psc_child.FIRST);
dbms_output.put_line('end = ' || la_psc_child.LAST);

BEGIN
FORALL x IN la_psc_child.FIRST..la_psc_child.LAST
SAVE EXCEPTIONS
INSERT INTO psc_child
VALUES la_psc_child (x);
EXCEPTION
WHEN OTHERS THEN
FOR a IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
dbms_output.put_line ('Error ' ||
a ||
' occurred at row ' ||
sql%bulk_exceptions(a).error_index ||
' with error ' ||
sql%bulk_exceptions(a).error_code);
END LOOP;
END;

BEGIN
FORALL x IN la_psc_child.FIRST..la_psc_child.LAST
INSERT INTO psc_child
VALUES la_psc_child (x);

EXCEPTION
WHEN OTHERS THEN
ln_failed_index := sql%bulk_exceptions(1).error_index;
dbms_output.put_line ('failed index = ' || ln_failed_index);
dbms_output.put_line ('Error occurred at row ' ||
sql%bulk_exceptions(1).error_index ||
' with error ' ||
sql%bulk_exceptions(1).error_code);
END;
END;

gives the following results when the table psc_parent contains no records.

start = 1
end = 3
Error 1 occurred at row 1 with error 2291
Error 2 occurred at row 2 with error 2291
Error 3 occurred at row 3 with error 2291
failed index = 4
Error occurred at row 4 with error 2291

It is the fact that the bulk exception error index is returning four that is the problem. Has anybody any ideas where I am going wrong? This is on Oracle9i Release 9.2.0.4.0

I cannot reproduce the same output running all this as you posted it. Maybe you are missing something else ?

Interesting; the original problem occurrs under Oracle 9.2 running on a Unix box. I have snce replicated the problem under Windows XP. What operating system are you using?

Hi,
I think the problem lies with the fact that you are trying to find out the exception from the sql cursor sql%bulk_exceptions(1).error_index without having asked Oracle to store the exception for you. By default, Oracle would stop execution after it encounters the first exception. But it continues, if you ask it to save the exceptions using SAVE EXCEPTIONS as you did in the first half of the code. Only if you've saved the exception, can you read from the cursor sql%bulk_exceptions.
Therefore, try adding SAVE EXCEPTIONS after your FORALL loop, and things should work fine.

-Vinita

If you look at the documentation here http://download-uk.oracle.com/docs/c...olls.htm#32392 it states:

If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when an exception is raised. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record.

Which would lead one to believe that the one record in SQL%BULK_EXCEPTIONS contains the error index and the error code of the exception that was raised when the FORALL statement stops. Wouldn't you agree?

Yeah, true.
In our case here, we get
1. error_index as 4
2. sql%bulk_exceptions.count as 1
3. and sql%bulk_exceptions.error_code as 2291.

What I understand is, that without using SAVE EXCEPTIONS, Orcale does stop the execution of further statements, and stores the error index and code in SQL%BULK_EXCEPTIONS. This single record should obviously contain the index at which it failed, but surprisingly, it contains the last consecutively numbered index it encounters in the collection. Probably a bug, not sure !

We are treating it as a bug and, consequently, we are raising a TAR. It is a blessed nuisance for me though as it means re-writing a substantial amount of code, hey ho!

..it's a bug. Oracle support has recreated it and raised a bug. I await further developments.