oracle procedure sending result set to the front-end-- issue

HI,
    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)
as
String1 varchar2(500);
begin    
    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 ;    
end;

Thanks,
Aeddy.
paeddyAsked:
Who is Participating?
 
OP_ZaharinCommented:
"delete from sbg.apktemp1;
    string1 := 'insert into apktemp1
                select * from FndTbl where FndTypCd = :v_FndTypCd
                and creatdt >= sysdate - 365';                
    execute immediate string1
    using p_fndTypCd; commit;
    open p_SelCur for
    select * from apktemp1;"

- i didnt see the "commit" in place between delete and insert statement from your above procedure which why you are still getting the result of 100 instead of 50. always do a commit after DML operation (insert-update-delete):
 
    delete from sbg.apktemp1;
    commit;
    string1 := 'insert into apktemp1
                select * from FndTbl where FndTypCd = :v_FndTypCd
                and creatdt >= sysdate - 365';                
    execute immediate string1
    using p_fndTypCd;
    commit;
    open p_SelCur for
    select * from apktemp1;


 
0
 
OP_ZaharinCommented:
"It seems the rows are not getting deleted."
- i didn't see any delete command in your stored procedure. there is only insert into apk.temp table there. so i assume you are telling that record for t1=2 or 3 or 4 is not inserted to apk.temp table?

- lets check the data in apk.temp. if you run this query in sqlplus or any sql editor you are using, is there any return value?
select * from apk.temp1 where t1 = 2
/
select * from apk.temp1 where t1 = 3
/
select * from apk.temp1 where t1 = 4
0
 
paeddyAuthor Commented:
When I execute the procedure from SQL*PLUS, it executes successfully.
Before entering commit,  if I use
select * from apk.temp1 where t1 = 2..
then yes I can see the result in the cursor.

After I enter commit, and run the select query then no rows returned.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
OP_ZaharinCommented:
ah i realize you didn't put a commit in your stored procedure. put it before the end; line to commit the insert.
0
 
OP_ZaharinCommented:
put commit; after the execute .... line
0
 
paeddyAuthor Commented:
Tried as you mentioned to put commit.
when executed from sql plus, and I use commit on Sql plus, then run the select statement, I get the result set.
ALso, I run the procedure again with other values say 3,4, and commit it.
I then run the select statement and I see that the rows are all there..
i.e, rows for 1 and 2 and 3 and 4..

As above, when I use the front end screen, rows keep adding for 1,2,3,4..

0
 
OP_ZaharinCommented:
- i couldn't get what do u mean, so does this run ok now? or are there new problems?
0
 
paeddyAuthor Commented:
Nope. It the problem stays as is..
WHat i meant was when I used value 1 I got say 50 records.
I do commit;
Then I run with value 2, I get 50 + 50  records.
When value = 3, 50+50+50 records in the temp table, instead of just he 50 for value 3.


0
 
OP_ZaharinCommented:
- do you mean that you want that apk.temp to reset to 0 first each time you run the procedure? then add before the insert statement line:

delete from apk.temp;
commit;
0
 
paeddyAuthor Commented:
I do have the delete. but no luck yet.
0
 
OP_ZaharinCommented:
try this:

CREATE OR REPLACE procedure  APK.TESTING
(p_var number := NULL, p_SelCur out sys_refcursor)
as
String1 varchar2(500);
begin    
    delete from apk.temp;
    commit;
    string1 := 'insert into apk.temp   select * from apk.temp1 where t1 = :p_var '';                
    execute immediate string1  using p_var;    
    commit;
    open p_SelCur for
    select * from apk.temp ;    
end;
0
 
paeddyAuthor Commented:
Thanks Zaharin.

Yes it works for me for using varchar2(500) and me using a smaller query.
delete from sbg.apktemp1;
    string1 := 'insert into apktemp1
                select * from FndTbl where FndTypCd = :v_FndTypCd
                and creatdt >= sysdate - 365';                
    execute immediate string1
    using p_fndTypCd; commit;
    open p_SelCur for
    select * from apktemp1;
If I use the above, the ColdFusion(front end) could get the results based on the input parameter.

If test the same scenario with a larger string which I have in my case, it not working. The front end is seeing the results to be accumulated. i.e, say I execute first with parameter = 1, and I get 50 records.
then I execute with parameter = 2 which supposedly should get 50 records, now it displays 100, 50+50.
I declared my string as CLOB() and also as varchar2(5000) to check but no luck.
But with small query like above it works..
Did you guys face such kind of problem?
any further ideas?

Thanks.


0
 
paeddyAuthor Commented:
Yes, I missed the commit while typing above.
As you mentioned I have commit after each DML statement.
But for my query its not working. If I remove my big query and put the small query above
'select * from FndTbl where FndTypCd = :v_FndTypCd
                and creatdt >= sysdate - 365';    
it works. Not sure if the query size and the number of bind variables if something to do with it..
0
 
OP_ZaharinCommented:
-  in pl/sql varchar2() datatype support up to 32767. so try to use the max.
0
 
paeddyAuthor Commented:
Hi Zaharin,
                  Thank you very much for your excellent support.
I finally figured out the problem. The problem is not with the queries and the dynamic SQL, I have been asking questions about.
The procedure that I am dealing with is a huge one and I thought the problem was with my Dynamic SQL's and the temp tables data insert and retrieval.
But, the procedure has some part of the code at the top, where in it gets some of the Codes  and stores in the temp tables as well. I have all been overlooking these small tables and code section.
WHile I was testing using a small set, and when I commented out this small section of the code where it gets these codes, the procedure worked normally..
It a huge relief.. Sometimes even if the problem is very small and is in front of you, some times I guess  its overlooked..

But thank you very much for all the support and suggestions that you provided which made it easy for me..

Thanks,
Aeddy

0
 
OP_ZaharinCommented:
-  great that it finally work for you Aeddy. sometimes it need a fresh eyes to look into problems :)

OP
0
 
paeddyAuthor Commented:
Great support. Really appreciate that..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.