Re: How can I dynamically execute a procedure name?

Marcie -

I struggled with something similar until I realized that you really can't use "execute" inside of PL/SQL code. Instead, use the PL/SQL "begin" and "end" and put your call to the procedure in between.

Here's a snippet of code from one of my procedures: <snip>

    stmt := 'begin osei.evall(' || jobno || '); end;';     execute immediate stmt;

In this case, evall is a procedure in the osei schema. I obtained jobno earlier in my PL/SQL, assembled the statement that executes the procedure, and then executed it. It works well.

Bye,
Tom

marcie wrote:

> We've created a report_map table that lists all the reports we need to
> run on a daily/monthly basis. I'm trying to create a procedure that
> will read each report name into a cursor and then execute a procedure
> with that report name.Is there a way using dynamic sql to do this?
> (We're using oracle 9i)
>
> i.e. my report_types are daily_sum, daily_95th - I'm reading these
> into a cursor and what to execute the procedure named daily_sum and
> daily_95th.
> Here's where I'm at:
>
> create or replace procedure nf3_daily as
> cursor cs1 is
> select report_type
> from report_type
> begin
> open cs1;
> loop
> fetch cs1 into daily_procedure;
> exit when cs1%NOTFOUND;
>
> AT THIS POINT I WANT TO EXECUTE EACH PROCEDURE (daily_sum etc.) if I
> use
> 'execute daily_procedure' it tries to find a procedure named
> 'daily_procedure' -- not the name of the procedure stored in the
> variable.
>
> end loop;
> end nf3_daily;
>
> I'd appreciate any ideas!
Received on Wed Aug 14 2002 - 11:21:45 CDT