Home » SQL & PL/SQL » SQL & PL/SQL » Difference between (Oracle10g)
Difference between [message #400657] Wed, 29 April 2009 04:09 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,

What is difference between "dbms_sql.parse " AND "EXECUTE IMMEDIATE "
Re: Difference between [message #400667 is a reply to message #400657] Wed, 29 April 2009 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One can do much more things than the other.
Details in:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Difference between [message #400688 is a reply to message #400657] Wed, 29 April 2009 05:03 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
cur:=DBMS_SQL.OPEN_CURSOR;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
DBMS_SQL.PARSE(cur,sql_com,dbms_sql.v7);
row_proc:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);


EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL'

Re: Difference between [message #400693 is a reply to message #400688] Wed, 29 April 2009 05:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, for DDL, all you need to do is parse the command.
declare
  cur   integer;
begin
  cur:=DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur,'CREATE TABLE TEST_184 (col_1 number)',dbms_sql.v7);
  DBMS_SQL.CLOSE_CURSOR(cur);      
end;
/


DBMS_SQL is harder to use, but can be more flexible.
Re: Difference between [message #400718 is a reply to message #400688] Wed, 29 April 2009 06:19 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use "dbms_sql.v7" use "dbms_sql.native".

Regards
Michel
Previous Topic: subtract date
Next Topic: how to write a procedure .......
Goto Forum:
  


Current Time: Sat Dec 03 11:59:58 CST 2016

Total time taken to generate the page: 0.05686 seconds