how to use found_rows in oracle package to avoid two queries

Question

I made a package which I can use like this:

select * from table(my_package.my_function(99, 'something, something2', 1, 50))

I make use of the package in a stored procedure. Sample stored procedure looks like:

insert into something values(...) 
from 
(select * from table(my_package.my_function(99, 'something, something2', 1, 50))) a
other_table b
where b.something1 = a.something1;

open cv_1 for 
select count(*) from 
table(my_package.my_function(99, 'something, something2', 1, 50))

So I am calling the same package twice. first time to match records with other tables and other stuff and second time to get the count.

Is there a way to get the count first time around and put it into a variable and second time around I just pick that variable rather than calling the whole query again?

Hope it makes sense.

Answer 1

You can obtain the number of rows just inserted like this (I have corrected your INSERT syntax):

insert into something (...) 
select * from table(my_package.my_function(99, 'something, something2', 1, 50))) a
other_table b
where b.something1 = a.something1;

l_count := SQL%ROWCOUNT;