Execute Immediate
Oracle Tips by Burleson

The Oracle EXECUTE IMMEDIATE command is one of the easiest commands available. The syntax is:

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

In its most basic form, Oracle EXECUTE IMMEDIATE takes only a single parameter and that is a command string.

Here is an example showing how to use dynamic DDL to create, drop and re-create a table:

BEGIN
EXECUTE IMMEDIATE 'create table abcd (efgh NUMBER)';
EXECUTE IMMEDIATE 'drop table abcd';
EXECUTE IMMEDIATE 'create table abcd (efgh VARCHAR2(10))';
END;

You can use this method to execute any DDL.

More than likely, you will use dynamic SQL to execute DML commands more often than DDL. With dynamic SQL you can issue inserts, updates and deletes just as you can with static SQL:

BEGIN
EXECUTE IMMEDIATE 'INSERT INTO abcd (efgh) VALUES (:text_string)'
         USING 'ijkl';
EXECUTE IMMEDIATE 'INSERT INTO abcd (efgh) VALUES (:text_string)'
         USING 'mnop';
EXECUTE IMMEDIATE 'UPDATE abcd ' ||
            'SET efgh = :text_string WHERE efgh = :second_string'
         USING 'qrst', 'mnop';
EXECUTE IMMEDIATE 'DELETE FROM abcd ' ||
            'WHERE efgh = :text_string '
         USING 'qrst';

END;

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 dynamic SQL to select your data back out.

DECLARE
v_data abcd.efgh%TYPE;

v_data_row abcd%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE 'SELECT efgh FROM abcd WHERE efgh = :text_string'
INTO v_data
USING 'ijkl';

DBMS_OUTPUT.PUT_LINE( 'Column Variable: ' || v_data );

EXECUTE IMMEDIATE 'SELECT * FROM abcd WHERE efgh = :text_string'
INTO v_data_row
USING 'ijkl';

DBMS_OUTPUT.PUT_LINE( 'Row Variable: ' || v_data_row.efgh );

END;

INFO: Column Variable: ijkl
INFO: Row Variable: ijkl

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.