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 dynamic SQL use intensively the concept of bind variable to improve performance and scalability.
The Native dynamic SQL code is more compact and much faster than calling the apps/search/search.jsp package.
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
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.
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:
a_null CHAR(1); -- set to NULL automatically at run time
EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;
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.
EXECUTE IMMEDIATE 'dbms_output.put_line(''No semicolon'')';
EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''semicolons''); END;';