1051-1054

Previous Table of Contents Next

Page 1051

You can use indicator variables in VALUES, INTO, and SET clauses in executable SQL statements. To reference these variables, you must prefix them with a colon and append them to their associate host variables ; they can not be referenced by themselves . See the following syntax guideline:

 :host_variable:indicator_variable 

TIP

You cannot use indicator variables in WHERE clauses.

Some of the possible values for indicator variables are outlined in the following list:

Return Codes for SELECTs (Output)

	Successful return of value into host variable
_1	Returned value of null
>0	Returned value was truncated

Return Codes for UPDATE or DELETE (Input)

>=0	Value of host variable is used
_1	Null value is used in place of host variable

Host Arrays

A host array is a collection of related data items, called elements, and is associated with a single variable name . Arrays improve performance because large amounts of data can be manipulated at one time with a single Oracle statement. You declare arrays in the DECLARE section just as you do for simple variables, only you must set the size of the array. The maximum dimension of a host array is 32,767 elements.

Some of the restrictions that are imposed include not declaring a two-dimensional array or not declaring an array of pointers.

You can use arrays as input variables in the INSERT, UPDATE, and DELETE statements and as output variables in the INTO clause of SELECT and FETCH statements. When using the arrays as an output variable, if you know the exact number of rows that will be returned, you want to dimension the array to that number. You need not process host arrays in a loop unless you are batching . Simply use the unsubscripted array names in your SQL statement.

Page 1052

If you are unsure of the number of rows that will be returned, you should do batch FETCHes. Batch FETCHes within a loop allow you to retrieve a large amount of data without knowing exactly how many rows you will retrieve. Each FETCH returns the next batch of rows from the current active set. The maximum rows returned depend on the array size. Fewer rows are returned under the following conditions:

• The end of the active set is reached.
• Fewer than a full batch of rows remain to be FETCHed.
• An error is detected while processing a row.

TIP

For INSERT, UPDATE, DELETE, and SELECT INTO statements, SQLERRD(3) records the number of rows processed. For FETCH statements, it records the cumulative sum of rows processed .

Dynamic SQL

Dynamic SQL is nothing more than SQL statements that are not known at compile time. Unlike static SQL statements, dynamic SQL statements are not embedded in your source program. These statements are stored in a character string or built by the program at runtime. Dynamic SQL statements can be built interactively or read from a file. The primary advantage of dynamic SQL is that it is more versatile than plain embedded SQL. One of the disadvantages of dynamic SQL is that it requires added processing time. You should use dynamic SQL when one of the following is unknown:

• Text of the SQL statement
• The number of host variables
• The data types of host variables
• References to database objects such as columns , indexes, sequences, tables, usernames, and views

To represent a dynamic SQL statement, a character string must contain the required text of a valid SQL statement without the EXEC SQL clause, host-language delimiters, statement terminators, or any of the following embedded SQL commands:

CLOSE

DECLARE

DESCRIBE

EXECUTE

FETCH

Page 1053

INCLUDE

OPEN

PREPARE

WHENEVER

TIP

Initialize your character string before you use or reuse it, and do not null-terminate the string.

These character strings are manipulated by Oracle via cursors. Cursors are areas of memory used for processing SQL statements. The scope of the cursors is the entire Pro*C program. There are no limits to the number of cursors a Pro*C program can have, but there is a maximum number on how many cursors can be open at one time. The INIT.ORA file contains a parameter that sets how many open cursors are allowed at one time. The two types of cursors are implicit and explicit. Implicit cursors are automatically declared and can be used for all SQL statements. Explicit cursors are required to manipulate multi-rowed SELECTs. They are also used to keep track of the current set or row. Four Oracle statements are associated with cursors :

• DECLARE: Associates SQL statement with cursor.
• OPEN: Executes SQL statement.
• FETCH: Retrieves next row and store in buffer.
• CLOSE: Releases cursor area back to Oracle.

The DECLARE statement defines the cursor by giving it a name that is associated with the SQL statement. This name is used by the precompiler and should not be defined in the DECLARE section. Cursor names should not be hyphenated and can be any length, but only the first 31 characters are evaluated. Because this statement is a declarative statement, it must precede all other SQL statements and must be declared only once.

TIP

For ANSI compatibility, use cursor names no longer than 18 characters.

The following example shows how to declare a cursor:

 EXEC SQL DECLARE Class_Students CURSOR FOR                     SELECT FNAME, LNAME, GRADE                     FROM STUDENT S, GRADE G                     WHERE S.SSN = G.SSN                     AND G.CCODE = :class_code                     ORDER BY LNAME; 

Page 1054

NOTE

When using explicit cursors, you must omit the INTO clause.

After declaring a cursor, you must open it so that Oracle can parse it, replace variables and parameters, and execute the query. The OPEN command positions the cursor just before the first row that is retrieved from the active set. It also zeroes the row-processed count kept by the third element of the SQLERRD in the SQLCA. Remember that rows are not retrieved at this point. After a cursor is opened, the variables are not reexamined until it is opened again; therefore, if you want a different active set, you must reopen the cursor. The amount of work done by the OPEN command depends on the values of three precompiler options: HOLD_CURSOR, RELEASE_CURSOR, and MAXOPENCURSORS. The following example shows the syntax of the OPEN command:

 EXEC SQL OPEN Class_Students; 

After the cursor is opened, you can retrieve data using the FETCH command. The FETCH command requires that you specify the output host variables that contain the results from the query using the INTO clause. The first time you execute the FETCH command, the cursor moves to the first row in the active set. With each execution of FETCH, the cursor advances one row until it encounters no rows to retrieve. The cursor can only move forward. If you want a previous row, you must reopen the cursor. After the last row is retrieved, SQLCODE in the SQLCA has a value of 1403. Only one FETCH statement can be associated with an open cursor. The following example shows the syntax for the FETCH command:

 EXEC SQL FETCH Class_Students                       INTO  :first, :last, :grade; 

NOTE

The SQLWARN[3] flag is set when the number of columns in the SELECT does not match the number of INTO host variables.

The last statement is the CLOSE command. This command releases all cursor resources back to Oracle. The active set becomes undefined and an invalid cursor error occurs if a FETCH is executed against a closed cursor. A cursor can be closed multiple times, but it is usually closed only when there is no further need for the SQL statement. The following example shows the syntax for the CLOSE command:

 EXEC SQL CLOSE Class_Students; 

There are four methods for programming a dynamic SQL statement. With all four methods , you must store dynamic SQL statements in a character string, which must be a quoted literal or a host variable. Choosing the correct method can be confusing, but Figure 45.1 will help you make this decision.

Previous Table of Contents Next