Example of EXECUTE IMMEDIATE usage

1. To run a DDL statement in PL/SQL.

begin
 execute immediate 'set role all';
end;

2. To pass values to a dynamic statement (USING clause).

declare
 l_depnam varchar2(20) := 'testing';
 l_loc    varchar2(10) := 'Dubai';
begin
 execute immediate 'insert into dept values (:1, :2, :3)'
   using 50, l_depnam, l_loc;
 commit;
end;

3. To retrieve values from a dynamic statement (INTO clause).

declare
 l_cnt    varchar2(20);
begin
 execute immediate 'select count(1) from emp'
   into l_cnt;
 dbms_output.put_line(l_cnt);
end;

4. To call a routine dynamically: The bind variables used for parameters of the routine have to be specified along with the parameter type. IN type is the default, others have to be specified explicitly.

declare
 l_routin   varchar2(100) := 'gen2161.get_rowcnt';
 l_tblnam   varchar2(20) := 'emp';
 l_cnt      number;
 l_status   varchar2(200);
begin
 execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
   using in l_tblnam, out l_cnt, in out l_status;

 if l_status != 'OK' then
    dbms_output.put_line('error');
 end if;
end;

5. To return value into a PL/SQL record type: The same option can be used for %rowtype variables also.

declare
 type empdtlrec is record (empno  number(4),
                           ename  varchar2(20),
                           deptno  number(2));
 empdtl empdtlrec;
begin
 execute immediate 'select empno, ename, deptno ' ||
                   'from emp where empno = 7934'
   into empdtl;
end;

6. To pass and retrieve values: The INTO clause should precede the USING clause.

declare
 l_dept    pls_integer := 20;
 l_nam     varchar2(20);
 l_loc     varchar2(20);
begin
 execute immediate 'select dname, loc from dept where deptno = :1'
   into l_nam, l_loc
   using l_dept ;
end;

7. Multi-row query option. Use the insert statement to populate a temp table for this option. Use the temporary table to carry out further processing. Alternatively, you may use REF cursors to by-pass this drawback.

declare
 l_sal   pls_integer := 2000;
begin
 execute immediate 'insert into temp(empno, ename) ' ||
                   '          select empno, ename from emp ' ||
                   '          where  sal > :1'
   using l_sal;
 commit;
end;

http://www.databasejournal.com/features/oracle/article.php/2109681/EXECUTE-IMMEDIATE-option-for-Dynamic-SQL-and-PLSQL.htm