ORACLE Execute Immediate

Execute Dynamic SQL Statement

The EXECUTE IMMEDIATE can be used to execute dynamic sql statement or on anynomous block in Oracle PL/SQL. Especially when we need to execute the data definition language (DDL) statement, such as create, drop, alter, etc. Of course you can use it on DML too like select, delete, insert. The Execute Immediate statement simply will execute sql or query on text forms.

Syntax:

EXECUTE IMMEDIATE <SQL>
  [INTO <variable list>]
  [USING <bind variable list>];
 

Example:

DECLARE
 query varchar2(1000);
 v_row table1%rowtype;
 v_tablename varchar2(10);

BEGIN
 query := 'create table table1 (id number, name varchar2(50))';
 execute immediate query;

 query := 'insert into table1 values (:a :b)';
 execute immediate query using 1, 'yourname';

 query := q'{select * from table1 where name=:a or name like '%:a%'}';
 execute immediate query into v_row using 'your';
 dbms_output.put_line(v_row.id||v_row.name);

 v_tablename := 'table1';
 query := 'delete from '||v_tablename||' where id=1';
 execute immediate query;

END;
/

Tips: From example above to solving the problem with quotes, try use q'{ }' instead the usual ' .
   
For more information about Execute Immediate see Oracle Database Documentation Library.