1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

why bulk collect will not raise in no_data_found

Discussion in 'SQL PL/SQL' started by ramprasad.cgs, Dec 6, 2014.

  1. ramprasad.cgs

    ramprasad.cgs Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    100
    hi folks,

    can anyone tell me why bulk collect will not raise in no_data_found? explain
     
  2. garyfletcher

    garyfletcher Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    For bulk operations (FORALL, BULK COLLECT) you need to use the SAVE EXCEPTIONS clause and BULK_EXECEPTIONS.

    Google bulk collect exceptions to get more details.
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    This is a documented Oracle behavior:

    Refer to following link for more info:
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2219

    In order to check if "no data found" (i.e. no rows returned), check the content of the collection after the SELECT ... BULK COLLECT INTO or FETCH ... BULK COLLECT INTO .. statements using " IF <my_collection>.COUNT = 0...."
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Bulk collect fetches groups of rows at a time; the NO_DATA_FOUND is embedded in the last fetch and it IS a trigger to terminate the loop. Of course when it DOES terminate the loop the last, incomplete group of records fetched is 'lost':


    http://dfitzjarrell.wordpress.com/2012/02/27/collecting-thoughts/


    The second example proves this. It also shows how to get around the 'problem' and return all of the fetched data.




    David Fitzjarrell