Execute Immediate Drop Table Error

Execute Immediate Drop Table Error

_____________________________________________________________________________________________________________________
To drop a cluster and all its the tables, use the DROP CLUSTER statement with the INCLUDING TABLES clause to avoid dropping each table individually. See DROP CLUSTER. If the table is a base table for a view, a container or master table of a materialized view, or if it is referenced in a stored procedure, function, or package, then the database. You can do two things. Define the exception you want to ignore (here ORA-00942) add an undocumented (and not implemented) hint /.+ IF EXISTS./ that will pleased you management. Declare tabledoesnotexist exception; PRAGMA EXCEPTIONINIT (tabledoesnotexist, -942); begin execute immediate 'drop table continent /.+ IF EXISTS./'; exception when tabledoesnotexist then. In its most basic form, Oracle EXECUTE IMMEDIATE takes only a single parameter and that is a command string. Here is an example showing how to use dynamic DDL to create, drop and re-create a table: BEGIN EXECUTE IMMEDIATE 'create table abcd (efgh NUMBER)'; EXECUTE IMMEDIATE 'drop table. In a stored procedure that is defined with authid definer, exectuting an alter session set currentschema = with execute immediate does not affect the scope in which execute immediate is executed, but it does if the procedure is defined with authid currentschema, see this example and this research).
Error Description:
The procedure with execute immediate command is failing with ORA-01031: insufficient privileges. The procedure is trying to create a table and drop the table. The owner of the procedure has DBA role. Still the user cannot create the table or drop the table through execute immediate command. The user is able to create and drop the table using SQL*Plus prompt. 
Solution Description
Even though DBA role is given to the user, you have to explicitly grant the privileges whichever you used in the procedure. If you used create table command in execute immediate, you have to grant create any table privilege explicitly. See one example here.
Granting the DBA to user
SQL> connect / as sysdba



Creating the procedure with execute immediate.
SQL> connect scott
Connected.
srpt varchar2(2000);
srpt:='create table test(col1 number(2))';
srpt:='drop table test';
end; 


Executing the procedure and giving the error.
BEGIN testproc; END;
*
ORA-01031: insufficient privileges
ORA-06512: at line 1
Again granting the specific privileges to the user and execution succeded.
SQL> connect / as sysdba
SQL> grant create any table, drop any table to scott;
Grant succeeded.
SQL> connect scott
Connected.
USER is 'SCOTT'
SQL> exec testproc;
PL/SQL procedure successfully completed.
_____________________________________________________________________________________________________________________
This chapter shows you how to use Oracle Dynamic SQL, an advanced programming technique that adds flexibility and functionality to your applications. You will learn four methods for writing programs that accept and process SQL statements at run time. This chapter contains the following topics:
Note:
Oracle Dynamic SQL does not support object types, cursor variables, arrays of structs, DML returning clauses, Unicode variables, and LOBs. Use ANSI Dynamic SQL method 4 instead.
13.1 What is Dynamic SQL? Most database applications do a specific job. For example, a simple program might prompt the user for an employee number, then update rows in the EMP and DEPT tables. In this case, you know the makeup of the UPDATE statement at precompile time. That is, you know which tables might be changed, the constraints defined for each table and column, which columns might be updated, and the datatype of each column.
However, some applications must accept (or build) and process a variety of SQL statements at run time. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the statement's makeup is unknown until run time. Such statements can, and probably will, change from execution to execution. They are aptly called dynamic SQL statements.
Unlike static SQL statements, dynamic SQL statements are not embedded in your source program. Instead, they are stored in character strings input to or built by the program at run time. They can be entered interactively or read from a file.
13.2 Advantages and Disadvantages of Dynamic SQL Host programs that accept and process dynamically defined SQL statements are more versatile than plain embedded SQL programs. Dynamic SQL statements can be built interactively with input from users having little or no knowledge of SQL.
For example, your program might simply prompt users for a search condition to be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement. A more complex program might allow users to choose from menus listing SQL operations, table and view names, column names, and so on. Thus, dynamic SQL lets you write highly flexible applications.
However, some dynamic queries require complex coding, the use of special data structures, and more runtime processing. While you might not notice the added processing time, you might find the coding difficult unless you fully understand dynamic SQL concepts and methods.
13.3 When to Use Dynamic SQL 

In practice, static SQL will meet nearly all your programming needs. Use dynamic SQL only if you need its open-ended flexibility. Its use is suggested when one of the following items is unknown at precompile time:
Text of the SQL statement (commands, clauses, and so on) 
The number of host variables 
The datatypes of host variables 
References to database objects such as columns, indexes, sequences, tables, usernames, and views 
13.4 Requirements for Dynamic SQL Statements To represent a dynamic SQL statement, a character string must contain the text of a valid SQL statement, but not contain the EXEC SQL clause, or the statement terminator, or any of the following embedded SQL commands:
ALLOCATE
CLOSE 
DECLARE 
DESCRIBE 
EXECUTE 
FETCH 
FREE
GET
INCLUDE 
OPEN 
PREPARE 
SET
WHENEVER 
In most cases, the character string can contain dummy host variables. They hold places in the SQL statement for actual host variables. Because dummy host variables are just placeholders, you do not declare them and can name them anything you like. For example, Oracle makes no distinction between the following two strings:

