Diff B/w EXECUTE IMMEDIATE AND DBMS_SQL

[kspolavarapu1]

What is the Diff B/w EXECUTE IMMEDIATE AND DBMS_SQL

[jamesravid]

"EXECUTE IMMEDIATE" is a new feature added in Oracle 9i. Before oracle9i you have to use only DBMS_SQL package in order to execute dynamic sql queries.

"EXECUTE IMMEDIATE" is simple to use and it provides better performance than DBMS_SQL.

[kspolavarapu1]

and i also found that in execute immediate DML statements need to be commited.Where as in DBMS_SQL they r auto commited .

[debasisdas]

a little change to james's statment

EXECUTE IMMEDIATE is available in oracle from version 8i not 9i as stated by james.

The following issues teeds to be kept in mind before using any of these two .

1. EXECUTE IMMEDIATE is used for functionalities such as OBJECTS and COLLECTIONS which is not supported in DBMS_SQL.
2. EXECUTE IMMEDIATE reprepairs the dynamic string everytime before execution, so it might create some overhead.
3. Using DBMS_SQL to execute DDL can lead to Deadlocks