I have a stored procedure something like below. apk.temp is the global temporary table with on commit preserve rows. Front end web pages are in Cold-Fusion, with back end Oracle 11g.
Front end (search screen)calls this similar procedure to display result set.
p_Var has four kind of values say 1,2,3,4, that can be passed.
Say first the front end uses 1, it gives 50 records.
but when the try to use with other values 2, 3 or 4, each time instead of showing the result for Only 2 or 3 or 4, it stacks up. It seems the rows are not getting deleted.
I do not know if something is wrong with the procedure?
can any one please give some suggestions, advices please?
CREATE OR REPLACE procedure APK.TESTING
(p_var number := NULL, p_SelCur out sys_refcursor)
string1 := 'insert into apk.temp select * from apk.temp1 where t1 = :p_var '';
execute immediate string1 using p_var;
open p_SelCur for
select * from apk.temp ;