Difference between Execute and DBMS_SQL [message #160963] |
Wed, 01 March 2006 03:28  |
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 #161064 is a reply to message #161010] |
Wed, 01 March 2006 19:07   |
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   |
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 #412081 is a reply to message #412072] |
Tue, 07 July 2009 12:07  |
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
|
|
|