Static and Dynamic SQL within the context of Cursors
What is static and dynamic SQL?
The SQL in a cursor can be static or dynamic. In a static cursor, the user defines the exact syntax of the
SELECT statement, and execution of the statement does not change its syntax. On the other hand, you can build dynamic SQL with the DBMS_SQL package.
In a dynamic SQL, the exact syntax of the
SELECT statement is not defined at compilation time. It is generated when Oracle executes the PL/SQL block, however.
Using Native Dynamic SQL and DBMS_SQL
You can use native dynamic SQL to create general-purpose procedures and to execute DDL commands within PL/SQL.
You can use dynamic SQL to execute SQL commands that are built at run time rather than at program-creation time within stored procedural objects (such as packages) and in anonymous PL/SQL
blocks. For example, you can create procedures that create tables, that take table names as input parameters, or that take where clauses as input parameters.
Oracle PL/SQL Programming
Using EXECUTE IMMEDIATE
The script in the following example creates a procedure called ANYSTRING. The ANYSTRING procedure has a single input variable: a SQL command. When you execute the ANYSTRING procedure, you pass it the SQL command to execute; the SQL command may be either a DML command (such as select or insert) or a DDL command (such as create table).
create or replace procedure ANYSTRING(String IN VARCHAR2) AS
EXECUTE IMMEDIATE (String);
You can execute the ANYSTRING procedure via the execute command, as shown in the upcoming examples of DDL and DML commands.
Note that the strings are not terminated with semicolons. If you are going to run DDL commands through dynamic SQL, you must have been granted the necessary system privileges explicitly and not via roles.
If you do not have the CREATE TABLE system privilege explicitly granted to you, the following command will fail with an
ORA-01031: insufficient privileges error:
execute ANYSTRING('create table CD (Artist VARCHAR2(25), Title VARCHAR2(25)) ');
execute ANYSTRING('insert into CD values(''Glenn Gould'', ''Goldberg Variations'') ');
The first command in the preceding listing executes the following SQL command:
create table CD
The second command in the listing inserts a record into the CD table:
insert into CD values ('Glenn Gould', 'Goldberg Variations');
If the SQL statements passed to ANYSTRING contain quotes,
each quote should be replaced with a set of two quotes, as shown in the preceding execution of ANYSTRING for the insert command.
After each command, you will receive the following response:
PL/SQL procedure successfully completed.
Differences between static and dynamic SQL
Static and dynamic SQL are each appropriate for different circumstances. You should consider the differences between the two when determining whether static SQL or dynamic SQL is best for your application.
Flexibility of static SQL with host variables
The source form of a static SQL statement is embedded within an application program written in a host language like PL/I.
The statement is prepared before the program is executed and the operational form of the statement persists beyond the execution of the program.
Static SQL statements in a source program must be processed before the program is compiled. This processing can be accomplished through the precompiler or the coprocessor. The precompiler or the coprocessor checks the syntax of the SQL statements, turns them into host language comments, and generates host language statements to invoke .
The preparation of an SQL application program includes precompilation, the preparation of its static SQL statements, and compilation of the modified source program