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!

bulk collect

Discussion in 'SQL PL/SQL' started by kalpana_csr, Oct 23, 2009.

  1. kalpana_csr

    kalpana_csr Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    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
    (param(i).emp_id, param(i).proj_id, param(i).proj_name,param(i).location, dept);
    end loop;

    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.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You already have a table type so a CAST won't do you much good, and bulk collect is for cursors (which you don't have). I don't understand your 'logic' for 'fine tuning'.
     
  3. kalpana_csr

    kalpana_csr Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    if I have to insert 1000's of records then each insert will make a call to the d/b and decrease the performance...so I want to perform a bulk insert....to reduce the interation with the d/b so many times
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    BULK COLLECT has no place in this code. You might get a forall to work if you do this:

    forall i in param3.first..param3.last
    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;

    Of course that won't allow your conditional insert to work.