Programmer's Guide to the Pro*Ada Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
You can use host arrays as input and output host variables. However, host arrays of access types, such as STRING_DEF, are not allowed.
Arrays can ease programming and offer improved performance. When writing an application, you are usually faced with the problem of storing and manipulating large collections of data. Arrays simplify the task of naming and referencing the individual items in each collection.
Using arrays can increase the performance of your application. Arrays let you manipulate an entire collection of data items with a single SQL statement. Oracle communication overhead is reduced markedly, especially in a networked environment.
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.
type integer_array is array (integer range <>) of integer;
type float_array is array (integer range <>) of float;
type long_float_array is array (integer range <>) of long_float;
An indicator array may be associated with any host variable array. The indicator array should be declared with at least the same number of elements as the host variable array. Arrays of indicator variables must be declared using the type INDICATOR_ARRAY, which is predefined as follows:
type INDICATOR_ARRAY is
array (integer range<>) of INDICATOR;
The usual way to bind a user-defined array is to instantiate the generic package ARRAY_BINDS as shown below:
type HOST_ARRAY_TYPE is array (integer range <>)
of HOST_VARIABLE_TYPE;
package MY_ARRAY is new array_binds (
HOST_VARIABLE_TYPE,
HOST_ARRAY_TYPE,
ORACLE_TYPES.ORACLE_TYPE);
use MY_ARRAY;
Both HOST_VARIABLE_TYPE and HOST_ARRAY_TYPE must be defined types, and HOST_VARIABLE_TYPE must be a type for which binds already exist. In the absence of user-defined non-array types, HOST_VARIABLE_TYPEs must be one of the predefined types.
Note: Composite types, such as VARCHAR or VARRAW, cannot be used in host variable arrays. However, you can use arrays of strings as host arrays as shown in the section "An Array Bind Example" . Arrays of STRING_DEFs (string access types) are not allowed.
The embedded SQL syntax for host variable array usage is generally identical to that used for non-array host variables. One difference is that the user can control the number of rows processed through use of the optional FOR clause. Certain restrictions also exist with regard to mixing array and non-array host variables.
subtype NAME_STR is string(1..20);
type NAME_ARRAY is array(integer range<>) of NAME_STR;
package NAME_BINDS is new ARRAY_BINDS(
NAME_STR,
NAME_ARRAY,
ORACLE_TYPES.ORACLE_TYPE);
use NAME_BINDS;
EMP_NO : ORACLE.INTEGER_ARRAY(1..100); --predef'd array type
EMP_NAME : NAME_ARRAY(1..100); --user defined array type
COMM : ORACLE.FLOAT_ARRAY(1..100); --predefined array type
COMM_IND : INDICATOR_ARRAY(1..100); --predefined ind. array
EXEC SQL INSERT INTO emp (ename, empno, comm)
VALUES (:EMP_NAME, :EMP_NO, :COMM :COMM_IND);
with oracle_types, array_binds;
...
subtype MY_STRING is string(1..10);
type NAME_ARRAY is array(integer range <>) of MY_STRING;
package NAME_BINDS is new ARRAY_BINDS (
MY_STRING,
ORACLE.INDICATOR,
EMP_ARRAY,
ORACLE.INDICATOR_ARRAY,
ORACLE_TYPES.ORACLE_TYPE);
use NAME_BINDS;
EMP_NUMBER : ORACLE.INTEGER_ARRAY(1..50);
EMP_NAME : NAME_ARRAY(1..50);
SALARY : ORACLE.FLOAT_ARRAY(1..50);
EXEC SQL SELECT ename, empno, sal
INTO :EMP_NAME, :EMP_NUMBER, :SALARY
FROM emp
WHERE sal > 1000;
In this example, the SELECT statement returns up to 50 rows. If there are fewer than 50 eligible rows, or you only want to retrieve 50 rows, this way will do.
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 there are more rows in the query result set. You must either dimension 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 dimensioned, Oracle issues the error message
SQL-02112: PCC: SELECT ...INTO returns too many rows
unless you specify the SELECT_ERROR=NO option. For more information about this option, see Chapter 11, "Running the Pro*Ada Precompiler."
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:
EMP_NUMBER : ORACLE.INTEGER_ARRAY(1..20);
SALARY : ORACLE.FLOAT_ARRAY(1..20);
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, sal FROM emp;
EXEC SQL OPEN emp_cursor;
-- establish a local block, with an exception to
-- handle the "no data found" condition
begin
EXEC SQL WHENEVER NOT FOUND raise NO_MORE_DATA;
FETCH_LOOP:
loop -- fetch the data, 20 rows at a time
EXEC SQL FETCH emp_cursor
INTO :EMP_NUMBER, :SALARY;
for I in 1..20 loop
-- process batches of 20 rows
...
end loop;
end loop FETCH_LOOP;
-- the exception NO_MORE_DATA is raised when there is
-- no more data to FETCH
exception
when NO_MORE_DATA =>
PUT("No more data to fetch. N of rows fetched was ");
PUT(ORACLE.SQLROWS);
NEW_LINE;
-- turn off the error handling
EXEC SQL WHENEVER NOT FOUND CONTINUE;
end;
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)
Table 7 - 1 shows which uses of host arrays are valid in a SELECT INTO statement:
INTO Clause | WHERE Clause | Valid |
array | array | NO |
scalar | scalar | YES |
array | scalar | YES |
scalar | array | NO |
If some elements in the arrays are irrelevant, you can use the FOR clause to control the number of rows INSERTed. See "Using the FOR Clause" .
An example of INSERTing with host arrays follows:
...
EMP_NAME : EMP_ARRAY(1..50);
EMP_NUMBER : ORACLE.INTEGER_ARRAY(1..50);
SALARY : ORACLE.FLOAT_ARRAY(1..50);
-- populate the host arrays, then
EXEC SQL INSERT INTO emp (ename, empno, sal)
VALUES (:EMP_NAME, :EMP_NUMBER, :SALARY);
The cumulative number of rows INSERTed can be found in SQLCA.SQLERRD(3), or is returned by the function ORACLE.SQLROWS.
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 in 1..ARRAY_DIMENSION loop
EXEC SQL INSERT INTO emp (ename, empno, sal)
VALUES (:EMP_NAME(I), :EMP_NUMBER(I), :SALARY(I));
end loop;
In this imaginary example (imaginary because host variables cannot be subscripted in a SQL statement), you try to use a for loop to access all array elements in sequential order.
Note: You cannot use an array of access types in the VALUES clause of an INSERT statement. All array elements must be data items, not pointers.
Mixing simple host variables with host arrays in the VALUES clause of an INSERT statement is not allowed. If any of the host variables is an array, all must be arrays.
EMP_NUMBER : ORACLE.INTEGER_ARRAY(1..1000);
SALARY : ORACLE.FLOAT_ARRAY(1..1000);
-- populate the host arrays, then
EXEC SQL UPDATE emp SET sal = :SALARY
WHERE empno = :EMP_NUMBER;
The cumulative number of rows UPDATEd can be found in SQLCA.SQLERRD(3), or is returned by the ORACLE.SQLROWS function.
If some elements in the arrays are irrelevant, you can use the SQL FOR clause to limit the number of rows updated.
The last example showed a typical UPDATE using a unique key (empno). Each array element qualified just one row for updating. In the following example, each array element qualifies multiple rows:
JOB_TITLE : NAME_ARRAY(1..50);
COMMISSION : ORACLE.FLOAT_ARRAY(1..50);
-- populate the host arrays, then
EXEC SQL UPDATE emp SET comm = :COMMISSION
WHERE job = :JOB_TITLE;
The following table shows which uses of host arrays are valid in an UPDATE statement:
SET Clause | WHERE Clause | Valid? |
array | array | YES |
scalar | scalar | YES |
array | scalar | NO |
scalar | array | NO |
An example of DELETEing with host arrays follows:
...
EMP_NUMBER : ORACLE.INTEGER_ARRAY;
...
-- populate the host array, then
EXEC SQL DELETE FROM emp
WHERE empno = :EMP_NUMBER;
The cumulative number of rows DELETEd is returned by the function ORACLE.SQLROWS.
The last example showed a typical DELETE using a unique key (empno). Each array element qualified just one row for deletion. In the following example, each array element qualifies multiple rows.
...
JOB_TITLE : name_array(1..10);
...
-- populate the host array, then
EXEC SQL DELETE FROM emp
WHERE job = :JOB_TITLE;
You cannot use host arrays with the CURRENT OF clause in a DELETE statement. For an alternative, see "Mimicking CURRENT OF" .
EMP_NAME : name_array(1..50);
EMP_NUMBER : ORACLE.INTEGER_ARRAY(1..50);
COMMISSION : ORACLE.FLOAT_ARRAY(1..50);
IND_COMM : ORACLE.INDICATOR_ARRAY(1..50);
-- populate the host arrays
-- populate the indicator array; to insert a null value
-- into the COMM column, assign -1 to the appropriate
-- element in the indicator array, then
EXEC SQL INSERT INTO emp (ename, empno, comm) VALUES
(:EMP_NAME, :EMP_NUMBER, :COMMISSION INDICATOR :IND_COMM);
EMP_NAME : name_array(1..100);
SALARY : ORACLE.FLOAT_ARRAY(1..100);
ROWS_TO_INSERT : integer;
...
-- populate the host arrays
ROWS_TO_INSERT := 25; -- set FOR-clause variable
...
EXEC SQL FOR :ROWS_TO_INSERT -- will process only 25 rows
INSERT INTO emp (ename, sal)
VALUES (:EMP_NAME, :SALARY);
The FOR clause must use an integer host variable to count array elements. For example, the following FOR clause is invalid:
EXEC SQL FOR 25 -- literal not allowed
INSERT INTO emp (ename, empno, sal)
VALUES (:EMP_NAME, :EMP_NUMBER, :SALARY);
The number of array elements processed is determined by comparing the dimension of the smallest host or indicator array with the FOR-clause variable. The lesser value is used.
If the value of the FOR-clause variable is negative, no rows are processed. In this case, a zero is returned to SQLCODE in the SQLCA indicating that Oracle carried out the operation successfully. However, if the value of the FOR-clause variable is zero, no rows are processed, and Oracle issues the error message
ORA-01009: missing mandatory parameter
because zero is treated like an unspecified value.
PCC-S-0055: Array <name> not allowed as bind variable at ...
For example, assuming the declarations
EMP_NAME : NAME_ARRAY(1..20);
JOB_TITLE : NAME_ARRAY(1..20);
it would be ambiguous if the statement
EXEC SQL SELECT ename INTO :EMP_NAME
FROM emp
WHERE job = :JOB_TITLE;
were treated like the imaginary statement
for I in 1..50 loop
SELECT ename INTO :EMP_NAME(I)
FROM emp
WHERE job = :JOB_TITLE(I);
end loop;
because multiple rows might meet the WHERE-clause search condition, but only one output variable is available to receive data. Therefore, an error message is issued.
However, it would not be ambiguous if the statement
EXEC SQL UPDATE emp SET ename = :EMP_NAME
WHERE job = :JOB_TITLE;
were treated like the imaginary statement
for I in 1..50 loop
UPDATE emp SET ename = :EMP_NAME(I)
WHERE job = :JOB_TITLE(I);
end loop;
because there is an EMP_NAME 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 EMP_NAME. Therefore, no error message is issued.
Likewise, it would not be ambiguous if the statement
EXEC SQL UPDATE emp SET ename = :EMP_NAME WHERE empno IN
(SELECT empno FROM emp WHERE job = :JOB_TITLE);
were treated like the imaginary statement
for I in 1..50 loop
UPDATE emp SET ename = :EMP_NAME(I) WHERE empno IN
(SELECT empno FROM emp WHERE job = :JOB_TITLE(I));
end loop;
because there is a value in the SET clause for each row that meets the WHERE-clause search condition, even if multiple rows do so.
EMP_NAME : NAME_ARRAY(1..25);
JOB_TITLE : NAME_ARRAY(1..25);
ROWS_ID : ORACLE.ROWID(1..25);
...
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, job, rowid FROM emp;
...
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND raise ...
loop
EXEC SQL FETCH emp_cursor
INTO :EMP_NAME, :JOB_TITLE, :ROWS_ID;
...
EXEC SQL DELETE FROM emp
WHERE job = :OLD_TITLE AND rowid = :ROWS_ID;
EXEC SQL COMMIT WORK;
end loop;
However, the FETCHed rows are not locked because no FOR UPDATE OF clause is used. You cannot use FOR UPDATE OF without CURRENT OF.
Note: You might get inconsistent results if another user changes a row after you read it but before you DELETE it. You can lock the table explicitly with EXEC SQL LOCK TABLE to avoid this problem. However, this will lock all rows in the table, not just the ones identified as the active set.
When using host arrays with FETCH, to find the number of rows returned by the most recent iteration, subtract the current value of ORACLE.SQLROWS from its previous value (stored in a different variable). This is demonstrated in the array bind example program .
Note: SQLROWS returns the value in SQLCA.SQLERRD(3). See Chapter 5, "Handling Runtime Errors," for more information about the SQLCA.
-- Copyright (c) 1994 by Oracle Corporation -- ARRAYS : -- 1) Log on to ORACLE -- 2) DECLARE and OPEN a cursor for a SQL statement -- 3) FETCH in batches from the cursor -- 4) Print the data -- 5) Log off with text_io, integer_text_io, float_text_io, oracle_types, array_binds;
procedure ARRAYS is use text_io, integer_text_io, float_text_io; subtype NAME_STRING is STRING(1..10); type NAME_ARRAY is array (INTEGER range <>) of NAME_STRING; package NAME_BINDS is new ARRAY_BINDS ( NAME_STRING, oracle.indicator, NAME_ARRAY, oracle.indicator_array, ORACLE_TYPES.ORACLE_TYPE); use NAME_BINDS; USERNAME : constant STRING := "SCOTT/TIGER"; EMPLOYEE_NUMBER : ORACLE.INTEGER_ARRAY(1..5); EMPLOYEE_NAME : NAME_ARRAY(1..5); SALARY : ORACLE.FLOAT_ARRAY(1..5); NUMBER_OF_ROWS_RETURNED : INTEGER := 0; SQL_ERROR : exception; procedure PRINT_ROWS (N : INTEGER) is begin if N < 1 then PUT_LINE("No rows returned."); return; end if; for I in 1..N loop PUT(EMPLOYEE_NUMBER(I), 10); PUT(" "); PUT(EMPLOYEE_NAME(I)); PUT(SALARY(I),6,2,0); NEW_LINE; end loop; end PRINT_ROWS; begin -- main EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; EXEC SQL CONNECT :USERNAME; PUT("Connected to ORACLE as user: "); PUT_LINE(USERNAME); EXEC SQL DECLARE C1 CURSOR FOR SELECT empno, ename, sal FROM emp; EXEC SQL OPEN C1; NEW_LINE; PUT_LINE(" Employee No. Employee Name Salary"); PUT_LINE("-----------------------------------------"); -- Use the "WHENEVER NOT FOUND" to branch to a label when the -- last row has been retrieved. EXEC SQL WHENEVER NOT FOUND GOTO FETCH_DONE; loop -- start the FETCH LOOP EXEC SQL FETCH C1 INTO :EMPLOYEE_NUMBER, :EMPLOYEE_NAME, :SALARY; -- SQLCA.SQLERRD(3) is the cumulative number of rows -- retrieved. PRINT_ROWS(SQLCA.SQLERRD(3) - NUMBER_OF_ROWS_RETURNED); NUMBER_OF_ROWS_RETURNED := SQLCA.SQLERRD(3); end loop; <<FETCH_DONE>> -- print the remaining rows if (SQLCA.SQLERRD(3) - NUMBER_OF_ROWS_RETURNED > 0) then PRINT_ROWS(SQLCA.SQLERRD(3) - NUMBER_OF_ROWS_RETURNED); end if; NEW_LINE; PUT(" Rows processed: "); PUT(SQLCA.SQLERRD(3)); NEW_LINE; PUT_LINE("Program complete."); -- log off EXEC SQL COMMIT RELEASE; exception when SQL_ERROR => EXEC SQL WHENEVER SQLERROR CONTINUE; PUT_LINE("** ORACLE ERROR OCCURED **"); NEW_LINE; PUT_LINE(ORACLE.ERROR.MESSAGE); EXEC SQL ROLLBACK RELEASE; end ARRAYS;
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |