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 round trips between the client program and the server database. Arrays reduce the round trips.

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.

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];

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.

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:

char   emp_name[50][20]; 
int    emp_number[50]; 
float  salary[50]; 
 
EXEC SQL SELECT ENAME, EMPNO, SAL 
    INTO :emp_name, :emp_number, :salary 
    FROM EMP 
    WHERE SAL > 1000; 

In the preceding 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 reexecute 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.

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.

An example of inserting with host arrays follows:

char   emp_name[50][20]; 
int    emp_number[50]; 
float  salary[50]; 
/* 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 the rows-processed count, sqlca.sqlerrd[2].

In the following example, the INSERT is done one row at a time. This is much less efficient than the previous example, since a call to the server must be made for each row inserted.

for (i = 0; i < array_size; i++) 
    EXEC SQL INSERT INTO emp (ename, empno, sal) 
        VALUES (:emp_name[i], :emp_number[i], :salary[i]); 

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

int   emp_number[50]; 
float salary[50]; 
/* 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[2]. This number does not include rows processed by an update cascade.

If some elements in the arrays are irrelevant, you can use the embedded SQL 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:

char  job_title [10][20]; 
float commission[10]; 
 
... 
 
EXEC SQL UPDATE emp SET comm = :commission 
    WHERE job = :job_title;

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:

... 
int emp_number[50]; 
 
/* populate the host array */ 
... 
EXEC SQL DELETE FROM emp 
    WHERE empno = :emp_number; 

The cumulative number of rows deleted can be found in sqlerrd[2]. The 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:

... 
char job_title[10][20]; 
 
/* populate the host array  */ 
... 
EXEC SQL DELETE FROM emp 
    WHERE job = :job_title;
...

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:

char  emp_name[100][20]; 
float salary[100]; 
int   rows_to_insert; 
 
/* 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 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:

EXEC SQL FOR :rows_to_insert + 5                 /* illegal */ 
    INSERT INTO emp (ename, empno, sal) 
        VALUES (:emp_name, :emp_number, :salary); 

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.

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.

For example, assuming the declarations

int  mgr_number[50]; 
char job_title[50][20]; 

it would be ambiguous if the statement

EXEC SQL SELECT mgr INTO :mgr_number FROM emp 
WHERE job = :job_title; 

were treated like the imaginary statement

for (i = 0; i < 50; i++) 
    SELECT mgr INTO :mgr_number[i] FROM emp 
        WHERE job = :job_title[i]; 

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.

On the other hand, it would not be ambiguous if the statement

EXEC SQL UPDATE emp SET mgr = :mgr_number 
    WHERE empno IN (SELECT empno FROM emp 
        WHERE job = :job_title); 

were treated like the imaginary statement

for (i = 0; i < 50; i++) 
    UPDATE emp SET mgr = :mgr_number[i] 
        WHERE empno IN (SELECT empno FROM emp 
            WHERE job = :job_title[i]); 

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.

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

In order to perform multirow operations involving multiple columns, however, you 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 multirow, multicolumn 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.

Since arrays of structs are intended to be used when performing multirow 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.

You use the CURRENT OF cursor clause in a DELETE or UPDATE statement to refer to the latest row FETCHed from the cursor. 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.

For example:

char  emp_name[20][10]; 
char  job_title[20][10]; 
char  old_title[20][10]; 
char  row_id[20][19]; 
... 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
SELECT ename, job, rowid FROM emp FOR UPDATE; 
... 
EXEC SQL OPEN emp_cursor; 
EXEC SQL WHENEVER NOT FOUND do break; 
for (;;) 
{ 
    EXEC SQL FETCH emp_cursor 
        INTO :emp_name, :job_title, :row_id; 
    ... 
    EXEC SQL DELETE FROM emp 
        WHERE job = :old_title AND rowid = :row_id; 
    EXEC SQL COMMIT WORK; 
} 

The Oracle precompiler also supports the DB2 insert and fetch syntax for the host tables. The supported additional array insert and fetch syntax are shown in the following figures, respectively.

Figure 8-1 Additional Insert Syntax

Description of "Figure 8-1 Additional Insert Syntax"

Figure 8-2 Additional Fetch Syntax

Description of "Figure 8-2 Additional Fetch Syntax"

The optional ROWSET and ROWSET STARTING AT clauses are used in the fetch-orientation (FIRST, PRIOR, NEXT, LAST, CURRENT, RELATIVE and ABSOLUTE). Consider the following examples:

• FIRST ROWSET

• PRIOR ROWSET

• NEXT ROWSET

• LAST ROWSET

• CURRENT ROWSET

• ROWSET STARTING AT RELATIVEn

