Execute Immediate:

 

The Execute Immediate statement, in Oracle, is a feature available from Oracle 8i. In the older versions, DBMS_SQL package was available to perform the above. The use of the Execute Immediate is easier compared to the DBMS_SQL package. Running a dynamic SQL, has disadvantages of its own, one of which is that the execution of a dynamic SQL is slower than running a static SQL. It is used to run SQLs generated on the fly and to run DDL statements in PL/SQL blocks. However, the use of dynamic SQLs needs to be minimized to the maximimum, for performance.

 

SYNTAX:

 

EXECUTE IMMEDIATE 'SQL statement as string'

[[INTO|BULK COLLECT INTO]

var1,...|PL/SQL Table Type,...]

[USING [IN|OUT|IN OUT] bind_var_subst1,...]

EXAMPLE:

 

CREATE OR REPLACE PROCEDURE pr_exec_imm_eg

(sql_tnam IN VARCHAR,

sql_sel IN VARCHAR,

sql_whr IN VARCHAR,

val IN VARCHAR)

AS

 

v_rec LONG;

v_sql VARCHAR2 (100);

 

TYPE tbltyp_recs IS TABLE OF LONG INDEX BY BINARY_INTEGER;

v_recs_tbl tbltyp_recs;

 

BEGIN

 

/*** The SQL which is to be executed is generated on the fly ***/

 

v_sql := 'SELECT ' || sql_sel || ' FROM '|| sql_tnam ||

' WHERE ' || sql_whr || ' = :vl';

 

/*** The value for the bind variable is substituted

inthe USING clause ***/

 

/*** The INTO clause is used if the result set contains

only one row ***/

 

EXECUTE IMMEDIATE v_sql INTO v_rec USING val;

 

DBMS_OUTPUT.PUT_LINE (sql_sel || ' = ' || v_rec ||

' in ' || sql_tnam || ' for ' || sql_whr || ' = ' || val );

 

EXCEPTION

 

WHEN TOO_MANY_ROWS THEN

 

/*** If the result set contains more than one row

a table type is used to collect all the rows ***/

 

EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_recs_tbl USING val;

 

DBMS_OUTPUT.PUT_LINE ('Values for ' || sql_sel || ' for ' ||

sql_whr || ' = ' || val || ' in ' || sql_tnam || ' are:');

 

/*** each and every value is retrieved from the PL/SQL table ***/

 

FOR i IN v_recs_tbl.FIRST..v_recs_tbl.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (v_recs_tbl (i));

END LOOP;

 

WHEN OTHERS THEN

 

DBMS_OUTPUT.PUT_LINE (SQLCODE || ': ' || SQLERRM);

END;

 

Executing this procedure,

 

EXECUTE pr_exec_imm_eg ('DEPT', 'DNAME', 'DEPTNO', '30')

DNAME = SALES in DEPT for DEPTNO = 30

 

PL/SQL procedure successfully completed.

 

EXECUTE pr_exec_imm_eg ('EMP', 'ENAME', 'DEPTNO', '30')

Values for ENAME for DEPTNO = 30 in EMP are:

ALLEN

WARD

MARTIN

JOSEPH

LEWIS

 

PL/SQL procedure successfully completed.

 

Above is a simple example to brief the use of the Execute Immediate statement. Similarly, the statement can be used to generate DDL statements and can be used to generate PL/SQL blocks as well.