8 Host Arrays

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 array elements in a SQL statement that is accessible in one fetch is 32K (or possibly greater, depending on the platform and the available memory). If you try to access a number that exceeds the maximum, you get a "parameter out of range" runtime error. If the statement is an anonymous PL/SQL block, the number of array elements accessible is limited to 32512 divided by the size of the datatype.

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.

In this example, only 25 rows are Inserted

int    emp_number[50]; 
char   emp_name[50][10]; 
int    dept_number[25]; 
/* Populate host arrays here. */ 

EXEC SQL INSERT INTO emp (empno, ename, deptno) 
    VALUES (:emp_number, :emp_name, :dept_number);
 

It is possible to subscript host arrays in SQL statements, and use them in a loop to INSERT or fetch data. For example, you could INSERT every fifth element in an array using a loop such as:

for (i = 0; i < 50; i += 5) 
    EXEC SQL INSERT INTO emp (empno, deptno) 
        VALUES (:emp_number[i], :dept_number[i]);

However, if the array elements that you need to process are contiguous, you should not process host arrays in a loop. Simply use the non-scripted 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:

int    emp_number[50]; 
int    dept_number[50]; 
float  commission[50]; 
short  comm_ind[50];       /* indicator array */ 

/* Populate the host and indicator arrays.  To insert a null 
   into the comm column, assign -1 to the appropriate 
   element in the indicator array. */ 
    EXEC SQL INSERT INTO emp (empno, deptno, comm) 
        VALUES (:emp_number, :dept_number, 
        :commission INDICATOR :comm_ind); 

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.

If DBMS=V7 or DBMS=v8 and you SELECT or FETCH a NULL column value into a host array that is not associated with an indicator array, then Oracle stops processing, sets sqlerrd[2] to the number of rows processed, and returns an error message. When DBMS=V7 or DBMS=v8, Oracle does not consider truncation to be an error.

Also, if your SELECT or FETCH results in any warning such as ORA-24347 due to usage of NULL, and if any column does not have an indicator array, Oracle stops processing.

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:

 
int   emp_number[20]; 
float salary[20]; 
 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT empno, sal FROM emp; 
 
EXEC SQL OPEN emp_cursor; 
 
EXEC SQL WHENEVER NOT FOUND do break; 
for (;;) 
{ 
    EXEC SQL FETCH emp_cursor 
        INTO :emp_number, :salary; 
    /* process batch of rows */ 
    ... 
} 
...

Do not forget to check how many rows were actually returned in the last fetch, and process them.

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:

int  emp_number[100]; 
char emp_name[100][20]; 
 
int rows_to_fetch, rows_before, rows_this_time; 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT empno, ename 
    FROM emp 
    WHERE deptno = 30; 
EXEC SQL OPEN emp_cursor; 
EXEC SQL WHENEVER NOT FOUND CONTINUE; 
/* initialize loop variables */ 
rows_to_fetch = 20;   /* number of rows in each "batch" */ 
rows_before = 0;      /* previous value of sqlerrd[2]  */ 
rows_this_time = 20; 
 
while (rows_this_time == rows_to_fetch) 
{ 
    EXEC SQL FOR :rows_to_fetch 
    FETCH emp_cursor 
        INTO :emp_number, :emp_name; 
    rows_this_time = sqlca.sqlerrd[2] - rows_before; 
    rows_before = sqlca.sqlerrd[2]; 
} 
... 

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 20 number elements 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) */

Scrollable Cursor Fetches

You can also use host arrays with scrollable cursors. With scrollable cursors sqlca.sqlerrd[2] represents the maximum (absolute) row number processed. Since an application can arbitrarily position the fetches in scrollable mode, it need not be the total number of rows processed.

