8 Host Arrays

This chapter looks at using arrays to simplify coding and improve program performance. You learn how to manipulate Oracle data using arrays, how to operate on all the elements of an array with a single SQL statement, and how to limit the number of array elements processed. The following issues are addressed:

Why Use Arrays?

Arrays reduce programming time and result in improved performance.

With arrays, you manipulate an entire array with a single SQL statement. Thus, Oracle communication overhead is reduced markedly, especially in a networked environment. A major portion of runtime is spent on network roundtrips between the client program and the server database. Arrays reduce the roundtrips.

For example, suppose you want to insert information about 300 employees into the EMP table. Without arrays your program must do 300 individual INSERTs--one for each employee. With arrays, only one INSERT needs to be done.

Declaring Host Arrays

The following example declares three host arrays, each with a maximum of 50 elements:

char emp_name[50][10];
int emp_number[50];
float salary[50];

Arrays of VARCHARs are also allowed. The following declaration is a valid host language declaration:

VARCHAR v_array[10][30];

Restrictions

You cannot declare host arrays of pointers, except for object types.

Except for character arrays (strings), host arrays that might be referenced in a SQL statement are limited to one dimension. So, the two-dimensional array declared in the following example is invalid:

int hi_lo_scores[25][25]; /* not allowed */

Maximum Size of Arrays

The maximum number of bytes accessable by an array in one fetch is dependent on resources used. If you declare an array that exceeds the maximum, you get a "parameter out of range" runtime error.

Using Arrays in SQL Statements

You can use host arrays as input variables in the INSERT, UPDATE, and DELETE statements and as output variables in the INTO clause of SELECT and FETCH statements.

The embedded SQL syntax used for host arrays and simple host variables is nearly the same. One difference is the optional FOR clause, which lets you control array processing. Also, there are restrictions on mixing host arrays and simple host variables in a SQL statement.

The following sections illustrate the use of host arrays in data manipulation statements.

Referencing Host Arrays

If you use multiple host arrays in a single SQL statement, their number of elements should be the same. Otherwise, an "array size mismatch" warning message is issued at precompile time. If you ignore this warning, the precompiler uses the smallest number of elements for the SQL operation.

However, if the array elements that you need to process are contiguous, you should not process host arrays in a loop. Simply use the unsubscripted array names in your SQL statement. Oracle treats a SQL statement containing host arrays of element number n like the same statement executed n times with n different scalar variables.

Using Indicator Arrays

You can use indicator arrays to assign NULLs to input host arrays, and to detect NULL or truncated values (character columns only) in output host arrays. The following example shows how to INSERT with indicator arrays:

Oracle Restrictions

Mixing scalar host variables with host arrays in the VALUES, SET, INTO, or WHERE clause is not allowed. If any of the host variables is an array, all must be arrays.

You cannot use host arrays with the CURRENT OF clause in an UPDATE or DELETE statement.

ANSI Restriction and Requirements

The array interface is an Oracle extension to the ANSI/ISO embedded SQL standard. However, when you precompile with MODE=ANSI, array SELECTs and FETCHes are still allowed. The use of arrays can be flagged using the FIPS flagger precompiler option, if desired.

When doing array SELECTs and FETCHes, always use indicator arrays. That way, you can test for NULLs in the associated output host array.

When you precompile with the precompiler option DBMS=V7 or V8, if a NULL is selected or fetched into a host variable that has no associated indicator variable, Oracle stops processing, sets sqlca.sqlerrd[2] to the number of rows processed, and returns an error.

When DBMS=V7 or V8, Oracle does not consider truncation to be an error.

Selecting into Arrays

You can use host arrays as output variables in the SELECT statement. If you know the maximum number of rows the SELECT will return, simply declare the host arrays with that number of elements. In the following example, you select directly into three host arrays. Knowing the SELECT will return no more than 50 rows, you declare the arrays with 50 elements:

