SQL & PL/SQL

Hi Everybody!

I have two questions for you. All my questions are pertaining PL/SQL programming with Oracle 8i. But before that, I would like to introduce a bit about the background.

We have .NET based application, which calls some 80 odd Oracle stored procedures one after one. The input parameters for all these stored procedure are same i.e. two IN parameters of Integer type and a OUT parameter of cursor type. The name of these stored procedures are listed in table (let say tblSPTable). We use to get the list of stored procedures from this table and execute them one after one.

Sooner or later we realized that, this way of calling the stored procedures is causing a performance issue. So, we thought of moving the call to all these stored procedures to a new stored procedure. We thought of giving a call to this new stored procedure, which will in turn execute all these stored procedures one after one (by using the tblSPTable), and return us the all the cursors at one shot. But here is where we got stuck:

How can I declare a OUT parameter for a list of cursors? Because I need to store the output of all the 80 odd calls in different cursors and have to get it back. I have tried to declare VARRAY of cursors or TABLE of cursors but it is not supported. One way of doing this is to declare all the 80 cursors as OUT parameters in the new stored procedure, but that is absolutely a bad programming practice. Apart from that, in future if we want to modify the order of the stored procedure, OR if we want to add or remove few stored procedures listed in tblSPTable, we have to modify this new procedure every time. My question is how can I declare or use a variable which can hold the list of cursors, which I can use from a .NET base application.

Secondly, I will get the name of all the stored procedure by querying the tblSPTable, and will execute them dynamically. I have tried out something like this but not succeeded

declare
cur_result some_package.some_cursor;
var_spname varchar;
begin
var_spname:=’pr_some_procedure’;
execute immediate 'begin ‘ || var_spname || ‘(:2); end;' using out cur_result;
end;

Bur, I am getting an error saying “Error while trying to retrieve text for error ORA-03113”. I have scanned through few docs available over net, but none of them are really helpful. My question is how can I dynamically execute a stored procedure which has a cursor as a OUT parameter.

Please help me out if you people have any idea regarding this. Please let me know whether my approach is correct or not. I am waiting for your valuable suggestions.

Thanking you
Ayan Mitra

Message was edited by:
user588628