While using host arrays with the FETCH statement in scrollable mode, you cannot subtract the current value of sqlca.sqlerrd[2] from its previous value to find the number of rows returned by the most recent iteration. The application program determines the total number of rows in the result set by executing a FETCH LAST. The value of sqlca.sqlerrd[2] provides the total number of rows in the result set. Refer to "Sample Program: Host Arrays Using Scrollable Cursor" for an example illustrating the use of host arrays with scrollable cursors

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]). This program is available on-line in the file sample3.pc in your demo directory.

/*
 *  sample3.pc
 *  Host Arrays
 *
 *  This program connects to ORACLE, declares and opens a cursor,
 *  fetches in batches using arrays, and prints the results using
 *  the function print_rows().
 */

#include <stdio.h>
#include <string.h>

#include <sqlca.h>

#define NAME_LENGTH   20
#define ARRAY_LENGTH   5
/* Another way to connect. */
char *username = "SCOTT";
char *password = "TIGER";

/* Declare a host structure tag. */
struct
{
    int    emp_number[ARRAY_LENGTH];
    char   emp_name[ARRAY_LENGTH][NAME_LENGTH];
    float  salary[ARRAY_LENGTH];
} emp_rec;

/* Declare this program's functions. */
void print_rows();              /* produces program output */
void sql_error();          /* handles unrecoverable errors */


main()
{
    int  num_ret;               /* number of rows returned */
  
/* Connect to ORACLE. */
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:");

    EXEC SQL CONNECT :username IDENTIFIED BY :password;
    printf("\nConnected to ORACLE as user: %s\n", username);


    EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:");
/* Declare a cursor for the FETCH. */
    EXEC SQL DECLARE c1 CURSOR FOR
        SELECT empno, ename, sal FROM emp;

    EXEC SQL OPEN c1;

/* Initialize the number of rows. */
    num_ret = 0;

/* Array fetch loop - ends when NOT FOUND becomes true. */
    EXEC SQL WHENEVER NOT FOUND DO break;

    for (;;)
    {
        EXEC SQL FETCH c1 INTO :emp_rec;

/* Print however many rows were returned. */
        print_rows(sqlca.sqlerrd[2] - num_ret);
        num_ret = sqlca.sqlerrd[2];        /* Reset the number. */
    }
/* Print remaining rows from last fetch, if any. */
    if ((sqlca.sqlerrd[2] - num_ret) > 0)
        print_rows(sqlca.sqlerrd[2] - num_ret);

    EXEC SQL CLOSE c1;
    printf("\nAu revoir.\n\n\n");

/* Disconnect from the database. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}


void
print_rows(n)
int n;
{
    int i;

    printf("\nNumber   Employee         Salary");
    printf("\n------   --------         ------\n");

    for (i = 0; i < n; i++)
        printf("%-9d%-15.15s%9.2f\n", emp_rec.emp_number[i],
               emp_rec.emp_name[i], emp_rec.salary[i]);

}


void
sql_error(msg)
char *msg;
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s", msg);
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

Sample Program: Host Arrays Using Scrollable Cursor

This program describes how to use host arrays with scrollable cursors. This program is available on-line in the file scrolldemo2.pc in your demo directory.

/*
 *  A Sample program to demonstrate the use of scrollable 
 *  cursors with host arrays.
 * 
 *  This program uses the hr/hr schema.Make sure
 *  that this schema exists before executing this program
 */


#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

#define ARRAY_LENGTH   4

/* user and passwd */
char *username = "hr";
char *password = "hr";

/* Declare a host structure tag. */
struct emp_rec_array
{
    int    emp_number;
    char   emp_name[20];
    float  salary;
} emp_rec[ARRAY_LENGTH];


/* Print the result of the query */

void print_rows()
{
    int i;

    for (i=0; i<ARRAY_LENGTH; i++)
        printf("%d    %s  %8.2f\n", emp_rec[i].emp_number,
             emp_rec[i].emp_name, emp_rec[i].salary);

}

/*  Oracle error handler */

void sql_error(char *msg)
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s", msg);
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(EXIT_FAILURE);
}

