Re: Dynamically Calling Procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 28 Aug 1998 17:14:47 GMT
Message-ID: <35ece4f9.15289184@192.86.155.100>

A copy of this was sent to "Main" <kevind_at_i-link.net> (if that email address didn't require changing) On Fri, 28 Aug 1998 10:50:41 -0600, you wrote:

>Hi,
>
>Is there a way to dynamically call a stored procedure?
>
>I would like to do something like:
>
>BEGIN
> SELECT procedure_name
> INTO w_proc_name
> FORM Actions
> WHERE condition = w_conditition;
>
> EXECUTE w_proc_name;
>
>END;
>
>I have been playing with DBMS_SQL, but this seems to only let you create
>queries, and not execute programs.
>
>kd
>

Thats not true, dbms_sql lets you execute any SQL. The problem you hit was probably that you tried to use "EXECUTE proc_name" in the dbms_sql call when you should have used "begin proc_name; end;".

"EXECUTE foo" is simply sqlplus shortcut for "begin foo; end;". You can sort of see this if you EXECUTE something that doesn't exist, for example:

SQL> execute somehthing_that_doesnt_exist begin somehthing_that_doesnt_exist; end;

      *
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'SOMEHTHING_THAT_DOESNT_EXIST' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

See, the error message points to a string "begin ...; end;". Execute isn't sql.

Here is an example that works with dbms_sql:

SQL> declare

  2      l_cursor     integer default dbms_sql.open_cursor;
  3      l_dummy      number  default 0;
  4      l_procname   varchar2(255);
  5  begin
  6          l_procname := 'dbms_output.put_line( ''Hello World'' );';
  7  
  7          dbms_sql.parse(  l_cursor,
  8                          'begin ' || l_procname || 'end;',
  9                           dbms_sql.native );
 10  
 10          l_dummy := dbms_sql.execute(l_cursor);
 11  
 11          dbms_sql.close_cursor( l_cursor );
 12  exception
 13      when others then
 14        if dbms_sql.is_open(l_cursor) then
 15          dbms_sql.close_cursor(l_cursor);
 16        end if;
 17        raise;

 18 end;
 19 /
Hello World

PL/SQL procedure successfully completed.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 28 1998 - 12:14:47 CDT