• ROWSET STARTING AT ABSOLUTEn

Examples of the DB2 array insert/fetch syntax and their comparison with the corresponding Oracle precompiler syntax are shown in Table 8-3:

EXEC SQL
  INSERT INTO dsn8810.act 
  (actno, actkwd, actdesc) 
  VALUES (:hva1, :hva2, :hva3)
  FOR :NUM_ROWS ROWS;

EXEC SQL FOR :num_rows
  INSERT INTO dsn8810.act
  (actno, actkwd, actdesc)
  VALUES (:hva1, :hva2, :hva3);

EXEC SQL
  FETCH NEXT ROWSET FROM c1 
  FOR 20 ROWS 
  INTO :hva_empno, :hva_lastname,
       :hva_salary;

EXEC SQL
   FOR :twenty
   FETCH c1 
   INTO :hva_empno, :hva_lastname,
        :hva_salary;

In DB2 syntax, a row-set positioned cursor should be first declared before retrieving row sets of data. To enable a cursor to fetch row sets, the 'WITH ROWSET POSITIONING' clause has to be used in the DECLARE CURSOR statement, which is not required and relevant in the Oracle precompiler syntax, as shown in the following table.

EXEC SQL
 DECLARE c1 CURSOR
  WITH ROWSET POSITIONING FOR
  SELECT empno, lastname, salary
      FROM dsn8810.emp;

EXEC SQL
   DECLARE c1 CURSOR FOR
   SELECT empno, lastname, salary
        FROM dsn8810.emp;

This DB2 array syntax support can be enabled with the precompiler option db2_array, whose default option is no. The DB2 array syntax support cannot be used together with the Oracle precompiler syntax; only one of the syntax, only on of the syntax, either Oracle precompiler or DB2 syntax, is supported at a time.

/* 
 * db2arrdemo.pc 
 */
 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>
 
/* Number of rows to be inserted in one shot */
#define INSCNT 100
/* Number of rows to be fetched in one shot */
#define FETCHCNT 20
 
/* Define a host structure
   for inserting data into the table 
   and for fetching data from the table */
struct emprec
{
  int     empno;
  varchar ename[10];
  varchar job[9];
  int mgr;
  char hiredate[10];
  float sal;
  float comm;
  int deptno;
};
typedef struct emprec empdata;
 
/* Function prototypes */
void sql_error(char *);
void insertdata();
void fetchdata();
void printempdata(empdata *);
 
void main()
{
 
  exec sql begin declare section;
    char *uid = "scott/tiger";
  exec sql end declare section;
 
  exec sql whenever sqlerror do sql_error("ORACLE error--\n");
  exec sql connect :uid;
 
  printf("Inserting %d rows into EMP table using DB2 array insert syntax.\n",
         INSCNT);
  insertdata();
  printf("\nFetching data using DB2 array fetch syntax.\n");
  fetchdata();
 
  exec sql rollback work release;
  exit(EXIT_SUCCESS);
}
 
/* Inserting data into the table using DB2 array insert syntax*/
void insertdata() 
{
  int i, cnt;
  char *str;
  empdata emp_in[INSCNT];
 
  /* To store temporary strings */
  str = (char *)malloc (25 * sizeof(char));
 
  /* Fill the array elements to insert */ 
  for (i = 0; i < INSCNT; i++)
  {
    emp_in[i].empno = i+1;
    sprintf(str, "EMP_%03d", i+1);
    strcpy (emp_in[i].ename.arr, str);
    emp_in[i].ename.len = strlen (emp_in[i].ename.arr);
    sprintf(str, "JOB_%03d", i+1);
    strcpy (emp_in[i].job.arr, str);
    emp_in[i].job.len = strlen (emp_in[i].job.arr);
    emp_in[i].mgr = i+1001;
    sprintf(str, "%02d-MAY-06", (i%30)+1);
    strcpy (emp_in[i].hiredate, str);
    emp_in[i].sal = (i+1) * 10;
    emp_in[i].comm = (i+1) * 0.1;
    emp_in[i].deptno = 10;
  }
 
  free (str);
 
  /* Inserting data using DB2 array insert syntax */
  exec sql insert into emp values (:emp_in) FOR :INSCNT rows;
 
  exec sql select count(*) into :cnt from emp where ename like 'EMP_%';
  printf ("Number of rows successfully inserted into emp table: %d\n", cnt);
}
 