void main()
{
    int noOfRows; /* Number of rows in the result set */

    /* Error handler */
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:");

    /* Connect to the data base */
    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    /* Error handle */
    EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:");

    /* declare the cursor in scrollable mode */
    EXEC SQL DECLARE c1 SCROLL CURSOR FOR
        SELECT employee_id, first_name, salary FROM employees;

    EXEC SQL OPEN c1;

    EXEC SQL WHENEVER SQLERROR DO sql_error("Fetch Error:");

    /* This is a dummy fetch to find out the number of rows
       in the result set */
    EXEC SQL FETCH LAST c1 INTO :emp_rec;

    /* The number of rows in the result set is given by 
       the value of sqlca.sqlerrd[2] */

    noOfRows = sqlca. sqlerrd[2];
    printf("Total number of rows in the result set %d:\n", 
             noOfRows);

    /* Fetch the first ARRAY_LENGTH number of rows */
    EXEC SQL FETCH FIRST c1 INTO :emp_rec;
    printf("******************** DEFAULT : \n");
    print_rows();

    /* Fetch the next set of ARRAY_LENGTH rows */
    EXEC SQL FETCH NEXT c1 INTO :emp_rec;
    printf("******************** NEXT  : \n");
    print_rows();

    /* Fetch a set of ARRAY_LENGTH rows from the 3rd row onwards */
    EXEC SQL FETCH ABSOLUTE 3 c1 INTO :emp_rec;
    printf("******************** ABSOLUTE 3 : \n");
    print_rows();

    /* Fetch the current ARRAY_LENGTH set of rows */
    EXEC SQL FETCH CURRENT c1 INTO :emp_rec;
    printf("******************** CURRENT : \n");
    print_rows();

    /* Fetch a set of ARRAY_LENGTH rows from the 2nd offset
       from the current cursor position */
    EXEC SQL FETCH RELATIVE 2 c1 INTO :emp_rec;
    printf("******************** RELATIVE 2 : \n");
    print_rows();

    /* Again Fetch the first ARRAY_LENGTH number of rows */
    EXEC SQL FETCH ABSOLUTE 0 c1 INTO :emp_rec;
    printf("******************** ABSOLUTE 0 : \n");
    print_rows();

    /* close the cursor */
    EXEC SQL CLOSE c1;

/* Disconnect from the database. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(EXIT_SUCCESS);
}

Host Array Restrictions

Using host arrays in the WHERE clause of a SELECT statement is not allowed except in a subquery. For an example, see "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:

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 or DBMS=v8, if you SELECT or FETCH a NULL column value into a host array that is not associated with an indicator array, Oracle stops processing, sets sqlerrd[2] to the number of rows processed, and issues an error message.

Also, if your SELECT or FETCH results in any warning such as ORA-24347 due to usage of NULL, and if any column does not have an indicator array, Oracle stops processing. Use indicator variables in all the columns in the SELECT or FETCH.If all columns do not have indicators, the precompiler option unsafe_null=yes could be used as an alternative.

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 Restrictions

You cannot use an array of pointers in the VALUES clause of an INSERT statement; all array elements must be data items.

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

Updating with Arrays 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.

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

Deleting with Arrays 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 Clause Restrictions

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

EXEC SQL FOR :limit FETCH emp_cursor INTO ... 

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; 

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; 

Arrays of Structs Usage

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 also " 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 does not 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  age;
    } 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++

struct department {
   int deptno;
   char dname[15];
   char loc[14];
} dept[4];

while the following declaration is illegal.

struct {              /* the struct is missing a structure tag */
  int empno[15];      /* struct members may not be arrays */
  char ename[15][10]; /* character types may not be 2-dimensional */
  struct nested {
    int salary;   /* nested struct not permitted in array of structs */
  } sal_struct;
} bad[15];

It is also important to note that you cannot 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 earlier 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.

typedef int myint;
exec sql type myint is integer(4);

struct equiv {
  myint empno; /* now legally considered an integer(4) datatype */
   ...
} ok[15];

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.

