Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL.EXECUTE
DBMS_SQL.EXECUTE [message #36555] Tue, 04 December 2001 08:01 Go to next message
Eric
Messages: 78
Registered: April 1999
Member
has anyone actually got dbms_sql.execute to to
execute a stored procedure?

I've had no luck.

----------------------------------------------------------------------
Re: DBMS_SQL.EXECUTE [message #36557 is a reply to message #36555] Tue, 04 December 2001 11:31 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
why do you want to execute stored proc using dbms_sql package?. use dbms_sql package to execute dynamic sql

----------------------------------------------------------------------
Re: DBMS_SQL.EXECUTE [message #36561 is a reply to message #36557] Tue, 04 December 2001 12:44 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I guess it would execute anonomous pl/sql OK, but as Suresh says, it's not common to try to do so.

Try something like this:
v_sql := 'begin
my_proc;
end;';

then execute that string using dbms_sql. Remember that role based grants on that proc (if it's not yours) is unlikely to be enough. You normally need to have the execute grant made direclty to the executer. Watch out with the trailing ';' or '/' in your string, it's not identical syntax to sqlplus.

----------------------------------------------------------------------
Re: DBMS_SQL.EXECUTE [message #36571 is a reply to message #36557] Wed, 05 December 2001 00:15 Go to previous message
Eric
Messages: 78
Registered: April 1999
Member
Thank you, Andrew. That worked a treat.

----------------------------------------------------------------------
Previous Topic: Pass default values to procedure
Next Topic: how to get ref of an object
Goto Forum:
  


Current Time: Mon Mar 30 02:25:23 CDT 2020