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

[debasisdas]

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.

[debasisdas]

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.

[ayanmitra2007mindtree]

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..

[ayanmitra2007mindtree]

Any Ideas about the approach. Has anybody done this before...