Home » SQL & PL/SQL » SQL & PL/SQL » Difference between Execute and DBMS_SQL
Difference between Execute and DBMS_SQL [message #160963] Wed, 01 March 2006 03:28 Go to next message
orajamzs
Messages: 110
Registered: February 2006
Location: hyderabad
Senior Member
In a procedure i used below given methods to drop a table and both are working, but what is the difference between 'execute immediate' and 'DBMS_SQL.PARSE' package...which is faster in execution, please suggest.

vsql varchar2(100):='drop user '||v_user_check;
execute immediate vsql;

or

cr number:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cr, vsql ,DBMS_SQL.V7);

--- which command is better to use in PLSQL as im using oracle9i Re1.

Thanks in advance
Re: Difference between Execute and DBMS_SQL [message #161010 is a reply to message #160963] Wed, 01 March 2006 07:12 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
execute immediate is faster than dbms_sql. This is also documented at:

http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a96590/adg09dyn.htm#26586

and you can try and verify it by creating a testcase as well.

Re: Difference between Execute and DBMS_SQL [message #161064 is a reply to message #161010] Wed, 01 March 2006 19:07 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DBMS_SQL predates EXECUTE IMMEDIATE in PL/SQL. DBMS_SQL was all we had in v7. EXECUTE IMMEDIATE is now (since v8.0) the preferred method of dynamic SQL in PL/SQL.

DBMS_SQL is still maintained because of the inability of EXECUTE IMMEDIATE to perform a so-called "Method 4 Dynamic SQL" where the name/number of SELECT columns or the name/number of bind variables is dynamic.

_____________
Ross Leishman
Re: Difference between Execute and DBMS_SQL [message #412071 is a reply to message #161010] Tue, 07 July 2009 11:13 Go to previous messageGo to next message
ctbalamurali
Messages: 11
Registered: June 2009
Junior Member
I'm facing problem while renaming the table loaded with data.
Here I'm trying to swith the table names but while renaming the table data is not getting loaded. Any suggestion please

Initialy Bakup table wil have data, I'm trying to rename the Backup table to Original and Original table to Backup.


begin
DBMS_OUTPUT.put_line (
'Inside EOB_temp_header');

execute immediate 'alter table Original rename to OLD';

execute immediate 'alter table BAKUP rename to Original'; --Now Original table not having any data

execute immediate 'alter table OLD'rename to BAKUP';
--BAKUP table again have the data!!. But expectation is Original table to be loaded with data

END;

Re: Difference between Execute and DBMS_SQL [message #412072 is a reply to message #412071] Tue, 07 July 2009 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the relation between your question and this topic named "Difference between Execute and DBMS_SQL"?

Regards
Michel
Re: Difference between Execute and DBMS_SQL [message #412074 is a reply to message #160963] Tue, 07 July 2009 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/147210/136107/

How is your hijacked post different from URL above?
Why do you refuse to follow Posting Guidelines?
Re: Difference between Execute and DBMS_SQL [message #412081 is a reply to message #412072] Tue, 07 July 2009 12:07 Go to previous message
ctbalamurali
Messages: 11
Registered: June 2009
Junior Member
I posted in this tread because while just executing "alter table BAKUP rename to Original" in prompt table is getting renamed and renamed table holds data but while using "execute immediate 'alter table BAKUP rename to Original'';" in store proc table is getting renamed but data is no more exist in it!! So I posted my entry thing that we can speak about execute immediate here.

Thanks & Regards,
Bala
Previous Topic: Need Query tunning Tips for performance (merged 2)
Next Topic: Find procedure name
Goto Forum:
  


Current Time: Tue Feb 11 03:43:22 CST 2025