The maximum dimension of a host array is 32,767 elements. If you use a host array that exceeds the maximum, you get a "parameter out of range" run-time error. If you use multiple host arrays in a single SQL statement, their dimensions 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 dimension for the SQL operation.

You cannot declare host arrays of pointers. Also, 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:

EXEC SQL BEGIN DECLARE SECTION;
 hi_lo_scores (25, 25) INTEGER; -- not allowed
EXEC SQL END DECLARE SECTION;

If you do not know the maximum number of rows a select will return, you can declare and open a cursor_name 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:

EXEC SQL BEGIN DECLARE SECTION;
 emp_number (20) INTEGER;
 salary (20) REAL;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE emp_cursor CURSOR FOR
 SELECT EMPNO, SAL FROM EMP;
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND DO ...
LOOP
 EXEC SQL FETCH emp_cursor INTO :emp_number, :salary;
 -- process batch of rows
ENDLOOP;

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

• The end of the active set is reached. The "no data found" Oracle warning code is returned to SQLCODE in the SQLCA. For example, this happens if you fetch into an array of dimension 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 dimension 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(3) 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(3) is 20
EXEC SQL FETCH cursor2 INTO :array_of_30;
 -- now running total in SQLERRD(3) is 30, not 50
EXEC SQL FETCH cursor1 INTO :array_of_20;
 -- now running total in SQLERRD(3) is 40 (20 + 20)
EXEC SQL FETCH cursor2 INTO :array_of_30;
 -- now running total in SQLERRD(3) is 60 (30 + 30)

If you select or fetch a null into a host array that lacks an indicator array, Oracle stops processing, sets SQLERRD(3) to the number of rows processed, and issues the following error message:

ORA-01405: fetched column value is NULL

To learn how to find nulls and truncated values, refer to "About Using Indicator Variables".

When DBMS=V7, if you select or fetch a truncated column value into a host array that lacks an indicator array, Oracle stops processing, sets SQLERRD(3) to the number of rows processed, and issues the following error message:

ORA-01406: fetched column value was truncated

You can check SQLERRD(3) for the number of rows processed before the truncation occurred. The rows-processed count includes the row that caused the truncation error.

When MODE=ANSI, truncation is not considered an error, so 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.

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. Refer to About Using the FOR Clause.

An example of inserting with host arrays follows:

EXEC SQL BEGIN DECLARE SECTION;
 emp_name (50) CHARACTER(20);
 emp_number (50) INTEGER;
 salary (50) REAL;
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)
 VALUES (:emp_name, :emp_number, :salary);

The cumulative number of rows inserted can be found in SQLERRD(3).

Although functionally equivalent to the following statement, the INSERT statement in the last example is much more efficient because it issues only one call to Oracle:

FOR i = 1 TO array_dimension
 EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)
 VALUES (:emp_name[i], :emp_number[i], :salary[i]);
ENDFOR;

In this imaginary example (imaginary because host variables cannot be subscripted in a SQL statement), you use a FOR loop to access all array elements in sequential order.

You can also use host arrays as input variables in an UPDATE statement, as the following example shows:

EXEC SQL BEGIN DECLARE SECTION;
 emp_number (50) INTEGER;
 salary (50) REAL;
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
EXEC SQL UPDATE EMP SET SAL = :salary WHERE EMPNO = :emp_number;

The cumulative number of rows updated can be found in SQLERRD(3). The number does not include rows processed by an update cascade.

If some elements in the arrays are irrelevant, you can use the FOR clause to limit the number of rows updated.

The last example showed a typical update using a unique key (emp_number). Each array element qualified just one row for updating. In the following example, each array element qualifies multiple rows:

EXEC SQL BEGIN DECLARE SECTION;
 job_title (10) CHARACTER(10);
 commission (50) REAL;
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
EXEC SQL UPDATE EMP SET COMM = :commission WHERE JOB = :job_title;

Restrictions: Mixing simple host variables with host arrays in the SET or WHERE clause of an UPDATE statement is not allowed. If any of the host variables is an array, all must be arrays. Furthermore, if you use a host array in the SET clause, you must use one in the WHERE clause. However, their dimensions and datatypes need not match.

You cannot use host arrays with the CURRENT OF clause in an UPDATE statement. For an alternative, refer to About Mimicking the CURRENT OF Clause.

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

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. An example of deleting with host arrays follows:

EXEC SQL BEGIN DECLARE SECTION;
 ...
 emp_number (50) INTEGER;
 EXEC SQL END DECLARE SECTION;
-- populate the host array
EXEC SQL DELETE FROM EMP WHERE EMPNO = :emp_number;

The cumulative number of rows deleted can be found in SQLERRD(3). That number does not include rows processed by a delete cascade.

The last example showed a typical delete using a unique key (emp_number). Each array element qualified just one row for deletion. In the following example, each array element qualifies multiple rows:

EXEC SQL BEGIN DECLARE SECTION;
 ...
 job_title (10) CHARACTER(10);
EXEC SQL END DECLARE SECTION;
-- populate the host array
EXEC SQL DELETE FROM EMP WHERE JOB = :job_title;

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. Also, you cannot use host arrays with the CURRENT OF clause in a DELETE statement. For an alternative, refer to "About Mimicking the CURRENT OF Clause".

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

If you use the FOR clause in a SELECT statement, you get the following error message:

PCC-E-0056: FOR clause not allowed on SELECT statement at ...

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 ...

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:

EXEC SQL DECLARE emp_cursor CURSOR FOR
 SELECT ENAME, SAL FROM EMP WHERE EMPNO = :emp_number;
...
EXEC SQL OPEN emp_cursor;
...
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...
EXEC SQL UPDATE EMP SET SAL = :new_salary
 WHERE CURRENT OF emp_cursor;

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;