Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    17

    Exclamation Unanswered: Does any exception raised with Bulk Collect!!

    Hi all,

    I am having a question regarding the bulk collect. I am using bulk collect in my SELECT statement. when SELECT statement does not retrive any statement [ when it uses bulk collect], it is not raising any exception. But when we normally do a select statement from a table and if the select does not retrive any rows , then it will raise an exception [in PLSQL]. So y is this happening and y is it not raising any exception if select count is zero.

    Or is there any situation will this bulk collect will raise an exception? If so , then what is that situation ? To handle the exception, do i need to use BULK_EXCEPTION?

    Example Query would be :
    Select emp_name bulk collect into lv_name from emp;

    Thanks in advance.

    Note : i searched through the web and as i am not able to find the appropriate answer, i am posting the same to the Forum.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    maybe you can use

    exception when others then...

  3. #3
    Join Date
    Sep 2004
    Posts
    17
    I would like to know the reason y any exception is not raised when using bulk collect in sql query.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Because NO_DATA_FOUND is applicable only to single-row selects (as is TOO_MANY_ROWS). Once you use BULK COLLECT you are expecting a variable number of rows, and that exception does not apply. You can check how many records were returned using SQL%ROWCOUNT. If you want, you can then even raise NO_DATA_FOUND:
    Code:
    select ... bulk collect into ... from ...;
    if SQL%ROWCOUNT = 0 then
      raise NO_DATA_FOUND;
    end if;

  5. #5
    Join Date
    Sep 2004
    Posts
    17
    Thanks a lot for the answer.

  6. #6
    Join Date
    Sep 2004
    Posts
    17
    one more doubt is like
    i)SQL%ROWCOUNT
    ii)array.last [ array is of type table]
    iii)array.count

    What is the difference between the above three...all of them r serving the same purpose!!What r all the diff context in which they use?

    Thanks in advance,
    Swarna

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    They aren't always the same, although they happen to be the same in this case. Consider:
    Code:
    SQL> declare
      2    type tabtype is table of number index by binary_integer;
      3    tab tabtype;
      4  begin
      5    tab(42) := 1;
      6    dbms_output.put_line(tab.count);
      7    dbms_output.put_line(tab.last);
      8  end;
      9  /
    1
    42
    
    PL/SQL procedure successfully completed.
    There my array has 1 element (tab.count=1), but that element has index value 42, so tab.last=42.

    SQL%ROWCOUNT tells you how many rows were processed in the preceding SQL statement. It can be used whether you are using arrays or not.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Also, let me add that one of the advantage of using bulk collections in PL/SQL is also the ability to catch the exceptions into BULKS, without having to actually process them row per row, you rather process then into BULKS as well. Regullary, what you would do is to *ignore* the exceptions, saving them for later auditing when your bulk process finishes.

  9. #9
    Join Date
    Sep 2004
    Posts
    17
    collecting Bulk Exceptions

    Sample :

    EXCEPTION
    WHEN others THEN
    error_count := SQL%BULK_EXCEPTIONS.COUNT;
    DBMS_OUTPUT.PUT_LINE('Number of errors is ' ||
    error_count);
    FOR indx IN 1..error_count LOOP
    DBMS_OUTPUT.PUT_LINE('Error ' || indx || '
    occurred during '||'iteration ' ||
    SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
    DBMS_OUTPUT.PUT_LINE('Error is ' ||
    SQLERRM(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
    END LOOP;
    END;

  10. #10
    Join Date
    Sep 2004
    Posts
    17
    Sorry for the inconvinience ..

    sample for collecting Bulk exception :

    exception
    when others then

    for i in 1..SQL%BULK_EXCEPTIONS.COUNT
    loop
    dbms_output.put_line('Error number ' || i || ' at iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
    dbms_output.put_line('Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    end loop;

    dbms_output.put_line('Total salaries updated = ' || SQL%ROWCOUNT));

    end;

Posting Permissions

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