Re: Dynamic stored procedure?

From: Venkateswara R Polisetti <vrao_at_sctcorp.com>
Date: 1996/08/22
Message-ID: <1996Aug21.183640.8492_at_mcrcr6>#1/1

Mark Styles <lambic_at_msn.com> 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;
>/

Execute is one of the SQL*PLUS commands. You can not use it in PL/SQL block.

>I tried using dbms_sql to do this, but it only recognises standard SQL.

You can certainly do this using dbms_sql : The following is an example that depicts that: 1. Create a test procedure from SQL*PLUS.

create or replace procedure myproc
as

   v_user varchar2(30);
begin

    select user into v_user from dual;
    dbms_output.put_line('Current user is :'|| v_user); end;
/

2. Execute the following code which in turn call myproc procedure using dynamic sql in PL/SQL.    

1. Turn the server output on in SQL*PLUS so that dbms_output can be seen on the screen. SQL> SET SERVEROUTPUT ON
2. execute the following PL/SQL sample code:

DECLARE
    c_id integer;
    ret integer;
begin

         c_id := dbms_sql.open_cursor;    
        dbms_sql.parse(c_id, 'begin myproc; end;',dbms_sql.v7);
        ret := dbms_sql.execute(c_id);
        dbms_sql.close_cursor(c_id);

end;
/

I think you may be able to pass parameters to the procedure in the form of literals but I don't think you will be able to get the values back from the procedure.

Good luck,
Venkateswara Rao

/-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\

| Venkateswara R.Polisetti                 Email: vrao_at_sctcorp.com  |
| SCT Utility Systems, Inc.             Phone(O): (803) 935-8160    |
| 9 Science Court                            (R): (803) 736-1609    |
| Columbia, SC  29203-9344                                          |

\-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-/ Received on Thu Aug 22 1996 - 00:00:00 CEST