In this example, the SELECT statement returns up to 50 rows. If there are fewer than 50 eligible rows or you want to retrieve only 50 rows, this method will suffice. However, if there are more than 50 eligible rows, you cannot retrieve all of them this way. If you re-execute the SELECT statement, it just returns the first 50 rows again, even if more are eligible. You must either declare a larger array or declare a cursor for use with the FETCH statement.

If a SELECT INTO statement returns more rows than the number of elements you declared, Oracle issues an error message unless you specify SELECT_ERROR=NO. For more information about the SELECT_ERROR option, see the section "Using the Precompiler Options".

Cursor Fetches

If you do not know the maximum number of rows a SELECT will return, you can declare and open a cursor, then fetch from it in "batches."

Batch fetches within a loop let you retrieve a large number of rows with ease. Each FETCH returns the next batch of rows from the current active set. In the following example, you fetch in 20-row batches:

Do not forget to check how many rows were actually returned in the last fetch, and process them. See "Number of Rows Fetched".

Using sqlca.sqlerrd[2]

For INSERT, UPDATE, DELETE, and SELECT INTO statements, sqlca.sqlerrd[2] records the number of rows processed. For FETCH statements, it records the cumulative sum of rows processed.

When using host arrays with FETCH, to find the number of rows returned by the most recent iteration, subtract the current value of sqlca.sqlerrd[2] from its previous value (stored in another variable). In the following example, you determine the number of rows returned by the most recent fetch:

sqlca.sqlerrd[2] is also useful when an error occurs during an array operation. Processing stops at the row that caused the error, so sqlerrd[2] gives the number of rows processed successfully.

Number of Rows Fetched

Each FETCH returns, at most, the total number of rows in the array. Fewer rows are returned in the following cases:

The end of the active set is reached. The "no data found" Oracle error code is returned to SQLCODE in the SQLCA. For example, this happens if you fetch into an array of number of elements 100 but only 20 rows are returned.

Fewer than a full batch of rows remain to be fetched. For example, this happens if you fetch 70 rows into an array of number of elements 20 because after the third FETCH, only 10 rows remain to be fetched.

An error is detected while processing a row. The FETCH fails and the applicable Oracle error code is returned to SQLCODE.

The cumulative number of rows returned can be found in the third element of sqlerrd in the SQLCA, called sqlerrd[2] in this guide. This applies to each open cursor. In the following example, notice how the status of each cursor is maintained separately:

EXEC SQL OPEN cursor1;
EXEC SQL OPEN cursor2;
EXEC SQL FETCH cursor1 INTO :array_of_20;
/* now running total in sqlerrd[2] is 20 */
EXEC SQL FETCH cursor2 INTO :array_of_30;
/* now running total in sqlerrd[2] is 30, not 50 */
EXEC SQL FETCH cursor1 INTO :array_of_20;
/* now running total in sqlerrd[2] is 40 (20 + 20) */
EXEC SQL FETCH cursor2 INTO :array_of_30;
/* now running total in sqlerrd[2] is 60 (30 + 30) */

Sample Program 3: Host Arrays

The demonstration program in this section shows how you can use host arrays when writing a query in Pro*C/C++. Pay particular attention to the use of the "rows processed count" in the SQLCA (sqlca.sqlerrd[2]). See Chapter 9, "Handling Runtime Errors" for more information about the SQLCA. This program is available on-line in the file sample3.pc in your demo directory.

Restrictions

Using host arrays in the WHERE clause of a SELECT statement is not allowed except in a subquery. For an example, see the section "Using the WHERE Clause".

Also, you cannot mix simple host variables with host arrays in the INTO clause of a SELECT or FETCH statement. If any of the host variables is an array, all must be arrays.

Table 8-1 shows which uses of host arrays are valid in a SELECT INTO statement:

Table 8-1 Valid Host Arrays for SELECT INTO

INTO Clause

WHERE Clause

Valid?

array

array

no

scalar

scalar

yes

array

scalar

yes

scalar

array

no

Fetching NULLs

When doing array SELECTs and FETCHes, always use indicator arrays. That way, you can test for NULLs in the associated output host array.

