Using Collections in PL/SQL

[kewldudehere]

Hi All,

I have a Stored Procedure in which i have a for loop .In this loop i have a select statement and the returned row i am storing in a temporary physical table like

for cntr in 1..count loop
insert into temp(col1,col2, col3)
select col1,col2,col3 from table connect by id1=id start with id=cntr;
End loop;

And in the front end i am reading values from that temp table.
But this is taking long time to run when executed.
I googled abt this and found out many about Oracle Collections but i have never used this. Can any body help me out .

[debasisdas]

Try using the following steps.

1.create the procedure as part of a package.
2.declare a ref cursor inside a package.
3.use the ref cursor as the out parameter of the procedure.

[kewldudehere]

Hi,

Its confusing. Du want me to output refcursor from SP or Package? I have never used package. If possible can you wite me a sample one and how to call it.

Thanks

[debasisdas]

please check for some related articles in the HowTo section of the forum.

[amitpatel66]

Why are you doing a select inside FOR LOOP?
Why dont you insert all the records in the loop,commit and then SELECT JUST ONCE?

[kewldudehere]

Why are you doing a select inside FOR LOOP?
Why dont you insert all the records in the loop,commit and then SELECT JUST ONCE?

Hi Amit,

I am doing
for i=1..count loop
Insert into table(col1,col2 ,col3)
select c1,c2,c3 from table where id=i
End loop
So i get 1 row for each iteration in for loop which i am iserting in a table. After i am done with for loop ,I have opened a ref_cursor forthe table in which i have inserted and returning that Ref_cursor back.
But this is very slow since there its doing a insert to a table. I want to improve its performance. I think collections is the only way to make it work fast. I have never used collections.

[amitpatel66]

you said that you are using ref cursor for that table after for loop and returning that ref cursor as out parameter...the n what is the use of this SELECT statement inside the loop:

select c1,c2,c3 from table where id=i ??

[kewldudehere]

you said that you are using ref cursor for that table after for loop and returning that ref cursor as out parameter...the n what is the use of this SELECT statement inside the loop:

select c1,c2,c3 from table where id=i ??

I get one row for each iteration. I am inserting that row in temporory table. After for loop, i open cursor for that temporory table and return it.

[amitpatel66]

I get one row for each iteration. I am inserting that row in temporory table. After for loop, i open cursor for that temporory table and return it.

Why dont to insert all records first and then select from that table instead of selecting one by one after insert.