?
Solved

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

Posted on 2011-05-11
17
Medium Priority
?
568 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:paeddy
  • 9
  • 8
17 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35743742
"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
 

Author Comment

by:paeddy
ID: 35743851
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35743865
ah i realize you didn't put a commit in your stored procedure. put it before the end; line to commit the insert.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35743870
put commit; after the execute .... line
0
 

Author Comment

by:paeddy
ID: 35743968
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35743994
- i couldn't get what do u mean, so does this run ok now? or are there new problems?
0
 

Author Comment

by:paeddy
ID: 35744024
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35744033
- 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
 

Author Comment

by:paeddy
ID: 35744102
I do have the delete. but no luck yet.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35744115
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
 

Author Comment

by:paeddy
ID: 35773648
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
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35774146
"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
 

Author Comment

by:paeddy
ID: 35774241
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35774362
-  in pl/sql varchar2() datatype support up to 32767. so try to use the max.
0
 

Author Comment

by:paeddy
ID: 35774488
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35774508
-  great that it finally work for you Aeddy. sometimes it need a fresh eyes to look into problems :)

OP
0
 

Author Closing Comment

by:paeddy
ID: 35775035
Great support. Really appreciate that..
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month16 days, 2 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question