Results 1 to 8 of 8

Thread: bulk bind issue

  1. #1
    Join Date
    Sep 2001
    Location
    Reading, UK
    Posts
    5

    Unanswered: bulk bind issue

    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

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I cannot reproduce the same output running all this as you posted it. Maybe you are missing something else ?

  3. #3
    Join Date
    Sep 2001
    Location
    Reading, UK
    Posts
    5

    Curioser and curioser said the cat

    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?

  4. #4
    Join Date
    Sep 2004
    Posts
    16
    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

  5. #5
    Join Date
    Sep 2001
    Location
    Reading, UK
    Posts
    5

    However ...

    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?

  6. #6
    Join Date
    Sep 2004
    Posts
    16
    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 !

  7. #7
    Join Date
    Sep 2001
    Location
    Reading, UK
    Posts
    5

    Probably a bug

    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!

  8. #8
    Join Date
    Sep 2001
    Location
    Reading, UK
    Posts
    5

    It's official ..

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •