PL/SQL - Dynamic SQL

SQL statements that are only know until run time are called dynamic SQL statements.

To process a dynamic sql statement, you can use the following Native dynamic SQL statement:

• the PL/SQL - Execute Immediate statement with the BULK COLLECT INTO clause.
• the OPEN-FOR, FETCH, and CLOSE statements.

The Native dynamic SQL code is more compact and much faster than calling the DBMS_SQL package.

Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. You must build a string with a statement that includes the object names, then use EXECUTE IMMEDIATE to execute the statement:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/

Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.

The literal NULL is not allowed in the USING clause. To work around this restriction, replace the keyword NULL with an uninitialized variable:

DECLARE
   a_null CHAR(1); -- set to NULL automatically at run time
BEGIN
   EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;
END;
/

When building up a single SQL statement in a string, do not include any semicolon at the end.

When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block.

For example:

BEGIN
   EXECUTE IMMEDIATE 'dbms_output.put_line(''No semicolon'')';
   EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''semicolons''); END;';
END;

• Performing SQL Operations with Native Dynamic SQL
• Package DBMS_SQL
• PLSQL Reference