13.5 How Dynamic SQL Statements are Processed Typically, an application program prompts the user for the text of a SQL statement and the values of host variables used in the statement. Oracle then parses the SQL statement to ensure it meets syntax rules.
Next, Oracle binds the host variables to the SQL statement. That is, Oracle gets the addresses of the host variables so that it can read or write their values. 
Then Oracle executes the SQL statement. That is, Oracle does what the SQL statement requested, such as deleting rows from a table. 
The SQL statement can be executed repeatedly using new values for the host variables.
13.6 Methods for Using Dynamic SQL This section introduces four methods you can use to define dynamic SQL statements. It briefly describes the capabilities and limitations of each method, then offers guidelines for choosing the right method. Later sections show you how to use the methods, and include example programs that you can study.
The four methods are increasingly general. That is, Method 2 encompasses Method 1, Method 3 encompasses Methods 1 and 2, and so on. However, each method is most useful for handling a certain kind of SQL statement, as Table 13-1 shows: 
Table 13-1 Methods for Using Dynamic SQL
MethodKind of SQL Statement
1
non-query without host variables
2
non-query with known number of input host variables
3
query with known number of select-list items and input host variables
4
query with unknown number of select-list items or input host variables
Note:
The term select-list item includes column names and expressions such as SAL * 1.10 and MAX(SAL). 
13.6.1 Method 1 This method lets your program accept or build a dynamic SQL statement, then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables. For example, the following host strings qualify:
With Method 1, the SQL statement is parsed every time it is executed. 
13.6.2 Method 2 This method lets your program accept or build a dynamic SQL statement, then process it using the PREPARE and EXECUTE commands. The SQL statement must not be a query. The number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time. For example, the following host strings fall into this category: 
With Method 2, the SQL statement is parsed just once, but can be executed many times with different values for the host variables. SQL data definition statements such as CREATE and GRANT are executed when they are PREPAREd.
13.6.3 Method 3 This method lets your program accept or build a dynamic query, then process it using the PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables must be known at precompile time. For example, the following host strings qualify:
13.6.4 Method 4 This method lets your program accept or build a dynamic SQL statement, then process it using descriptors. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables can be unknown until run time. For example, the following host strings fall into this category:
Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables. 
13.6.5 Guidelines With all four methods, you must store the dynamic SQL statement in a character string, which must be a host variable or quoted literal. When you store the SQL statement in the string, omit the keywords EXEC SQL and the ';' statement terminator. 
With Methods 2 and 3, the number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time. 
Each succeeding method imposes fewer constraints on your application, but is more difficult to code. As a rule, use the simplest method you can. However, if a dynamic SQL statement will be executed repeatedly by Method 1, use Method 2 instead to avoid reparsing for each execution. 
Method 4 provides maximum flexibility, but requires complex coding and a full understanding of dynamic SQL concepts. In general, use Method 4 only if you cannot use Methods 1, 2, or 3. 

Method	Kind of SQL Statement
1	non-query without host variables
2	non-query with known number of input host variables
3	query with known number of select-list items and input host variables
4	query with unknown number of select-list items or input host variables

The decision logic in Figure 13-1 will help you choose the right method.
13.6.5.1 About Avoiding Common Errors If you precompile using the command-line option DBMS=V6_CHAR, blank-pad the array before storing the SQL statement. That way, you clear extraneous characters. This is especially important when you reuse the array for different SQL statements. As a rule, always initialize (or re-initialize) the host string before storing the SQL statement. Do not null-terminate the host string. Oracle does not recognize the null terminator as an end-of-string sentinel. Instead, Oracle treats it as part of the SQL statement. 
If you precompile with the command-line option DBMS=V8, make sure that the string is null terminated before you execute the PREPARE or EXECUTE IMMEDIATE statement. Movist for mac.
Regardless of the value of DBMS, if you use a VARCHAR variable to store the dynamic SQL statement, make sure the length of the VARCHAR is set (or reset) correctly before you execute the PREPARE or EXECUTE IMMEDIATE statement.
Figure 13-1 Choosing the Right Method

