1055-1058

Previous Table of Contents Next

Page 1055

Figure 45.1.
This logic flowchart
will help you decide
what method is
appropriate for your
statement.

After deciding what method is appropriate for your situation, avoid some of the common errors described in the following list:

• When using a character array to store the dynamic SQL, blank-pad the array before starting.
• Do not null-terminate the host string. Oracle does not recognize the null terminator as an end-of-string; instead, it treats it as part of the SQL statement.
• If you use a VARCHAR variable to store the dynamic SQL statement, make sure the length of the VARCHAR is set correctly before you execute the PREPARE or EXECUTE IMMEDIATE command.

Coding Methods for Dynamic SQL Statements

Now take a look at the four different methods that can be used to code dynamic SQL statements.

Method One

Method one lets you build a dynamic SQL statement and immediately execute it using the EXECUTE IMMEDIATE command. This method results in success or failure and uses no host variables . Statements in method one are parsed every time they are executed. These statements do not allow SELECTs.

Page 1056

Method Two

Method two is identical to method one except that you can use host variables, which need to be known at precompile time. With method two, the SQL is parsed just once but can be executed many times. This method lets you use the USING clause. Every placeholder in the prepared dynamic SQL statement must match a corresponding host variable in the USING clause.

TIP

If one host variable is an array, all the variables must be arrays.

Method Three

Method three allows your program to accept or build a dynamic query and then process it using PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. At precompile time, the following must be known: select-list items, number of placeholders, and data types of host variables. Method three is used for dynamic SQL with a known select list, giving the programmer more flexibility to build SQL statements on-the-fly .

Method Four

Method four is probably the most complex of the four methods, but it is very diverse in its use. It allows the program to accept or build a dynamic SQL statement and then process it using descriptors. At precompile time, the following are unknown: select-list items, number of placeholders, and datatypes of host variables. To process this type of dynamic query, you must be able to use the DESCRIBE SELECT LIST command and be able to declare a data structure called the SQL descriptor area (SQLDA). Descriptors are a segment of memory used by the computer and by Oracle to hold a complete description of the variables in a dynamic SQL statement. Descriptor variables are defined as a data structure containing the following information:

• Maximum number of columns that can be evaluated
• Actual number of columns in the SELECT list
• Array of pointers to column names
• Array of maximum lengths of columns
• Array of actual column lengths
• Array of datatypes for each column
• Array of pointers to data values
• Array of pointers to indicator variables

Page 1057

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. If you have more than one method for SQL statements, each statement requires its own SQLDAs; nonconcurrent cursors , however, can reuse SQLDAs. There is no set limit on the number of SQLDAs in a program.

The DESCRIBE command is useful in determining what the SQL statement contains. DESCRIBE instructs Oracle to provide the host variables for any select statement. It examines the select statement to determine the number and type of columns in the select list. Oracle must define a storage area to hold fetched rows from the database, and actual data returned from the select is stored in descriptor variables.

The following steps outline the coding for method four:

1. Define a descriptor variable:

    SQLDA *descr_var 

2. Place a SQL SELECT statement into a host variable:

    scanf("%[^\n",sql_statement); 

3. Prepare the SQL statement:

    EXEC SQL PREPARE S1 FROM sql_statement; 

4. Declare a cursor area for the SELECT statement:

    EXECSQL DECLARE C1 CURSOR FOR S1 

5. Execute the query and create an active set:

    EXEC SQL OPEN C1 USING DESCRIPTOR descr_var; 

6. DESCRIBE the SELECT into the descriptor variable:

    EXEC SQL DESCRIBE SELECT LIST FOR S1 INTO desc_var 

7. FETCH rows from active set:

    EXEC SQL FETCH C1 USING DESCRIPTOR descr_var; 

8. CLOSE the cursor:

    EXEC SQL CLOSE C1; 

User Exits

A user exit is a host-language subroutine that is called by SQL*Forms to do special processing. Sometimes SQL*Forms triggers are unable to perform complex calculations or field validations. Embedded SQL commands and PL/SQL blocks are allowed in user exits. User exits are invoked in the form but execute outside the form. Usually, user exits are faster than SQL commands, but the down side is that they are more difficult to debug and more complicated to write. Some of the common uses of user exits include controlling real-time devices or processes,

Page 1058

performing data manipulations that need extended procedural capabilities, or performing special file I/O operations. The following list contains some general rules and guidelines to use when writing user exits:

• User exits are written in a third-generation language with embedded SQL commands. This code is precompiled to translate the SQL commands into host-language statements.
• Third-generation language statements (host-language statements) are written as an ordinary host-language program, using uppercase and lowercase as usual.
• SQL commands are customarily written entirely in uppercase.
• User-exit variables must be written according to the rules of the host language.
• Connecting to the database via EXEC SQL CONNECT is not necessary as in the Pro*USE. Connection is made through SQL*Forms.
• Any user-exit variables that receive or write values from and to SQL*Forms or data tables must be declared in the DECLARE section of the user exit.
• The name of the user exit should not be a reserved word in Oracle. Also, avoid names that conflict with SQL*Forms commands, function codes, and externally defined names. SQL*Forms converts the name of the user exit to uppercase before searching for the exit; therefore, the EXIT command must be in uppercase.
• Sometimes SQL*Forms I/O calls interfere with printer I/O routines. If they do, the user exit cannot use the printer. This restriction does not apply to user exits written in C.
• A user exit should not UPDATE a database table that is associated with a form; when the form COMMITs the data, it could overwrite what the user exit saved.

It is possible to pass values to a user exit and receive a value in return. When a SQL*Forms trigger calls a user exit, it passes the following information:

• A character string that contains the exit name and specified parameters
• The trigger step failure message, if one is defined
• A flag indicating whether the user exit was called in normal or query mode

Global values can also be passed to a user exit using IAF GETS.

The returning values from a user exit indicate whether it succeeded or failed. The return code is an integer constant, which is defined in SQL*Forms. The three possible return values are outlined in Table 45.6.

Previous Table of Contents Next