/* Fetches data from the table using DB2 array fetch syntax*/
void fetchdata()
{
  empdata emp_out[FETCHCNT];
 
  /* Declares scrollable cursor to fetch data */
  exec sql declare c1 scroll cursor with rowset positioning for
         select empno, ename, job, mgr, hiredate, sal, comm, deptno
         from emp where ename like 'EMP_%' order by empno;
  
  exec sql open c1;
 
  exec sql whenever not found do break;
  while(1)
  {
    /* Fetches data using DB2 array fetch syntax */
    exec sql fetch next rowset from c1 for :FETCHCNT rows into :emp_out;
    printempdata(emp_out);
  }
  exec sql whenever not found do sql_error("ORACLE ERROR");
 
  exec sql close c1;
}
 
/* Prints the fetched employee data */
void printempdata(empdata *emp_out)
{
  int i;
  for (i=0; i<FETCHCNT; i++)
  {
    emp_out[i].ename.arr[emp_out[i].ename.len] = '\0';
    emp_out[i].job.arr[emp_out[i].job.len] = '\0';
    printf("Empno=%d, Ename=%s, Job=%s, Mgr=%d, Hiredate=%s, Sal=%6.2f,\n"
           "Comm=%5.2f, Deptno=%d\n", emp_out[i].empno, emp_out[i].ename.arr,
 emp_out[i].job.arr, emp_out[i].mgr, emp_out[i].hiredate,
 emp_out[i].sal, emp_out[i].comm, emp_out[i].deptno);
  }
}
 
/* Error handling function. */
void sql_error(char *msg)
{
  exec sql whenever sqlerror continue;
 
  printf("\n%s\n", msg);
  printf("%.70s\n", sqlca.sqlerrm.sqlerrmc); 
  exec sql rollback release;
 
  exit(EXIT_FAILURE);
}

For improved performance, Pro*C/C++ application developers can reference host arrays in their embedded SQL statements. This provides a means to execute an array of SQL statements with a single round-trip to the database. Despite the significant performance improvements afforded by array execution, some developers choose not to use this capability because it is not ANSI standard. For example, an application written to exploit array execution in Oracle cannot be precompiled using IBM's precompiler.

One workaround is to use buffered INSERT statements, which enable you to gain performance benefits while retaining ANSI standard embedded SQL syntax.

The command line option "max_row_insert" controls the number of rows to be buffered before executing the INSERT statement. By default it is zero and the feature is disabled. To enable this feature, specify any number greater than zero.

If insert bufering is enabled, precompiler runtime will flag the corresponding cursor and:

• Allocate or re-allocate extra memory to hold bind values (first execute only).

• Copy bind values from program host variables to internal runtime bind structures.

• Increment the rows buffered count.

• Flush the buffered INSERT statements if MAX_INSERT_ROWS have been buffered.

• If MAX_INSERT_ROWS has not been hit, then return after copying the values to the internal bind buffers without flushing.

If a new embedded SQL statement is executed and results in a flush of the buffered insert statements:

• Flush the buffers.

• Continue with the call that prompted the flush.

The application is informed of the error through the standard precompiler error mechanisms such as the sqlca in Pro*C.

The "implicit_svpt" option controls whether an implicit savepoint is taken prior to the start of a new batched insert.

• If yes, a savepoint is taken prior to the start of a new batch of rows. If an error occurs on the insert, an implicit "rollback to savepoint" is executed.

• If no, there is no implicit savepoint taken. If an error occurs on the buffered insert, then it is reported back to the application, but no rollback is executed. Errors are reported asynchronously for buffer inserts. Errors for inserted rows are not reported when the INSERT statement is executed in the application.

  • Some errors for inserted rows are reported later, when the first statement other than the INSERT is executed. This may include DELETE, UPDATE, INSERT (into different tables), COMMIT, and ROLLBACK. Any statement that closes the buffered insert statement can report an error. In such cases, the statement that reports the error is not executed. You need to first handle the error and also reexecute the statement on which the buffered insert error is reported. Otherwise, you may rollback the transaction and reexecute it.

    For example, consider using a COMMIT statement to close a buffered insert loop. COMMIT can report an error because of a duplicate key from an earlier insert. In this scenario, the commit is not executed. You should first handle the error and then reexecute COMMIT. Otherwise, you can rollback the transaction and reexecute it.

  • Some errors are reported on the insert itself, and may reflect an error of a previously inserted row. In such cases, no further inserts are executed. You need to handle the errors of the previously inserted row and continue inserting the current insert, which is a long process. Instead, you may rollback and reexecute the transaction.

    For example, consider that the limit of internal buffer is 10 rows and the application is inserting 15 rows in a loop. Suppose there is an error on the 8th row. The error is reported when the 11th row insert happens and the insert is no more executed further.

The following are some of the possible errors that you might face during buffered insert:

• ORA-00001: duplicate key in index

