473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Dynamic Execution of Stored Procedure

ayanmitra2007mindtree
Consider I have five stored procedures viz.

pr_sp1 (int_num1 IN number, int_num2 IN Number, v_cur OUT s_pkg.s_cur)
pr_sp2 (int_num1 IN number, int_num2 IN Number, v_cur OUT s_pkg.s_cur)
pr_sp3 (int_num1 IN number, int_num2 IN Number, v_cur OUT s_pkg.s_cur)
pr_sp4 (int_num1 IN number, int_num2 IN Number, v_cur OUT s_pkg.s_cur)
pr_sp5 (int_num1 IN number, int_num2 IN Number, v_cur OUT s_pkg.s_cur)


If you see, all these SPs are of same signature, but actually they queries different tables. For example pr_sp1 queries tblA, whereas the pr_sp2 queries tblB and so forth. The returns different resultsets with differnt range and number of columns in the Cursor, as defined in v_cur.

However, the int_num1 and int_num2 will be same for all the five SPs. All of them stores output as a cursor in v_cur variable which is a OUT parameter in all the SPs. We use this out paramter and apply some business logic.

Till date we were following an approach, where use to call these SPs one after one from a .net based application. But, we noticed a severe performance degradation as we are hitting the database everytime from the application to call the SP. So, we moved the call to a new oracle SP from where we will call these five SPs. Something like this:

create or replace my_new_sp
(
int_num1 in number,
int_num2 in number,
v_cur1 OUT s_pkg.s_cur,
v_cur2 OUT s_pkg.s_cur,
v_cur3 OUT s_pkg.s_cur,
v_cur4 OUT s_pkg.s_cur,
v_cur5 OUT s_pkg.s_cur
)
as
begin
pr_sp1(int_num1, int_num2, v_cur1);
pr_sp2(int_num1, int_num2, v_cur2);
pr_sp3(int_num1, int_num2, v_cur3);
pr_sp4(int_num1, int_num2, v_cur4);
pr_sp5(int_num1, int_num2, v_cur5);
end;


Like this if we call the new SP (my_new_sp), we will get all the results back in one shot.

But, as you can see, the way I am managing the call is absolutely not good (at least I don't like). Here I have shown only five SPs for illustration, but in actual scenario there is some 80+ SPs[/u]. So, declaring cursor for individual SP is absolutely not a good idea.

Apart from this, the name of the SPs (here pr_sp1, pr_sp2, ... etc.) which I will be executing is stored in a table called tblSPList, which can be used to dynamically execute the procedures. Now my questions is:

1) How can declare a variable which will give me a list of cursors (accessible from .NET). I mean Instead of declaring 5 cursors in the example shown above, can I declare a variable which stores the list of cursors. I have tried VARRAY, TABLES etc. but failed.

2) How can I execute, a stored procedure dynamically. I mean, in my example where I am exclusively calling :

pr_sp1(int_num1, int_num2, v_cur1);

can I have a way to call it dynamically, something like

execute immediate 'begin ‘ || var_spname || ‘(:2); end;' using out cur_result;

where var_spname will contain the name of SP i.e. pr_sp1 and the out put I will store in cur_result. Please note, I have tried the above statement, but it is failing and Oracle is showing some cryptic error message which says... "Error while trying to retrieve text for error ORA-03113"

Any Ideas
Aug 7 '07 #1
4 3747
debasisdas
8,127 Expert 4TB
for your situation the best solution would be to write a single stored procedures having a ref-cursor as the output. you need to declare the ref-cursor in a package so that its scope would be global. enen you can declare the procedure in side the package it self.
Aug 7 '07 #2
debasisdas
8,127 Expert 4TB
An ORA-03113 end-of-file on communication channel error is a very generic error that indicates that the connection has been lost.
ORA-3113 is reported by the oracle client processes due to many reasons. ORA 3113 by itself does not indicate the cause of the issue and is accompanied by other messages that are either displayed on the client side or in the oracle database alertlog.

Cause: The connection between Client and Server process was broken
Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time.
Aug 7 '07 #3
I think it will be better that you people read my question carefully and then answer. My problem is not with network or channel specific. I have asked something related to PL/SQL programming approach. I request you to please go through the scenario once again..
Aug 7 '07 #4
Any Ideas about the approach. Has anybody done this before...
Aug 8 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Guinness Mann | last post by:
When you guys talk about "dynamic SQL," to what exactly are you referring? Is dynamic SQL anything that isn't a stored procedure? Specifically, I use ASP.NET to communicate with my SQL Server...
1
by: Todd Peterson | last post by:
I'm a newbie to DB2 and am trying to figure out how to write a stored procedure, using dynamic SQL statements to return a result set. I believe the majority of the hurdles I have been facing might...
1
by: Raquel | last post by:
This is a stored procedure that resides on Mainframe and gets executed on the client by connecting to the mainframe through DB2 connect. It was executing fine till yesterday when I executed a table...
6
by: MattC | last post by:
Hi, I'm implementing a new Business Layer in one of our applications. I'm toying with the idea of placing all the Create, Read, Update and Delete SQL in the object in question and build a...
5
by: alingsjtu | last post by:
Hello, every body. When execute dynamic generated multiple OPENQUERY statements (which linkes to DB2) in SQLServer, I always got SQL1040N The maximum number of applications is already connected...
4
by: TheRealPawn | last post by:
I'm trying to get the execution plan for a single stored procedure from Profiler. Now, I've isolated the procedure but I get all execution plans. Any ideas on how to connect the SPIDs so that I...
7
by: Ronald S. Cook | last post by:
I've always been taught that stored procedures are better than writing SQL in client code for a number of reasons: - runs faster as is compiled and lives on the database server - is the more...
5
by: Ronald S. Cook | last post by:
I've read a few posts on the stored procedure vs dynamic sql debate. I ran a few performance test for myself and it appears to be a wash. Given that, I'm leaning toward dynamic sql mostly...
5
by: sqlgirl | last post by:
Hi, We are trying to solve a real puzzle. We have a stored procedure that exhibits *drastically* different execution times depending on how its executed. When run from QA, it can take as...
23
by: valentin tihomirov | last post by:
Hello, -=PREHISTORY=- Occasionally, I wanted to rewrite my UPDATE code to prevent SQL injection. I generated the SQL query UPDATE tt SET @p1_name = @p1_val
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.