EXECUTE IMMEDIATE

EXECUTE IMMEDIATE VS DBMS_SQL
EXECUTE IMMEDIATE provides better performance than DBMS_SQL.
EXECUTE IMMEDIATE" was added in Oracle 9i. DBMS_SQL package was used to execute
dynamic sql queries in earlier versions.

Using EXECUTE IMMEDIATE

1.
begin
execute immediate 'SQL Statement';
end;

2.To retrieve value from a dynamic statement
declare
var varchar2(20);
begin
execute immediate 'select count(*) from employee'
into var;
dbms_output.put_line(var);
end;

In case of static sql statement we use select count(*) into var from employee;

3.EXECUTE IMMEDIATE doesn't commits a DML transaction.An explicit commit should be done.

4.Using variables with EXECUTE IMMEDIATE

x number := 10000;
execute immediate 'select count(*) from employee where salary > '||x into var;