Description of 'Figure 13-1 Choosing the Right Method'13.7 Using Method 1 The simplest kind of dynamic SQL statement results only in 'success' or 'failure' and uses no host variables. Some examples follow:
Method 1 parses, then immediately executes the SQL statement using the EXECUTE IMMEDIATE command. The command is followed by a character string (host variable or literal) containing the SQL statement to be executed, which cannot be a query. 
The syntax of the EXECUTE IMMEDIATE statement follows:
In the following example, you use the host variable dyn_stmt to store SQL statements input by the user: 
You can also use string literals, as the following example shows: 
Because EXECUTE IMMEDIATE parses the input SQL statement before every execution, Method 1 is best for statements that are executed only once. Data definition language statements usually fall into this category. 
13.7.1 Example Program: Dynamic SQL Method 1 The following program uses dynamic SQL Method 1 to create a table, insert a row, commit the insert, then drop the table. This program is available on-line in your demo directory in the file sample6.pc.
13.8 Using Method 2 What Method 1 does in one step, Method 2 does in two. The dynamic SQL statement, which cannot be a query, is first PREPAREd (named and parsed), then EXECUTEd. 
With Method 2, the SQL statement can contain placeholders for input host variables and indicator variables. You can PREPARE the SQL statement once, then EXECUTE it repeatedly using different values of the host variables. Furthermore, you need not rePREPARE the SQL statement after a COMMIT or ROLLBACK (unless you log off and reconnect).
You can use EXECUTE for non-queries with Method 4. 
The syntax of the PREPARE statement follows: 
PREPARE parses the SQL statement and gives it a name. 
The statement_name is an identifier used by the precompiler, not a host or program variable, and should not be declared in the Declare Section. It simply designates the PREPAREd statement you want to EXECUTE. 
The syntax of the EXECUTE statement is 
where host_variable_list stands for the following syntax: 
EXECUTE executes the parsed SQL statement, using the values supplied for each input host variable. 
In the following example, the input SQL statement contains the placeholder n: 
With Method 2, you must know the datatypes of input host variables at precompile time. In the last example, emp_number was declared as an int. It could also have been declared as type float, or even a char, because Oracle supports all these datatype conversions to the internal Oracle NUMBER datatype. 
13.8.1 The USING Clause When the SQL statement is EXECUTEd, input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement.
Every placeholder in the PREPAREd dynamic SQL statement must correspond to a different host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd statement, each appearance must correspond to a host variable in the USING clause.
The names of the placeholders need not match the names of the host variables. However, the order of the placeholders in the PREPAREd dynamic SQL statement must match the order of corresponding host variables in the USING clause. 
If one of the host variables in the USING clause is an array, all must be arrays. 
To specify NULLs, you can associate indicator variables with host variables in the USING clause. 
13.8.2 Example Program: Dynamic SQL Method 2 The following program uses dynamic SQL Method 2 to insert two rows into the EMP table, then delete them. This program is available on-line in your demo directory, in the file sample7.pc.
Execute Immediate Drop Table13.9 Using Method 3 Method 3 is similar to Method 2 but combines the PREPARE statement with the statements needed to define and manipulate a cursor. This allows your program to accept and process queries. In fact, if the dynamic SQL statement is a query, you must use Method 3 or 4.
For Method 3, the number of columns in the query select list and the number of placeholders for input host variables must be known at precompile time. However, the names of database objects such as tables and columns need not be specified until run time. Names of database objects cannot be host variables. Clauses that limit, group, and sort query results (such as WHERE, GROUP BY, and ORDER BY) can also be specified at run time. 
With Method 3, you use the following sequence of embedded SQL statements:
Scrollable Cursors can also be used with Method 3. The following sequence of embedded SQL statements must be used for scrollable cursors.
Now we look at what each statement does. 
13.9.1 PREPARE (Dynamic SQL) PREPARE parses the dynamic SQL statement and gives it a name. In the following example, PREPARE parses the query stored in the character string select_stmt and gives it the name sql_stmt:
Commonly, the query WHERE clause is input from a terminal at run time or is generated by the application. 
The identifier sql_stmt is not a host or program variable, but must be unique. It designates a particular dynamic SQL statement. 
The following statement is correct also:
The following prepare statement, which uses the '%' wildcard, is correct also:
13.9.2 DECLARE (Dynamic SQL) DECLARE defines a cursor by giving it a name and associating it with a specific query. Continuing our example, DECLARE defines a cursor named emp_cursor and associates it with sql_stmt, as follows: 
The identifiers sql_stmt and emp_cursor are not host or program variables, but must be unique. If you declare two cursors using the same statement name, the precompiler considers the two cursor names synonymous. 
We can define a scrollable cursor named emp_cursor and associate it with sql_stmt as follows:
For example, if you execute the statements 
when you OPEN emp_cursor, you will process the dynamic SQL statement stored in delete_stmt, not the one stored in select_stmt.
13.9.3 OPEN (Dynamic SQL) OPEN allocates an Oracle cursor, binds input host variables, and executes the query, identifying its active set. OPEN also positions the cursor on the first row in the active set and zeroes the rows-processed count kept by the third element of sqlerrd in the SQLCA. Input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement. 
In our example, OPEN allocates emp_cursor and assigns the host variable salary to the WHERE clause, as follows: 
13.9.4 FETCH (Dynamic SQL) FETCH returns a row from the active set, assigns column values in the select list to corresponding host variables in the INTO clause, and advances the cursor to the next row. If there are no more rows, FETCH returns the 'no data found' Oracle error code to sqlca.sqlcode. 
In our example, FETCH returns a row from the active set and assigns the values of columns MGR and JOB to host variables mgr_number and job_title, as follows: 
If the cursor is declared in SCROLL mode, you can then use the various FETCH orientation modes to randomly access the result set.
13.9.5 CLOSE (Dynamic SQL) CLOSE disables the cursor. Once you CLOSE a cursor, you can no longer FETCH from it. 
In our example, CLOSE disables emp_cursor, as follows: 
13.9.6 Example Program: Dynamic SQL Method 3 The following program uses dynamic SQL Method 3 to retrieve the names of all employees in a given department from the EMP table. This program is available on-line in your demo directory, in the file sample8.pc 
13.10 Using Method 4 This section gives an overview of Oracle Dynamic SQL Method 4. Oracle Dynamic SQL Method 4 does not support object types, results sets, arrays of structs, or LOBs.
ANSI SQL does support all datatypes. Use ANSI SQL for all new applications. 
There is a kind of dynamic SQL statement that your program cannot process using Method 3. When the number of select-list items or placeholders for input host variables is unknown until run time, your program must use a descriptor. A descriptor is an area of memory used by your program and Oracle to hold a complete description of the variables in a dynamic SQL statement.
Recall that for a multirow query, you FETCH selected column values INTO a list of declared output host variables. If the select list is unknown, the host-variable list cannot be established at precompile time by the INTO clause. For example, you know the following query returns two column values: 
However, if you let the user define the select list, you might not know how many column values the query will return. 
Related Topics
13.10.1  Need for the SQLDA To process this kind of dynamic query, your program must issue the DESCRIBE SELECT LIST command and declare a data structure called the SQL Descriptor Area (SQLDA). Because it holds descriptions of columns in the query select list, this structure is also called a select descriptor.
Likewise, if a dynamic SQL statement contains an unknown number of placeholders for input host variables, the host-variable list cannot be established at precompile time by the USING clause. 
To process the dynamic SQL statement, your program must issue the DESCRIBE BIND VARIABLES command and declare another kind of SQLDA called a bind descriptor to hold descriptions of the placeholders for input host variables. (Input host variables are also called bind variables.)
If your program has more than one active SQL statement (it might have OPENed two or more cursors, for example), each statement must have its own SQLDA(s). However, non-concurrent cursors can reuse SQLDAs. There is no set limit on the number of SQLDAs in a program. 
13.10.2 The DESCRIBE Statement DESCRIBE initializes a descriptor to hold descriptions of select-list items or input host variables. 
If you supply a select descriptor, the DESCRIBE SELECT LIST statement examines each select-list item in a PREPAREd dynamic query to determine its name, datatype, constraints, length, scale, and precision. It then stores this information in the select descriptor.
If you supply a bind descriptor, the DESCRIBE BIND VARIABLES statement examines each placeholder in a PREPAREd dynamic SQL statement to determine its name, length, and the datatype of its associated input host variable. It then stores this information in the bind descriptor for your use. For example, you might use placeholder names to prompt the user for the values of input host variables.
13.10.3 What is a SQLDA? A SQLDA is a host-program data structure that holds descriptions of select-list items or input host variables.
SQLDA variables are not defined in the Declare Section. 
The select SQLDA contains the following information about a query select list:
Maximum number of columns that can be DESCRIBEd 
Actual number of columns found by DESCRIBE 
Addresses of buffers to store column values 
Lengths of column values 
Datatypes of column values 
Addresses of indicator-variable values 
Addresses of buffers to store column names 
Sizes of buffers to store column names 
Current lengths of column names 
The bind SQLDA contains the following information about the input host variables in a SQL statement: 
Maximum number of placeholders that can be DESCRIBEd 
Actual number of placeholders found by DESCRIBE 
Addresses of input host variables 
Lengths of input host variables 
Datatypes of input host variables 
Addresses of indicator variables 
Addresses of buffers to store placeholder names 
Sizes of buffers to store placeholder names 
Current lengths of placeholder names 
Addresses of buffers to store indicator-variable names 
Sizes of buffers to store indicator-variable names 
Current lengths of indicator-variable names 
See Also:
 Oracle Dynamic SQL: Method 4 for information on the SQLDA structure and variable names. 