int empno[15];
exec sql var empno[3] is integer(4); /* illegal */

myint empno[15]; /* legal */

In summary, you may not equivalence any individual array item.

Variables Guidelines

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 as follows:

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

  See Also:

  "Declaring an Array of Structs"

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.

Given these rules, assume the following struct declaration:

struct department {
   int deptno;
   char dname[15];
   char loc[14];
} dept[4];

The following is a legal indicator variable struct declaration:

struct department_ind {
   short deptno_ind;
   short dname_ind;
   short loc_ind;
} dept_ind[4];

while the following is illegal as an indicator variable

struct{               /* missing indicator structure tag */
  int deptno_ind;     /* indicator variable not of type short */
  short dname_ind[15];/* array element forbidden in indicator struct */
  short loc_ind[14];  /* array element forbidden in indicator struct */
} bad_ind[2];     /* indicator array size is smaller than host array */

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.

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

struct department {
   int deptno;
   char dname[15];
   char loc[14];
} my_dept[4];

exec sql select * into :my_dept from dept;

exec sql insert into dept values (:my_dept);

struct deptartment_ind {
   short deptno_ind;
   short dname_ind;
   short loc_ind;
} my_dept_ind[4];

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

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

struct employee {
   int empno;
   char ename[11];
} emp[14];

float sal[14];
float comm[14];

exec sql select empno, ename, sal, comm into :emp, :sal, :comm from emp;

short comm_ind[14];
...
exec sql select empno, ename, sal, comm
   into :emp, :sal, :comm indicator :comm_ind from emp;

struct employee_ind {   /* example of illegal usage */
   short empno_ind;
   short ename_ind;
   short sal_ind;
   short comm_ind;
} illegal_ind[15];

exec sql select empno, ename, sal, comm
   into :emp, :sal, :comm indicator :illegal_ind from emp;

exec sql insert into emp (empno, ename, sal, comm)
   values (:emp, :sal, :comm indicator :comm_ind);

struct employee {
   int empno;
   char ename[11];
   char job[10];
} emp[14];

struct compensation {
   int sal;
   int comm;
} wage[14];

struct compensation_ind {
   short sal_ind;
   short comm_ind;
} wage_ind[14];

exec sql declare c cursor for 
   select empno, ename, job, sal, comm from emp;

exec sql open c;

exec sql whenever not found do break;
while(1)
{
  exec sql fetch c into :emp, :wage indicator :wage_ind;
  ... process batch rows returned by the fetch ...
}

printf("%d rows selected.\n", sqlca.sqlerrd[2]);

exec sql close c;

int limit = 10;

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

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

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

struct employee {
    float comm;
    float sal;
    int empno;
    char ename[10];
} emp[14];

struct employee_ind {
    short comm;
} emp_ind[14];

exec sql select comm, sal, empno, ename 
   into :emp indicator :emp_ind from emp;

ORA-1405: fetched column value is NULL

struct employee {
   int empno;
   char ename[10];
   float sal;
   float comm;
} emp[15];

struct employee_ind {
   short comm;
   short sal;
   short empno;
   short ename;
} emp_ind;

struct employee_ind {
   short comm;
} emp_ind;

exec sql fetch comm, sal, empno, ename
   into :emp indicator :emp_ind from emp;

typedef struct dept {
   int deptno;
   char dname[15];
   char loc[14];
} dept;

void insert_data(d, n)
   dept *d;
   int n;
{
    exec sql for :n insert into dept values (:d);
}

void fetch_data(d, n)
   dept *d;
   int n;
{
   exec sql declare c cursor for select deptno, dname, loc from dept;
   exec sql open c;
   exec sql for :n fetch c into :d;
   exec sql close c;
}

dept d[4];
dept *dptr = &d[0];
const int n = 4;

fetch_data(dptr, n);
insert_data(d, n); /* We are treating '&d[0]' as being equal to 'd' */

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