Bulk collect on collections
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Bulk collect on collections

  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Bulk collect on collections

    I have a procedure

    create or replace proc1(param1 in out number,
    param2 varchar2,
    param3 table_emp_proj)
    as

    for i in param3.first..param3.last loop
    update emp
    set proj_id=param3(i).proj_id,
    proj_name=param3(i).proj_name,
    dept=param1
    where emp_id=param3(i).emp_id
    and location=param3(i).location;
    if sql%rowcount=0 then
    insert into emp
    values
    (emp_id, proj_id, proj_name,location, dept);
    end loop;

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    shame you couldn't read what this forum is for then

    but good for you having that procedure, is surely is a thing of beauty

  3. #3
    Join Date
    Oct 2009
    Posts
    3
    Accidentally got posted.....in half way through my question...

    I would like to know how can I use bulk collect or table(cast) in the procedure to fine tune it...

    The parameter table_emp_proj is defined as follow

    create or replace type emp_type as object(empno number(20), proj_id number(20), proj_name varchar2(40), location varchar2(40));

    create or replace type table_emp_proj as AS TABLE OF emp_type;

    I want to avoid the loop and use either bulk collect or for all option.

  4. #4
    Join Date
    Oct 2009
    Posts
    3
    Shame on you to give such replies....not giving enuf time to the author see the posted message and edit the message.....and after posting the full question....you disappeared....

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yet you still cant read the forum rules and purpose of it

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    you may want to check Venzi's comments on Bulk collect vs. Cursors... here http://venzi.wordpress.com/2007/09/2...rsor-for-loop/

    By the way, Dave is right... your post is in a wrong section of the forum.
    Last edited by PAVB; 10-24-2009 at 01:55 PM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width