EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. With the help of EXECUTE IMMEDIATE statement we execute DDL statement.

Syntax:-
        EXECUTE IMMEDIATE <SQL or SPL Commands>
        [INTO <variable list>]
        [USING <bind variable list>];

       
Example:-

BEGIN
    EXECUTE IMMEDIATE 'create table XXC05_TEST (ID NUMBER,NAME VARCHAR2(20))';
    EXECUTE IMMEDIATE 'drop table XXC05_TEST';
    EXECUTE IMMEDIATE 'create table XXC05_TEST (EMP_NAME VARCHAR2(10))';
END;       


DECLARE
 L_CNT    VARCHAR2(20);
BEGIN
 EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM EMPLOYEES'
   INTO L_CNT;
 DBMS_OUTPUT.PUT_LINE(L_CNT);
END;


Note:-
    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;



Note:-
    As useful as DDL and DML are, a database is not very useful if you can't get your data out. You can also use execute immediate to select your data back out.