The syntax for the Oracle
EXECUTE IMMEDIATE statement as follows:
EXECUTE IMMEDIATE <SQL or SPL Commands>
[INTO <variable list>]
[USING <bind variable list>];
Quotes and execute immediate
When executing a string variable that contains
quotes it is important to "escape" the quote marks.
On 10g and beyond you can "escape" the quotes with two single quotes or
a "q" and curly brackets:
For example, this "execute immediate"
escapes the quotes with a q'{xxxxxxxx}'
sqlstring :=
q'{insert into x values( ' || i || ')}';
execute immediate sqlstring;
As we see, the Oracle EXECUTE IMMEDIATE statement can be used
to execute dynamic SQL statements. Oracle EXECUTE IMMEDIATE can also
build up statements to execute operations in which you do not know the
table names, or other properties.
The Oracle EXECUTE IMMEDIATE statement has a wide
variety of uses. For more information on Oracle EXECUTE IMMEDIATE see
the following links:
Oracle EXECUTE IMMEDIATE - Dynamic SQL and Bulk Operations
Oracle EXECUTE IMMEDIATE - How to execute dynamic
PL/SQL procedure calls
Late Binding and Runtime Binding in PL/SQL
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 execute immediate 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
Note: There are cases where you can get an insufficient privileges
error when executing a stored procedure that contains execute immediate
statements.
execute
immediate causes insufficient privileges over a database link