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...then 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...then 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.