Fetch into an Array

David Clement

September 2004

Oracle versions 7-8

A fetch involves two significant processing steps. First it confirms that data is available to satisfy the query. For a user session connecting by means of the API available to Oracle Pro*C/C++ programs, this involves populating the SQL communications area (SQLCA). Second, the fetch performs the memory-to-memory copy of the data from the database block buffer in the SGA to the user process. Both of these steps go through the server process that connects the user process (often on a different machine) to the Oracle instance and database. In a client-server environment, both steps involve network transmission.

If a fetch returns a single row of data to the user process, then there have been two trips across the network for this row of data. Fetching one hundred rows of data one row at a time implies two hundred trips, each calling a database kernel subroutine. This processing effort can be greatly reduced by fetching into an array.

An array fetch returning twenty rows would fetch data for the SQLCA and then would fetch twenty rows of table data. Fetching one hundred rows of data into a twenty-row array implies ten round trips, not two hundred.

The coding technique is not challenging. Here is an example supplied by Tom Kyte of Oracle in his "Ask Tom" column.

          void myprocess (void)
          {
              typedef char enameType[30];
              exec sql type enameType is string[30];
              enameType ename[100];
              
              char job[100][20];
              exec sql var job is string[20];
              
              int i;
              
              exec sql whenever sqlerror do sqlerror_hard( );
              
              exec sql declare c cursor
              for 
              select ename, job
              from emp;
              
              exec sql open c;
              
              for (; sqlca.sqlcode == 0; ) {
                  exec sql fetch c into :ename, :job;
                  for (i = 0; i < sqlca.sqlerrd[2]; i++)
                      printf ("'%s', '%s'\n", ename[i], job[i]);
              }
              exec sql close;
              exec sql commit;
          }