-
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;
-
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
-
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.
-
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....
-
yet you still cant read the forum rules and purpose of it
-
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 12: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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|