Brajesh Singh Bhadoriya

 Profile Answers by Brajesh Singh Bhadoriya 

• Jan 25th, 2010

 

Execute immediate is used to prepare dynamic SQL. There might be scenario where one has to prepare the SQL statement at run time in a PL/SQL object. Execute immediate requires one SQL statement which can be stored in a varchar field and when this field is used with execute immediate, it runs that SQL statement.

For Example, if we want to fetch emp name for emp table based on the schema we passed and the emp number then we can use below sql statement.
EXECUTE IMMEDIATE 'Select empname into ' || v_empname || ' from ' || v_schema || '.emp where empno = ' || v_empno; 

Another important use of it is to use DDL statement in the PL/SQL object, as there is restriction on the use of DDL statement in PL/SQL object. If we want to truncate a table in a PL/SQL object we can use it in the following way:
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_TEMP';

Hope this clarifies the use of EXECUTE IMMEDIATELY.

Thanks,
Braj.

hmounir

 Profile Answers by hmounir 

• Jan 25th, 2010

 

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.