• ORA-01400: mandatory (not null) column is missing or Null during insert

• ORA-01401: inserted value too large for column

• ORA-01438: value larger than specified precision allows for this column

/* 
 * bufinsdemo.pc 
 */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>

/* Number of rows to be inserted into the table */
#define LOOPCNT 100

/* Define a host structure
   for inserting data into the table 
   and for fetching data from the table */
struct emprec
{
  int empno;
  varchar ename[10];
  varchar job[9];
  int mgr;
  char hiredate[10];
  float sal;
  float comm;
  int deptno;
};
typedef struct emprec buffinstyp;

/* Function prototypes */
void sql_error();
void insertdata();
void fetchdata();
void printempdata(buffinstyp);

void main()
{

  exec sql begin declare section;
    char *uid = "scott/tiger";
  exec sql end declare section;

  exec sql whenever sqlerror do sql_error();
  exec sql connect :uid;

  printf("\nInserting %d rows into EMP table.\n", LOOPCNT);
  insertdata();
  printf("\nFetching inserted data from EMP table.\n");
  fetchdata();

  exec sql delete from emp where empno < 1000;

  exec sql commit work release;
  exit(EXIT_SUCCESS);
}

/* Inserting data into the table */
void insertdata() 
{
  int i, cnt;
  char *str;
  buffinstyp emp_in;

  /* To store temporary strings */
  str = (char *)malloc (25 * sizeof(char));

  /*
   * When max_row_insert option is set to LOOPCNT and when the errorneous
   * statement is removed, all the rows will be inserted into the database in
   * one stretch and hence maximum performance gain will be achieved.
 */
  for (i = 1; i <= LOOPCNT; i++)
  {
    if (i != 5)
      emp_in.empno = i;
    else
    /* Errorneous statement. In emp table, empno is defined as number(4). */
      emp_in.empno = 10000;

    sprintf(str, "EMP_%03d", i);
    strcpy (emp_in.ename.arr, str);
    emp_in.ename.len = strlen (emp_in.ename.arr);
    sprintf(str, "JOB_%03d", i);
    strcpy (emp_in.job.arr, str);
    emp_in.job.len = strlen (emp_in.job.arr);
    emp_in.mgr = i+1001;
    sprintf(str, "%02d-MAY-06", (i%30));
    strcpy (emp_in.hiredate, str);
    emp_in.sal = (i) * 10;
    emp_in.comm = (i) * 0.1;
    emp_in.deptno = 10;

    exec sql insert into emp values (:emp_in);
  }

  free (str);

  exec sql commit;

  exec sql select count(*) into :cnt from emp where ename like 'EMP_%';
  printf ("Number of rows successfully inserted into emp table: %d\n", cnt);
}

/* Fetches data from the table*/
void fetchdata()
{
  buffinstyp emp_out;

  /* Declares cursor to fetch only the rows that are inserted */
  exec sql declare c1 cursor for
         select empno, ename, job, mgr, hiredate, sal, comm, deptno
         from emp where ename like 'EMP_%' order by empno;
  
  exec sql open c1;

  exec sql whenever not found do break;
  while(1)
  {
    /* Fetches single row at each call */
    exec sql fetch c1 into :emp_out;
    printempdata(emp_out);
  }
  exec sql whenever not found do sql_error();

  exec sql close c1;
}

/* Prints the fetched employee data */
void printempdata(buffinstyp emp_out)
{
  emp_out.ename.arr[emp_out.ename.len] = '\0';
  emp_out.job.arr[emp_out.job.len] = '\0';
  printf("Empno=%d, Ename=%s, Job=%s, Mgr=%d, Hiredate=%s, Sal=%6.2f,\n"
         "Comm=%5.2f, Deptno=%d\n", emp_out.empno, emp_out.ename.arr,
 emp_out.job.arr, emp_out.mgr, emp_out.hiredate, emp_out.sal,
 emp_out.comm, emp_out.deptno);
}

/* Error handling function. */
void sql_error()
{
   printf("Error %s\n", sqlca.sqlerrm.sqlerrmc);
   printf(" Rows Processed: %d\n", sqlca.sqlerrd[2]);
   printf(" Rows Rolled Back: %d\n", sqlca.sqlerrd[0]);
}

A scrollable cursor is a work area where Oracle executes SQL statements and stores information that is processed during execution.When a cursor is executed, the results of the query are placed into a a set of rows called the result set. The result set can be fetched either sequentially or non-sequentially. Non-sequential result sets are called scrollable cursors. A scrollable cursor enables users to access the rows of a database result set in a forward, backward, and random manner. This enables the program to fetch any row in the result set.