When DBMS = V7, if you SELECT or FETCH a NULL column value into a host array not associated with an indicator array, Oracle stops processing, sets sqlerrd[2] to the number of rows processed, and issues an error message:

Fetching Truncated Values

When DBMS=V7, truncation results in a warning message, but Oracle continues processing.

Again, when doing array SELECTs and FETCHes, always use indicator arrays. That way, if Oracle assigns one or more truncated column values to an output host array, you can find the original lengths of the column values in the associated indicator array.

Inserting with Arrays

You can use host arrays as input variables in an INSERT statement. Just make sure your program populates the arrays with data before executing the INSERT statement.

If some elements in the arrays are irrelevant, you can use the FOR clause to control the number of rows inserted. See the section "Using the FOR Clause".

Restrictions

Mixing simple host variables with host arrays in the SET or WHERE clause of an UPDATE statement is not recommended. If any of the host variables is an array, all should be arrays. Furthermore, if you use a host array in the SET clause, use one of equal number of elements in the WHERE clause.

You cannot use host arrays with the CURRENT OF clause in an UPDATE statement. For an alternative, see the section "Mimicking CURRENT OF".

Table 8-2 shows which uses of host arrays are valid in an UPDATE statement:

Table 8-2 Host Arrays Valid in an UPDATE

SET Clause

WHERE Clause

Valid?

array

array

yes

scalar

scalar

yes

array

scalar

no

scalar

array

no

Deleting with Arrays

You can also use host arrays as input variables in a DELETE statement. It is like executing the DELETE statement repeatedly using successive elements of the host array in the WHERE clause. Thus, each execution might delete zero, one, or more rows from the table.

Restrictions

Mixing simple host variables with host arrays in the WHERE clause of a DELETE statement is not allowed. If any of the host variables is an array, all must be arrays.

You cannot use host arrays with the CURRENT OF clause in a DELETE statement. For an alternative, see the section "Mimicking CURRENT OF".

Using the FOR Clause

You can use the optional embedded SQL FOR clause to set the number of array elements processed by any of the following SQL statements:

DELETE

EXECUTE

FETCH

INSERT

OPEN

UPDATE

The FOR clause is especially useful in UPDATE, INSERT, and DELETE statements. With these statements you might not want to use the entire array. The FOR clause lets you limit the elements used to just the number you need, as the following example shows:

The FOR clause can use an integer host variable to count array elements, or an integer literal. A complex C expression that resolves to an integer cannot be used. For example, the following statement that uses an integer expression is illegal:

The FOR clause variable specifies the number of array elements to be processed. Make sure the number does not exceed the smallest array dimension. Internally, the value is treated as an unsigned quantity. An attempt to pass a negative value through the use of a signed host variable will result in unpredictable behavior.

Restrictions

Two restrictions keep FOR clause semantics clear: you cannot use the FOR clause in a SELECT statement or with the CURRENT OF clause.

In a SELECT Statement

If you use the FOR clause in a SELECT statement, you get an error message.

The FOR clause is not allowed in SELECT statements because its meaning is unclear. Does it mean "execute this SELECT statement n times"? Or, does it mean "execute this SELECT statement once, but return n rows"? The problem in the former case is that each execution might return multiple rows. In the latter case, it is better to declare a cursor and use the FOR clause in a FETCH statement, as follows:

EXEC SQL FOR :limit FETCH emp_cursor INTO ...

With the CURRENT OF Clause

You can use the CURRENT OF clause in an UPDATE or DELETE statement to refer to the latest row returned by a FETCH statement, as the following example shows:

However, you cannot use the FOR clause with the CURRENT OF clause. The following statements are invalid because the only logical value of limit is 1 (you can only update or delete the current row once):

EXEC SQL FOR :limit UPDATE emp SET sal = :new_salary
WHERE CURRENT OF emp_cursor;
...
EXEC SQL FOR :limit DELETE FROM emp
WHERE CURRENT OF emp_cursor;

Using the WHERE Clause