13.10.4 About Implementing Oracle Method 4 With Oracle Method 4, you generally use the following sequence of embedded SQL statements:

In practice, static SQL will meet nearly all your programming needs. Use dynamic SQL only if you need its open-ended flexibility. Its use is suggested when one of the following items is unknown at precompile time:
Text of the SQL statement (commands, clauses, and so on) 
The number of host variables 
The datatypes of host variables 
References to database objects such as columns, indexes, sequences, tables, usernames, and views 
13.4 Requirements for Dynamic SQL Statements To represent a dynamic SQL statement, a character string must contain the text of a valid SQL statement, but not contain the EXEC SQL clause, or the statement terminator, or any of the following embedded SQL commands:
ALLOCATE
CLOSE 
DECLARE 
DESCRIBE 
EXECUTE 
FETCH 
FREE
GET
INCLUDE 
OPEN 
PREPARE 
SET
WHENEVER 
In most cases, the character string can contain dummy host variables. They hold places in the SQL statement for actual host variables. Because dummy host variables are just placeholders, you do not declare them and can name them anything you like. For example, Oracle makes no distinction between the following two strings:
13.5 How Dynamic SQL Statements are Processed Typically, an application program prompts the user for the text of a SQL statement and the values of host variables used in the statement. Oracle then parses the SQL statement to ensure it meets syntax rules.
Next, Oracle binds the host variables to the SQL statement. That is, Oracle gets the addresses of the host variables so that it can read or write their values. 
Then Oracle executes the SQL statement. That is, Oracle does what the SQL statement requested, such as deleting rows from a table. 
The SQL statement can be executed repeatedly using new values for the host variables.
13.6 Methods for Using Dynamic SQL This section introduces four methods you can use to define dynamic SQL statements. It briefly describes the capabilities and limitations of each method, then offers guidelines for choosing the right method. Later sections show you how to use the methods, and include example programs that you can study.
The four methods are increasingly general. That is, Method 2 encompasses Method 1, Method 3 encompasses Methods 1 and 2, and so on. However, each method is most useful for handling a certain kind of SQL statement, as Table 13-1 shows: 
Table 13-1 Methods for Using Dynamic SQL
MethodKind of SQL Statement
1
non-query without host variables
2
non-query with known number of input host variables
3
query with known number of select-list items and input host variables
4
query with unknown number of select-list items or input host variables
Note:
The term select-list item includes column names and expressions such as SAL * 1.10 and MAX(SAL). 
13.6.1 Method 1 This method lets your program accept or build a dynamic SQL statement, then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables. For example, the following host strings qualify:
With Method 1, the SQL statement is parsed every time it is executed. 
13.6.2 Method 2 This method lets your program accept or build a dynamic SQL statement, then process it using the PREPARE and EXECUTE commands. The SQL statement must not be a query. The number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time. For example, the following host strings fall into this category: 
With Method 2, the SQL statement is parsed just once, but can be executed many times with different values for the host variables. SQL data definition statements such as CREATE and GRANT are executed when they are PREPAREd.
13.6.3 Method 3 This method lets your program accept or build a dynamic query, then process it using the PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables must be known at precompile time. For example, the following host strings qualify:
13.6.4 Method 4 This method lets your program accept or build a dynamic SQL statement, then process it using descriptors. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables can be unknown until run time. For example, the following host strings fall into this category:
Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables. 
13.6.5 Guidelines With all four methods, you must store the dynamic SQL statement in a character string, which must be a host variable or quoted literal. When you store the SQL statement in the string, omit the keywords EXEC SQL and the ';' statement terminator. 
With Methods 2 and 3, the number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time. 
Each succeeding method imposes fewer constraints on your application, but is more difficult to code. As a rule, use the simplest method you can. However, if a dynamic SQL statement will be executed repeatedly by Method 1, use Method 2 instead to avoid reparsing for each execution. 
Method 4 provides maximum flexibility, but requires complex coding and a full understanding of dynamic SQL concepts. In general, use Method 4 only if you cannot use Methods 1, 2, or 3. 
The decision logic in Figure 13-1 will help you choose the right method.
13.6.5.1 About Avoiding Common Errors If you precompile using the command-line option DBMS=V6_CHAR, blank-pad the array before storing the SQL statement. That way, you clear extraneous characters. This is especially important when you reuse the array for different SQL statements. As a rule, always initialize (or re-initialize) the host string before storing the SQL statement. Do not null-terminate the host string. Oracle does not recognize the null terminator as an end-of-string sentinel. Instead, Oracle treats it as part of the SQL statement. 
If you precompile with the command-line option DBMS=V8, make sure that the string is null terminated before you execute the PREPARE or EXECUTE IMMEDIATE statement. Movist for mac.
Regardless of the value of DBMS, if you use a VARCHAR variable to store the dynamic SQL statement, make sure the length of the VARCHAR is set (or reset) correctly before you execute the PREPARE or EXECUTE IMMEDIATE statement.
Figure 13-1 Choosing the Right Method

