OraFAQ Forum: SQL & PL/SQL » DBMS

Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL.EXECUTE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_SQL.EXECUTE [message #36555]

Tue, 04 December 2001 08:01

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.

----------------------------------------------------------------------

Report message to a moderator

Re: DBMS_SQL.EXECUTE [message #36557 is a reply to message #36555]

Tue, 04 December 2001 11:31

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

----------------------------------------------------------------------

Report message to a moderator

Re: DBMS_SQL.EXECUTE [message #36561 is a reply to message #36557]

Tue, 04 December 2001 12:44

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.

----------------------------------------------------------------------

Report message to a moderator

Re: DBMS_SQL.EXECUTE [message #36571 is a reply to message #36557]

Wed, 05 December 2001 00:15

Eric
Messages: 78
Registered: April 1999

Member

Thank you, Andrew. That worked a treat.

----------------------------------------------------------------------

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:12:28 CDT 2024