Re: Dynamic stored procedure?

Loyal Barber <lbarber_at_ix.netcom.com> writes:

>Mark Styles wrote:
>>
>> Can anyone tell me if its possible to call a stored procedure when the
>> name of the procedure is held in a variable?
>>
>> e.g:
>>
>> declare
>> test varchar2(20) := 'get_emp_salary';
>> begin
>> execute(test);
>> end;
>> /
>>
>> I tried using dbms_sql to do this, but it only recognises standard SQL.
>I believe dynamic sql will work if you put it in a block
>declare
 

>begin
> <your procedure here>
>end;
 

>as a single string. Then submit the string to the parser.

Your right. I have a packaged procedure that executes other procedures listed in a table. The following is the important stuff:

cid := dbms_sql.open_cursor;

/* Execute the active procedures in the monitor control table */ for monitor_procs_rec in monitor_procs_cur loop

        dbms_sql.parse(cid,'begin '||monitor_procs_rec.proc_name||'; end;',dbms_sql.v7);

        execute_rc := dbms_sql.execute(cid);

Good luck.

-- 
-------------------------------------------------------------------------------
 Matthew J. Burke   		  			     mburke_at_netcom.com 
 Database Consultants, Inc.					(214) 392-0955
===============================================================================
-- 
-------------------------------------------------------------------------------
 Matthew J. Burke   		  			     mburke_at_netcom.com 
 Database Consultants, Inc.					(214) 392-0955
===============================================================================