Re: calling stored procedures dynamically

From: Javier Rojas <jrv_at_quisar.com>
Date: Wed, 13 Jan 1999 12:15:17 +0100
Message-ID: <369C8045.D143D3C7_at_quisar.com>

Dynamiq SQL is the solution (I think).

CREATE OR REPLACE PACKAGE DYNAMIC_PL_SQL IS   PROCEDURE execute(pl_sql_text IN VARCHAR2);   PROCEDURE execute_procedure(procedure_name IN VARCHAR2); END;    CREATE OR REPLACE PACKAGE BODY DYNAMIC_PL_SQL IS PROCEDURE execute(pl_sql_text IN VARCHAR2) IS

  cur_id 	NUMBER;
  status 	NUMBER;

BEGIN
  cur_id := dbms_sql.open_cursor;
  dbms_sql.parse(cur_id, pl_sql_text, DBMS_SQL.NATIVE);   status := dbms_sql.execute(cur_id);
  dbms_sql.close_cursor(cur_id);

EXCEPTION
  when OTHERS then
    if dbms_sql.is_open(cur_id) then
      dbms_sql.close_cursor(cur_id);
    end if;     

    raise;
END; PROCEDURE execute_procedure(procedure_name IN VARCHAR2) IS BEGIN
  execute('BEGIN ' || procedure_name || '; END;'); END; END; To call a procedure with this package:

dynamic_pl_sql.execute_procedure('procedure_name');

To execute PL-SQL code:

dynamic_pl_sql.execute('BEGIN ' ||
                       '  DBMS_OUTPUT.PUT_LINE(''hola'');' ||
                       'END;');

Aaaaaaaaaadios,

Javier Rojas.

Emmanuel Uhunmwangho wrote:
>
> Is it possible to call a stored procedure dynamically from a stored
> function. Like calling sql statements dynamically using the dbms_sql
> package.
> The problem is that I have a pl/sql block in forms5.0 which calls a
> procedure, but I cannot tell the name of the procedure to call until
> runtime.
Received on Wed Jan 13 1999 - 12:15:17 CET