Home » SQL & PL/SQL » SQL & PL/SQL » Urgent !
Urgent ! [message #38167] Wed, 27 March 2002 00:59 Go to next message
mousie
Messages: 9
Registered: March 2002
Junior Member
Hi;

My problem is :
- within a Stored procedure (let call it P) I retrieve (with a cursor) the names of others procedures (stored in a table); i want to execute some of them in some cases ; how can i perform this ? The retrieved name procedure is a literal string ; is there an option to tell to the caller (my Stored procedure P) to execute the procedure which name is stored in this variable ?

Thank you for all
Re: Urgent ! [message #38170 is a reply to message #38167] Wed, 27 March 2002 07:14 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You can use EXECUTE IMMEDIATE or DBMS_SQL.

Assuming no parameters for the procs:
for i in (select proc_name from tab) loop
execute immediate('begin '||i.proc_name||'; end;');
end loop;
end;
Re: Urgent ! [message #38177 is a reply to message #38170] Thu, 28 March 2002 04:29 Go to previous messageGo to next message
mousie
Messages: 9
Registered: March 2002
Junior Member
Thank you andrew ; but does it work on oracle v73 ? Can you show me how to use dbms_sql ? ( not only to execute proc whose names are stored in variables but also queries like 'select count(*)into x from tab_name'
where tab_name is a variable)

Thank you very much for helping me.
Re: Urgent ! [message #38180 is a reply to message #38170] Thu, 28 March 2002 07:13 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Create this function, then call it instead of EXECUTE IMMEDIATE.

CREATE OR REPLACE PROCEDURE DYNSQL (i_sql in VARCHAR2) iS
c_id PLS_INTEGER default dbms_sql.open_cursor;
o_count PLS_INTEGER;
BEGIN
dbms_sql.parse (c_id, i_sql, dbms_sql.native);
o_count := dbms_sql.execute(c_id);
dbms_sql.close_cursor(c_id);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(c_id);
DBMS_OUTPUT.PUT_LINE ( 'dynsql failed' );
END;
/

You'll have to take a look at the manual for details for getting output from the proc.
Previous Topic: SQL Loader question - Using a sequence to populate column
Next Topic: Temporary disable constaint on UPDATE
Goto Forum:
  


Current Time: Thu Mar 28 20:12:39 CDT 2024