Re: Calling Procedures/Functions dynamically

>My application is in Oracle8i(server) with Developer6 as
>Front End. The application requires that PL/SQL procedures
>and Functions be called dynamically. The procedure names
>are stored in a table which will be queried and executed.
>
>The problem is that although there are built-ins which
>return the value stored in a variable, we do not know how
>to execute the Procedure name returned by the variable
>without hardcoding the value.
>
>return_value := (name_in('Parameter.procname'))
>
>A statement like the one above only returns the
>proc/function name but does not execute it.
>
>How can we execute the 'Proc/Function name' returned?
>
>
>* Sent from AltaVista http://www.altavista.com Where you can also find
>related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is
>Beautiful
>
You can use dynamic sql to execute a procedure that is known only at runtime. Look in the doc... an overview is to...

create a cursor with dbms_sql.open_cursor... create a sql string with the package/procedure. If you are using a function, you will need to write it as a sql statement, and use bind variables. If you are using a procedure, you can just execute it.

dbms_sql.parse (<generated sql statement as a varchar2>);

...dbms_sql.execute (...)

...dbms_sql.close_cursor...

Hope this helps
Dan Hekimian-WIlliams Received on Fri Feb 04 2000 - 03:20:04 CET