Re: How can I dynamically execute a procedure name?

On 14 Aug 2002 08:59:40 -0700, marcie.tietjen_at_westgroup.com (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!

Check out EXECUTE IMMEDIATE. Otherwise, DBMS_SQL.

Brian Received on Wed Aug 14 2002 - 11:35:18 CDT