PL/SQL - Dynamic SQL
Table of Contents
1 - About
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 Native dynamic SQL code is more compact and much faster than calling the DBMS_SQL package.
2 - Articles Related
3 - Parameters, Bind Variable
3.1 - Passing Schema Object Names (Table, ...)
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.
3.2 - Passing Nulls to Dynamic SQL
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;
/
4 - Syntax
4.1 - When to Use or Omit the Semicolon with Dynamic SQL
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;