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 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.
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;