Oracle treats a SQL statement containing host arrays of number of elements n like the same SQL statement executed n times with n different scalar variables (the individual array elements). The precompiler issues an error message only when such treatment would be ambiguous.

because there is a mgr_number in the SET clause for each row matching job_title in the WHERE clause, even if each job_title matches multiple rows. All rows matching each job_title can be SET to the same mgr_number. Therefore, no error message is issued.

Arrays of Structs

Using arrays of scalars, users can perform multi-row operations involving a single column only. Using structs of scalars allows users to perform single row operations involving multiple columns.

In order to perform multi-row operations involving multiple columns, however, users previously needed to allocate several parallel arrays of scalars either separately or encapsulated within a single struct. In many cases, it is easier to reorganize this data structure more conveniently as a single array of structs instead.

Pro*C/C++ supports the use of arrays of structs which enable an application programmer to perform multi-row, multi-column operations using an array of C structs. With this enhancement, Pro*C/C++ can handle simple arrays of structs of scalars as bind variables in embedded SQL statements for easier processing of user data. This makes programming more intuitive, and allows users greater flexibility in organizing their data.

In addition to supporting arrays of structs as bind variables, Pro*C/C++ also supports arrays of indicator structs when used in conjunction with an array of structs declaration.

Note: Binding structs to PL/SQL records and binding arrays of structs to PL/SQL tables of records are not part of this new functionality. Arrays of structs may also not be used within an embedded PL/SQL block. See the section "Restrictions on Arrays of Structs" for further restrictions.

Since arrays of structs are intended to be used when performing multi-row operations involving multiple columns, it is generally anticipated that they will be used in the following ways.

As output bind variables in SELECT statements or FETCH statements.

As input bind variables in the VALUES clause of an INSERT statement.

Using Arrays of Structs

The notion of an array of structs is not new to C programmers. It does, however, present a conceptual difference for data storage when it is compared to a struct of parallel arrays.

In a struct of parallel arrays, the data for the individual columns is stored contiguously. In an array of structs, on the other hand, the column data is interleaved, whereby each occurrence of a column in the array is separated by the space required by the other columns in the struct. This space is known as a 'stride'.

Restrictions on Arrays of Structs

The following restrictions apply to the use of arrays of structs in Pro*C/C++:

Arrays of structs (just as with ordinary structs) are not permitted inside an embedded PL/SQL block.

Use of arrays of structs in WHERE or FROM clauses is prohibited.

Arrays of structs are not permitted with Oracle Dynamic SQL Method 4. They are permitted with ANSI Dynamic SQL. See the chapter Chapter 14, "ANSI Dynamic SQL".

Arrays of structs are not permitted in the SET clause of an UPDATE statement.

The syntax for declaring an array of structs doesn't really change. There are, however a few things to keep in mind when using an array of structs.

Declaring an Array of Structs

When declaring an array of structs which will be used in a Pro*C/C++ application, the programmer must keep in mind the following important points:

The struct must have a structure tag. For example, in the following code segment

struct person {
char name[15];
int VARCHARage;
} people[10];

the person variable is the structure tag. This is so the precompiler can use the name of the struct to compute the size of the stride.

The members of the struct must not be arrays. The only exception to this rule is for character types such as char or VARCHAR since array syntax is used when declaring variables of these types.

char and VARCHAR members may not be two-dimensional.

Nested structs are not permitted as members of an array of structs. This is not a new restriction, since nested structs have not been supported by previous releases of Pro*C/C++.

The size of just the struct may not exceed the maximum value that a signed 4-byte quantity may represent. This is typically two gigabytes.

Given these restrictions regarding the use of arrays of structs, the following declaration is legal in Pro*C/C++

It is also important to note that you may not apply datatype equivalencing to either the array of structs itself or to any of the individual fields within the struct. For example, assuming empno is not declared as an array in the above illegal struct, the following is illegal:

exec sql var bad[3].empno is integer(4);

