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