Description of 'Figure 13-1 Choosing the Right Method'13.7 Using Method 1 The simplest kind of dynamic SQL statement results only in 'success' or 'failure' and uses no host variables. Some examples follow:
Method 1 parses, then immediately executes the SQL statement using the EXECUTE IMMEDIATE command. The command is followed by a character string (host variable or literal) containing the SQL statement to be executed, which cannot be a query. 
The syntax of the EXECUTE IMMEDIATE statement follows:
In the following example, you use the host variable dyn_stmt to store SQL statements input by the user: 
You can also use string literals, as the following example shows: 
Because EXECUTE IMMEDIATE parses the input SQL statement before every execution, Method 1 is best for statements that are executed only once. Data definition language statements usually fall into this category. 
13.7.1 Example Program: Dynamic SQL Method 1 The following program uses dynamic SQL Method 1 to create a table, insert a row, commit the insert, then drop the table. This program is available on-line in your demo directory in the file sample6.pc.
13.8 Using Method 2 What Method 1 does in one step, Method 2 does in two. The dynamic SQL statement, which cannot be a query, is first PREPAREd (named and parsed), then EXECUTEd. 
With Method 2, the SQL statement can contain placeholders for input host variables and indicator variables. You can PREPARE the SQL statement once, then EXECUTE it repeatedly using different values of the host variables. Furthermore, you need not rePREPARE the SQL statement after a COMMIT or ROLLBACK (unless you log off and reconnect).
You can use EXECUTE for non-queries with Method 4. 
The syntax of the PREPARE statement follows: 
PREPARE parses the SQL statement and gives it a name. 
The statement_name is an identifier used by the precompiler, not a host or program variable, and should not be declared in the Declare Section. It simply designates the PREPAREd statement you want to EXECUTE. 
The syntax of the EXECUTE statement is 
where host_variable_list stands for the following syntax: 
EXECUTE executes the parsed SQL statement, using the values supplied for each input host variable. 
In the following example, the input SQL statement contains the placeholder n: 
With Method 2, you must know the datatypes of input host variables at precompile time. In the last example, emp_number was declared as an int. It could also have been declared as type float, or even a char, because Oracle supports all these datatype conversions to the internal Oracle NUMBER datatype. 
13.8.1 The USING Clause When the SQL statement is EXECUTEd, input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement.
Every placeholder in the PREPAREd dynamic SQL statement must correspond to a different host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd statement, each appearance must correspond to a host variable in the USING clause.
The names of the placeholders need not match the names of the host variables. However, the order of the placeholders in the PREPAREd dynamic SQL statement must match the order of corresponding host variables in the USING clause. 
If one of the host variables in the USING clause is an array, all must be arrays. 
To specify NULLs, you can associate indicator variables with host variables in the USING clause. 
13.8.2 Example Program: Dynamic SQL Method 2 The following program uses dynamic SQL Method 2 to insert two rows into the EMP table, then delete them. This program is available on-line in your demo directory, in the file sample7.pc.
Execute Immediate Drop Table13.9 Using Method 3 Method 3 is similar to Method 2 but combines the PREPARE statement with the statements needed to define and manipulate a cursor. This allows your program to accept and process queries. In fact, if the dynamic SQL statement is a query, you must use Method 3 or 4.
For Method 3, the number of columns in the query select list and the number of placeholders for input host variables must be known at precompile time. However, the names of database objects such as tables and columns need not be specified until run time. Names of database objects cannot be host variables. Clauses that limit, group, and sort query results (such as WHERE, GROUP BY, and ORDER BY) can also be specified at run time. 
With Method 3, you use the following sequence of embedded SQL statements:
Scrollable Cursors can also be used with Method 3. The following sequence of embedded SQL statements must be used for scrollable cursors.
Now we look at what each statement does. 
13.9.1 PREPARE (Dynamic SQL) PREPARE parses the dynamic SQL statement and gives it a name. In the following example, PREPARE parses the query stored in the character string select_stmt and gives it the name sql_stmt:
Commonly, the query WHERE clause is input from a terminal at run time or is generated by the application. 
The identifier sql_stmt is not a host or program variable, but must be unique. It designates a particular dynamic SQL statement. 
The following statement is correct also:
The following prepare statement, which uses the '%' wildcard, is correct also:
13.9.2 DECLARE (Dynamic SQL) DECLARE defines a cursor by giving it a name and associating it with a specific query. Continuing our example, DECLARE defines a cursor named emp_cursor and associates it with sql_stmt, as follows: 
The identifiers sql_stmt and emp_cursor are not host or program variables, but must be unique. If you declare two cursors using the same statement name, the precompiler considers the two cursor names synonymous. 
We can define a scrollable cursor named emp_cursor and associate it with sql_stmt as follows:
For example, if you execute the statements 
when you OPEN emp_cursor, you will process the dynamic SQL statement stored in delete_stmt, not the one stored in select_stmt.
13.9.3 OPEN (Dynamic SQL) OPEN allocates an Oracle cursor, binds input host variables, and executes the query, identifying its active set. OPEN also positions the cursor on the first row in the active set and zeroes the rows-processed count kept by the third element of sqlerrd in the SQLCA. Input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement. 
In our example, OPEN allocates emp_cursor and assigns the host variable salary to the WHERE clause, as follows: 
13.9.4 FETCH (Dynamic SQL) FETCH returns a row from the active set, assigns column values in the select list to corresponding host variables in the INTO clause, and advances the cursor to the next row. If there are no more rows, FETCH returns the 'no data found' Oracle error code to sqlca.sqlcode. 
In our example, FETCH returns a row from the active set and assigns the values of columns MGR and JOB to host variables mgr_number and job_title, as follows: 
If the cursor is declared in SCROLL mode, you can then use the various FETCH orientation modes to randomly access the result set.
13.9.5 CLOSE (Dynamic SQL) CLOSE disables the cursor. Once you CLOSE a cursor, you can no longer FETCH from it. 
In our example, CLOSE disables emp_cursor, as follows: 
13.9.6 Example Program: Dynamic SQL Method 3 The following program uses dynamic SQL Method 3 to retrieve the names of all employees in a given department from the EMP table. This program is available on-line in your demo directory, in the file sample8.pc 
13.10 Using Method 4 This section gives an overview of Oracle Dynamic SQL Method 4. Oracle Dynamic SQL Method 4 does not support object types, results sets, arrays of structs, or LOBs.
ANSI SQL does support all datatypes. Use ANSI SQL for all new applications. 
There is a kind of dynamic SQL statement that your program cannot process using Method 3. When the number of select-list items or placeholders for input host variables is unknown until run time, your program must use a descriptor. A descriptor is an area of memory used by your program and Oracle to hold a complete description of the variables in a dynamic SQL statement.
Recall that for a multirow query, you FETCH selected column values INTO a list of declared output host variables. If the select list is unknown, the host-variable list cannot be established at precompile time by the INTO clause. For example, you know the following query returns two column values: 
However, if you let the user define the select list, you might not know how many column values the query will return. 
Related Topics
13.10.1  Need for the SQLDA To process this kind of dynamic query, your program must issue the DESCRIBE SELECT LIST command and declare a data structure called the SQL Descriptor Area (SQLDA). Because it holds descriptions of columns in the query select list, this structure is also called a select descriptor.
Likewise, if a dynamic SQL statement contains an unknown number of placeholders for input host variables, the host-variable list cannot be established at precompile time by the USING clause. 
To process the dynamic SQL statement, your program must issue the DESCRIBE BIND VARIABLES command and declare another kind of SQLDA called a bind descriptor to hold descriptions of the placeholders for input host variables. (Input host variables are also called bind variables.)
If your program has more than one active SQL statement (it might have OPENed two or more cursors, for example), each statement must have its own SQLDA(s). However, non-concurrent cursors can reuse SQLDAs. There is no set limit on the number of SQLDAs in a program. 
13.10.2 The DESCRIBE Statement DESCRIBE initializes a descriptor to hold descriptions of select-list items or input host variables. 
If you supply a select descriptor, the DESCRIBE SELECT LIST statement examines each select-list item in a PREPAREd dynamic query to determine its name, datatype, constraints, length, scale, and precision. It then stores this information in the select descriptor.
If you supply a bind descriptor, the DESCRIBE BIND VARIABLES statement examines each placeholder in a PREPAREd dynamic SQL statement to determine its name, length, and the datatype of its associated input host variable. It then stores this information in the bind descriptor for your use. For example, you might use placeholder names to prompt the user for the values of input host variables.
13.10.3 What is a SQLDA? A SQLDA is a host-program data structure that holds descriptions of select-list items or input host variables.
SQLDA variables are not defined in the Declare Section. 
The select SQLDA contains the following information about a query select list:
Maximum number of columns that can be DESCRIBEd 
Actual number of columns found by DESCRIBE 
Addresses of buffers to store column values 
Lengths of column values 
Datatypes of column values 
Addresses of indicator-variable values 
Addresses of buffers to store column names 
Sizes of buffers to store column names 
Current lengths of column names 
The bind SQLDA contains the following information about the input host variables in a SQL statement: 
Maximum number of placeholders that can be DESCRIBEd 
Actual number of placeholders found by DESCRIBE 
Addresses of input host variables 
Lengths of input host variables 
Datatypes of input host variables 
Addresses of indicator variables 
Addresses of buffers to store placeholder names 
Sizes of buffers to store placeholder names 
Current lengths of placeholder names 
Addresses of buffers to store indicator-variable names 
Sizes of buffers to store indicator-variable names 
Current lengths of indicator-variable names 
See Also:
 Oracle Dynamic SQL: Method 4 for information on the SQLDA structure and variable names. 