The precompiler has no way to keep track of individual structure elements within the array of structs. One could do the following, on the other hand, to achieve the desired effect.

This should come as no surprise since equivalencing individual array items has not been supported by previous releases of Pro*C/C++. For example, the following scalar array declarations illustrate what is legal and what is not.

Using Indicator Variables

Indicator variables for an array of structs declaration work in much the same way as a normal struct declaration. An indicator array of structs declaration must abide by the rules for an array of structs described in the section "Declaring an Array of Structs", plus the following rules.

The number of fields in the indicator struct must be less than or equal to the number of fields in the corresponding array of structs.

The order of the fields must match the order of the corresponding members of the array of structs.

The datatype for all elements in the indicator struct must be short.

The size of the indicator array must be at least the same size as the host variable declaration. It may be larger, but it may not be smaller.

Note that these rules generally reflect the rules for using structs as implemented in prior releases of Pro*C/C++. The array restriction is also the same as that previously used for arrays of scalars.

Declaring a Pointer to an Array of Structs

In some cases, it may be desirable to declare a pointer to an array of structs. This allows pointers to arrays of structs to be passed to other functions or used directly in an embedded SQL statement.

Note: The length of the array referenced by a pointer to an array of structs cannot be known during precompilation. For this reason, an explicit FOR clause must be used when a bind variable whose type is a pointer to an array of structs is used in any embedded SQL statement.

Remember that FOR clauses may not be used in an embedded SQL SELECT statement. Therefore, to retrieve data into a pointer to an array of structs, an explicit cursor and FETCH statement must be used with the FOR clause.

Examples

The following examples demonstrate different uses of the array of structs functionality in Pro*C/C++.

Data is then be selected using the same query except for the addition of the indicator variable:

exec sql select * into :my_dept indicator :my_dept_ind from dept;

Similarly, the indicator could be used when inserting the data as well:

exec sql insert into dept values (:my_dept indicator :my_dept_ind);

Example 2, Using mixed scalar arrays with an array of structs

As in prior releases of Pro*C/C++, when using multiple arrays for bulk handling of user data, the size of the arrays must be the same. If they are not, the smallest array size is chosen leaving the remaining portions of the arrays unaffected.

Using the FOR clause

Alternatively, we could have used the FOR clause to instruct the fetch on how many rows to retrieve. Recall that the FOR clause is prohibited when using the SELECT statement, but not the INSERT or FETCH statements.

We add the following to our original declarations

int limit = 10;

and code our example accordingly.

exec sql for :limit
fetch c into :emp, :wage indicator :wage_ind;

Example 4, Individual array and struct member referencing

Prior releases of Pro*C/C++ allowed array references to single structures in an array of structs. The following is therefore legal since the bind expression resolves to a simple struct of scalars.

exec sql select * into :dept[3] from emp;

Users can reference an individual scalar member of a specific struct in an array of structs as the following example shows.

exec sql select dname into :dept[3].dname from dept where ...;

Naturally, this requires that the query be a single row query so only one row is selected into the variable represented by this bind expression.

Example 5, Using indicator variables, a special case

Prior releases of Pro*C/C++ required that an indicator struct have the same number of fields as its associated bind struct. This restriction has been relaxed when using structs in general. By following the above guidelines for indicator arrays of structs it is possible to construct the following example.

Or we can simply use such pointers to arrays of structs directly in some embedded SQL statement.

exec sql for :n insert into dept values (:dptr);

The most important thing to remember is the use of the FOR clause.

Mimicking CURRENT OF

You use the CURRENT OF cursor clause in a DELETE or UPDATE statement to refer to the latest row FETCHed from the cursor. (For more information, see "Using the CURRENT OF Clause".) However, you cannot use CURRENT OF with host arrays. Instead, select the ROWID of each row, then use that value to identify the current row during the update or delete. An example follows:

However, the fetched rows are not locked because no FOR UPDATE OF clause is used. (You cannot use FOR UPDATE OF without CURRENT OF.) So, you might get inconsistent results if another user changes a row after you read it but before you delete it.