13.10.4 About Implementing Oracle Method 4 With Oracle Method 4, you generally use the following sequence of embedded SQL statements:
However, select and bind descriptors need not work in tandem. So, if the number of columns in a query select list is known, but the number of placeholders for input host variables is unknown, you can use the Method 4 OPEN statement with the following Method 3 FETCH statement: 
Conversely, if the number of placeholders for input host variables is known, but the number of columns in the select list is unknown, you can use the Method 3 OPEN statement 
with the Method 4 FETCH statement. 
EXECUTE can be used for nonqueries with Method 4. 
Execute Immediate Drop Table Error Definition13.10.5 Restriction In Dynamic SQL Method 4, you cannot bind a host array to a PL/SQL procedure with a parameter of type 'table.'
13.11 About Using the DECLARE STATEMENT Statement With Methods 2, 3, and 4, you might need to use the statement
where db_name and statement_name are identifiers used by the precompiler, not host or program variables. 
DECLARE STATEMENT declares the name of a dynamic SQL statement so that the statement can be referenced by PREPARE, EXECUTE, DECLARE CURSOR, and DESCRIBE. It is required if you want to execute the dynamic SQL statement at a nondefault database. An example using Method 2 follows:
In the example, remote_db tells Oracle where to EXECUTE the SQL statement. 
With Methods 3 and 4, DECLARE STATEMENT is also required if the DECLARE CURSOR statement precedes the PREPARE statement, as shown in the following example: 
The usual sequence of statements is 
13.11.1 About Using Host Arrays The use of host arrays in static SQL and dynamic SQL is similar. For example, to use input host arrays with dynamic SQL Method 2, simply use the syntax
where host_array_list contains one or more host arrays. 
Similarly, to use input host arrays with Method 3, use the following syntax: 
To use output host arrays with Method 3, use the following syntax: 
With Method 4, you must use the optional FOR clause to tell Oracle the size of your input or output host array. 
Oracle Execute Immediate Call13.12 About Using PL/SQL The Pro*C/C++ Precompiler treats a PL/SQL block like a single SQL statement. So, like a SQL statement, a PL/SQL block can be stored in a string host variable or literal. When you store the PL/SQL block in the string, omit the keywords EXEC SQL EXECUTE, the keyword END-EXEC, and the ';' statement terminator.
However, there are two differences in the way the precompiler handles SQL and PL/SQL: 
The precompiler treats all PL/SQL host variables as input host variables whether they serve as input or output host variables (or both) inside the PL/SQL block. 
You cannot FETCH from a PL/SQL block because it might contain any number of SQL statements. 
13.12.1 With Method 1 If the PL/SQL block contains no host variables, you can use Method 1 to EXECUTE the PL/SQL string in the usual way.
13.12.2 With Method 2 If the PL/SQL block contains a known number of input and output host variables, you can use Method 2 to PREPARE and EXECUTE the PL/SQL string in the usual way.
You must put all host variables in the USING clause. When the PL/SQL string is EXECUTEd, host variables in the USING clause replace corresponding placeholders in the PREPAREd string. Though the precompiler treats all PL/SQL host variables as input host variables, values are assigned correctly. Input (program) values are assigned to input host variables, and output (column) values are assigned to output host variables. 
Every placeholder in the PREPAREd PL/SQL string must correspond to a host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd string, each appearance must correspond to a host variable in the USING clause.
13.12.3 With Method 3 Methods 2 and 3 are the same except that Method 3 allows FETCHing. Since you cannot FETCH from a PL/SQL block, just use Method 2 instead.
13.12.4 With Oracle Method 4 If the PL/SQL block contains an unknown number of input or output host variables, you must use Method 4.
Execute Immediate SyntaxTo use Method 4, you set up one bind descriptor for all the input and output host variables. Executing DESCRIBE BIND VARIABLES stores information about input and output host variables in the bind descriptor. Because the precompiler treats all PL/SQL host variables as input host variables, executing DESCRIBE SELECT LIST has no effect. 
WARNING:
In dynamic SQL Method 4, you cannot bind a host array to a PL/SQL procedure with a parameter of type 'table.'
WARNING:
Do not use ANSI-style Comments (- -) in a PL/SQL block that will be processed dynamically because end-of-line characters are ignored. As a result, ANSI-style Comments extend to the end of the block, not just to the end of a line. Instead, use C-style Comments (/* .. */).
13.13 Dynamic SQL Statement Caching Statement caching refers to the feature that provides and manages a cache of statements for each session. In the server, it means that cursors are ready to be used without the statement being parsed again. Statement caching can be enabled in the precompiler applications, which will help in the performance improvement of all applications that rely on the dynamic SQL statements. Performance improvement is achieved by removing the overhead of parsing the dynamic statements on reuse. 
Execute Immediate SqlYou can obtain this performance improvement by using a new command line option, stmt_cache (for the statement cache size), which will enable the statement caching of the dynamic statements. By enabling the new option, the statement cache will be created at session creation time. The caching is only applicable for the dynamic statements and the cursor cache for the static statements co-exists with this feature. 
The command line option stmt_cache can be given any value in the range of 0 to 65535. Statement caching is disabled by default (value 0). The stmt_cache option can be set to hold the anticipated number of distinct dynamic SQL statements in the application. 
Example 13-1 Using the stmt_cache Option
Execute Immediate Drop Table Error CalculatorThis example demonstrates the use of the stmt_cache option. In this program, you insert rows into a table and select the inserted rows by using the cursor in the loop. When the stmt_cache option is used to precompile this program, the performance increases compared to a normal precompilation. 