18 Collections

Nested Tables

A nested table is a collection of rows, called elements, within a column. For each row of the database table there are many such elements. A simple example is the list of tasks that each employee is working on. Thus many-to-one relationships can be stored in one table, without needing to join employee and task tables.

Nested tables differ from C and C++ arrays in these ways:

• Arrays have a fixed upper bound; nested tables are unbounded (have no maximum index).

• Arrays have consecutive subscripts (are dense); nested tables can be either dense or sparse. When a nested table is retrieved into an array in a program, the gaps are skipped, resulting in a filled array that has no gaps.

Use the CREATE TYPE statement to define a table type that can be nested within other object types in one or more columns of a relational table.

For example:, to store several projects in each department of an organization:

CREATE TYPE project_type AS OBJECT (
    pno            CHAR(5),
    pname          CHAR(20),
    budget         NUMBER(7,2)) ;

CREATE TYPE project_table AS TABLE OF project_type ;

CREATE TABLE depts (
    dno            CHAR(5),
    dname          CHAR(20),
    budgets_limit  NUMBER(15,2),
    projects       project_table)
    NESTED TABLE projects STORE AS depts_projects ;

Varrays

Unlike nested tables, you must specify the maximum number of elements when you create a VARRAY type. Varrays are only dense, unlike nested tables, which can be dense or sparse. The elements of a varray and a nested table are both numbered from 0.

You create a varray with CREATE TYPE statements such as:

CREATE TYPE employee AS OBJECT 
(
  name   VARCHAR2(10),
  salary NUMBER(8,2)
) ;
CREATE TYPE employees AS VARRAY(15) OF employee ;
CREATE TYPE department AS OBJECT
(
  name VARCHAR2(15),
  team employees
) ;

Use VARRAY as a datatype of a column of a relational table, or as the attribute of an object type. This saves storage space compared to a relational table that has up to 15 records for each team, each containing the team's name.

C and Collections

In a C or C++ program a nested table is read in starting from index value 0 of the collection. When you write the nested table into an array, the elements of the nested table are stored starting at array index 0. When a nested table which is sparse (has gaps in its index) is stored into an array, the gaps are skipped. When reading the array back into the nested table, the gaps are re-created.

In a C or C++ program varrays are written into an array, starting at index 0. When read back into the varray, the elements are restored starting at index 0 in the same order. Thus, arrays are easily used to access collections.

Declarations for Host and Indicator Variables

As for the other object types, a host variable representing a collection object type must be declared as a pointer to the appropriate OTT-generated typedef.

Unlike other object types, however, the indicator variable for a collection object type as a whole is declared as a scalar signed 2-byte type, OCIInd. The indicator variable is optional, but it is a good programming practice to use one for each host variable declared in Pro*C/C++.

Manipulating Collections

There are two ways to manipulate a collection: the collection is treated as an autonomous entity without access to its elements, or its elements are accessed, appended to, truncated, and so on.

Rules for Access

Access rules are different for autonomous and element access.

Indicator Variables

Each access method has its own way of using indicator variables.

COLLECTION GET

Purpose

The COLLECTION GET statement is analogous to the OBJECT GET statement, but is designed for collections. It retrieves the elements of a collection, sets the current slice, and converts elements to C types where appropriate.

Syntax

EXEC SQL [AT [:]database] [FOR :num]
   COLLECTION GET :collect [[INDICATOR] :collect_ind]
      INTO :hv [[INDICATOR] :hv_ind] ;

Variables

num (IN)

The number of elements requested. If this clause is omitted, the array size of the host variable (scalar has 1) determines the number of elements retrieved from the collection.

collect (IN)

A host variable C collection descriptor.

collect_ind (IN)

An optional indicator variable returning the NULL status of the collection.

hv (OUT)

The host variable that receives the collection element values.

hv_ind (OUT)

An optional indicator variable returning the NULL status of hv if it is a scalar, or an array holding the status of each element in the slice.

Usage Notes

The number of elements actually returned in the last COLLECTION GET is set in sqlca.sqlerrd[2] (not the cumulative total of all GETs). See also "sqlerrd".

The number returned can be less than the number requested when one or both of the slice endpoints exceed the collection bounds. This can occur when:

• The collection descriptor has not been initialized in a syntactically correct ALLOCATE statement, is NULL, or invalid for any other reason.

• The collection is NULL. Its associated indicator is -1.

• The collection is empty (has no elements).

• More elements were requested than are remaining in the collection.

• A COLLECTION TRIM statement has been executed, resulting in the endpoint index being below the starting index of the current slice.

An improperly initialized C collection descriptor results in an error. All other situations in the list will raise an ORA-01403: no data found error condition. In this case, the total number of elements successfully retrieved before the error occurs is still stored in sqlca.sqlerrd[2].

The initial GET or the first GET after a RESET affects the slice as follows:

• The ending index of the slice will be the index where the final element was found; it depends on how many elements are requested. If there were not enough elements in the remaining portion of the collection to satisfy the request, then the ending index will be the last index in the collection.

Subsequent GETs affect the slice indexes as follows:

• The index of the start-point is the index where the first element was found after the end-point of the previous slice. If no more elements are left after the end-point of the previous slice, the starting index is that of the last element in the collection.

• The ending index of the next slice will be the index where the final element was found; it depends on how many elements are requested. If there were not enough elements in the remaining portion of the collection to satisfy the request given the position of the previous slice, then the ending index will be the last index in the collection.

COLLECTION SET

Purpose

The COLLECTION SET statement is analogous to the OBJECT SET statement; it is used for updating element values of collections. Elements in the current slice are converted from the native C types to Oracle datatypes.

Syntax

EXEC SQL [AT [:]database] [FOR :num]
   COLLECTION SET :collect [[INDICATOR] :collect_ind]
      TO :hv [[INDICATOR] :hv_ind] ;

Variables

num (IN)

This optional scalar value is the maximum number of elements to be updated in the slice. If this clause is omitted, the array size of the host variable (scalar has 1) determines the number of elements updated from the collection.

collect (OUT)

A host variable C collection descriptor.

collect_ind (OUT)

An optional indicator variable which determines the NULL status of the collection.

hv (IN)

A host variable that contains the values to be updated in the collection.

hv_ind (IN)

An associated indicator variable representing the NULL status of the host variable.

Usage Notes

The following restrictions apply:

• A COLLECTION GET must be executed before a COLLECTION SET.

• The starting and ending indexes of the slice always remain unchanged. This is true even if less elements are placed in the collection than can be stored in the current slice. A SET statement never changes the endpoints of the slice. It only changes elements in the current slice.

• The COLLECTION SET is intended only to update those elements in the current slice. You cannot append new elements to a collection using the COLLECTION SET statement.

• If an attempt is made to SET more elements than the current slice contains, only those elements that fit in the existing slice are updated. Remaining elements beyond the end of the slice are unaffected and any extra values supplied by the host variable are unused.

The dimension of the host variable or the value num specified in an optional FOR clause gives the maximum number of elements requested to be updated in the collection.

The variable sqlca.sqlerrd[2] returns the number of elements successfully updated by the previous SET statement (not a cumulative total), which can be less than the number requested to be set (as for the GET), for these cases:

• The C collection descriptor has not been correctly initialized in a syntactically correct ALLOCATE statement, or is NULL, or otherwise invalid.

• The collection is empty.

• There were fewer elements in the remaining portion of the collection than were requested to be set given the position of the current slice in the collection.

• The end of the current slice was breached. This can happen only when an attempt is made to set more elements than are in the existing slice.

• A TRIM has been performed on the collection, bringing the maximum endpoint index value of the collection below the starting index of the current slice.

Issuing a COLLECTION SET immediately after a COLLECTION GET or SET will only update the values of the elements in the existing slice. A COLLECTION GET that immediately follows a COLLECTION SET will advance the slice as already described.

COLLECTION RESET

Purpose

Reset the collection slice endpoints back to the beginning of the collection.

Syntax

EXEC SQL [AT [:]database]
   COLLECTION RESET :collect 
      [ [INDICATOR] :collect_ind] ;

Variables

collect (IN/OUT)

The collection whose endpoints are to be reset.

collect_ind

Optional indicator variable determining NULL status of the collection.

Usage Notes

An error occurs if the given collection is NULL, or otherwise invalid.

COLLETION RESET does not affect the size or contents of the collection.

COLLECTION APPEND

Purpose

This statement appends a set of elements (one or more) to the end of a collection, increasing the size of the collection.

Syntax

EXEC SQL [AT [:]database] [FOR :num]
   COLLECTION APPEND :src [[INDICATOR] :src_ind]
      TO :collect [[INDICATOR] :collect_ind] ;

Variables

num (IN)

A scalar that contains the number of elements to be appended. If absent, the array size of src is the number of elements to be appended.

src (IN)

Scalar or array of elements to be appended to the collection.

src_ind (IN)

An optional indicator variable (scalar or array) determining the NULL status of the elements appended.

collect (IN OUT)

The collection to which elements are appended.

collect_ind (IN)

An optional indicator variable determining the NULL status of the collection.

Usage Notes

Elements are appended one at a time (the collection is increased in size by one, the data is copied to that element, and so on).

The variable sqlca.sqlerrd[2] returns the number of elements successfully appended by the latest APPEND (not a cumulative total). An error results if there is an attempt to add elements beyond the upper bound of the collection, or to append to a NULL collection. Only the elements that fit will be appended.

COLLECTION TRIM

Purpose

This statement removes elements from the end of a collection.

Syntax

EXEC SQL [AT [:]database]
   COLLECTION TRIM :num
      FROM :collect [[INDICATOR] :collect_ind] ;

Variables

num (IN)

A host scalar variable which is how many elements are to be removed. The maximum permitted value is two Gigabytes.

collect (IN OUT)

The collection to be trimmed.

collect_ind (IN)

An optional indicator variable determining the NULL status of the collection.

Usage Notes

Restrictions applied:

• The FOR clause is not allowed.

• The maximum value of num is 2 Gigabytes (the largest number in a 4-byte signed binary variable.

• No indicator is allowed with num.

If num is greater than the size of the collection, an error is returned. A warning is returned if a TRIM removes any elements from the current slice.

COLLECTION DESCRIBE

Purpose

This statement returns information about a collection.

Syntax

EXEC SQL [AT [:]database] 
   COLLECTION DESCRIBE :collect [[INDICATOR] :collect_ind]
      GET attribute1 [{, attributeN}]
         INTO :hv1 [[INDICATOR] :hv_ind1] [{, hvN [[INDICATOR] :hv_indN]}] ;

where attributeN is:

DATA_SIZE | TYPECODE | DATA_TYPE | NUM_ELEMENTS
   | PRECISION | SCALE | TYPE_NAME | TYPE_SCHEMA | SIZE | TABLE_SIZE

Variables

collect (IN)

The host variable C collection descriptor.

collect_ind (IN)

An optional indicator variable containing the NULL status of the collection.

hv1 .. hvN (OUT)

Output host variables where the information is to be stored.

hv_ind1 .. hv_indN (OUT)

Indicator variables for the output host variables.

Usage Notes

These restrictions are in effect:

• The collection cannot be NULL.

• Host variable types should be compatible with the types of the returned attributes.

• Indicator variables for the attributes are only required for TYPE_NAME and TYPE_SCHEMA attribute values where text truncation can occur.

• A FOR clause is not allowed.

• The variable sqlca.sqlerrd[2] returns the number of successful attributes retrieved with no errors. If the DESCRIBE statement incurs an error, then sqlca.sqlqerrd[2] contains the number of attributes returned before that error, and is one less than the attribute where the error occurred.

The following table gives attributes, descriptions, and C types for the attributes retrieved:

Rules for the Use of Collections

• A host variable collection descriptor must always be explicitly allocated.

• The metadata (internal Oracle data from the database about a collection and its element type) is collected during an ALLOCATE. That metadata becomes invalid when the connection in which the ALLOCATE was made is closed or if the type changes after the ALLOCATE.

• Use the ALLOCATE and FREE statements to begin and end use of each C collection descriptor.

Type and Table Creation

Assuming a connection as scott/tiger, we create the following types using SQL:

CREATE TYPE employee AS OBJECT 
(
  name   VARCHAR2(10),
  salary NUMBER(8,2)
) ;
CREATE TYPE employees AS VARRAY(15) OF employee ;
CREATE TYPE department AS OBJECT
(
  name VARCHAR2(15),
  team employees
) ;

Now for the header file generated by the Object Type Translator. The following intype file (called in.typ) will be used as input to OTT:

case=lower
type employee
type employees
type department

The following command will generate the header file:

ott intype=in.typ outtype=out.typ hfile=example.h user=scott/tiger code=c

This header file, example.h, is produced by OTT:

#ifndef EXAMPLE_ORACLE
# define EXAMPLE_ORACLE

#ifndef OCI_ORACLE
# include <oci.h>
#endif

typedef OCIRef employee_ref ;
typedef OCIArray employees ;
typedef OCIRef department_ref ;

struct employee
{
   OCIString * name ;
   OCINumber salary ;
} ;
typedef struct employee employee ;

struct employee_ind
{
   OCIInd _atomic ;
   OCIInd name ;
   OCIInd salary ;
} ;
typedef struct employee_ind employee_ind ;
struct department_ind
{
   OCIInd _atomic ;
   OCIInd name ;
   OCIInd team ;
} ;
typedef struct department_ind department_ind ;

#endif

Now create a simple table that has one column as follows:

CREATE TABLE division ( subdivision department ) ;

Now we will insert a few rows into that table:

INSERT INTO division (subdivision) VALUES
(department('Accounting',
            employees(employee('John', 75000),
                      employee('Jane', 75000)))
);
INSERT INTO division (subdivision) VALUES
(department('Development',
            employees(employee('Peter', 80000),
                      employee('Paula', 80000)))
) ;
INSERT INTO division (subdivision) VALUES
(department('Research',
            employees(employee('Albert', 95000),
                      employee('Alison', 95000)))
);

We can now use these type definitions and table information in the examples to follow.

GET and SET Example

Suppose we want to retrieve the values from the collection attribute of an example object, modify them in some simple way and place them back into the collection.

First, we need to include example.h and declare a variable for the object type:

#include <example.h>
department *dept_p ;

Now we will select the 'Development' department from the division table:

       EXEC SQL ALLOCATE :dept_p ;
       EXEC SQL SELECT subdivision INTO :dept_p
          FROM division WHERE name = 'Development' ;

We also need a variable for the team varray of employee object types and one to represent a single employee object. We will be giving all team members of the 'Development' department a raise so we need a variable for that also:

       employees *emp_array ;
       employee *emp_p ;
       double salary ;

Now we must ALLOCATE our varray C Collection and employee object descriptors. We will retrieve the actual collection from the object using the navigational interface:

       EXEC SQL ALLOCATE :emp_array ;
       EXEC SQL ALLOCATE :emp_p ;
       EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;

We will use a loop to iterate through the varray elements, controlling loop termination by using the WHENEVER directive:

       EXEC SQL WHENEVER NOT FOUND DO break ;
       while (TRUE)
         {

First, retrieve the element from the collection so that we can alter it. The actual element type is an employee object:

            EXEC SQL COLLECTION GET :emp_array INTO :emp_p ;

Now that we have the actual object element, we can then proceed to change an attribute's value using the existing navigational interface. In this case, we give a 10% salary increase to everyone:

            EXEC SQL OBJECT GET salary FROM :emp_p INTO :salary ;
            salary += (salary * .10) ;
            EXEC SQL OBJECT SET salary OF :emp_p TO :salary ;

Once we are done making our changes, we can update the value of the attribute of the object element we are currently at in the collection:

            EXEC SQL COLLECTION SET :emp_array TO :emp_p ;
         }

Once we are done iterating through all of the collection elements, we must then update the column of the table that stores the object that contains the collection we just finished modifying:

     EXEC SQL UPDATE division SET subdivision = :dept_p ;

We can then FREE all of our resources and COMMIT our work thus terminating this sequence of operations:

     EXEC SQL FREE :emp_array ;
     EXEC SQL FREE :emp_p ;
     EXEC SQL FREE :dept_p ;
     EXEC SQL COMMIT WORK ;

Although this is a fairly small and simple example, it is quite comprehensive. It clearly demonstrates how a collection attribute can be retrieved from an object into a C Collection Descriptor using the semantic extensions to the navigational OBJECT GET statement. Using that C Descriptor we then saw how to use the new COLLECTION GET and SET statements to retrieve and update the actual elements of that collection. We used the navigational interface to modify the attribute values of the collection object element type.

DESCRIBE Example

This example illustrates a simple use of the DESCRIBE SQL statement. We want to find out some basic information about a given collection.

First we need our example header file, an object pointer and a SQL Collection Descriptor:

#include <example.h>
department *dept_p ;

Now we ALLOCATE the object pointer and retrieve our object from the table as before:

EXEC SQL ALLOCATE :dept_p ;
EXEC SQL SELECT subdivision INTO :dept_p
   FROM division WHERE name = 'Research' ;

Declare Pro*C/C++ variables to store the collection attribute information we want:

int size ;
char type_name[9] ;
employees *emp_array ;

Allocate the collection descriptor, then use the navigational interface to get the collection attribute from the object:

EXEC SQL ALLOCATE :emp_array ;
EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;

Finally, we can use the new COLLECTION DESCRIBE statement to extract the desired collection attribute information:

EXEC SQL COLLECTION DESCRIBE :emp_array
   GET SIZE, TYPE_NAME INTO :size, :type_name ;

Because the type employees is a VARRAY of object employee, we can extract the type name.

After successful completion of the DESCRIBE, the value of size should be 2 (there are 2 elements, Albert and Alison, in this collection instance, Research). The type_name variable should read "EMPLOYEE\0" (it is a CHARZ by default).

Once we are finished with the SQL Descriptor and the object pointer we can FREE their resources:

EXEC SQL FREE :emp_array ;
EXEC SQL FREE :dept_p ;

We have just illustrated that the DESCRIBE mechanism is used to extract useful information from a C Collection Descriptor about the underlying collection to which the descriptor refers.

RESET Example

Now suppose that instead of giving just the employees in Development a raise, as in the GET and SET example, we give raises to everybody in the entire division.

We start off as before, including our example header file generated by the Object Type Translator. This time, however, we will be using a cursor to iterate through all departments in the division, one at a time:

#include <example.h>
EXEC SQL DECLARE c CURSOR FOR SELECT subdivision FROM division ;

We will need some local variables to manipulate our data:

department *dept_p ;
employees *emp_array ;
employee *emp_p ;
double salary ;
int size ;

Before we can use the object and collection variables, we must first initialize them by using this ALLOCATE statement:

EXEC SQL ALLOCATE :emp_array ;
EXEC SQL ALLOCATE :emp_p ;

Now we are ready to use our cursor to iterate through all of the departments in the division:

EXEC SQL OPEN c ;
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
    {
         EXEC SQL FETCH c INTO :dept_p ;

At this point, we have a department object. We need to use the Navigational Interface to extract the 'team' VARRAY attribute from the department:

         EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;

Before we can start referring to the collection, we need to guarantee that the slice endpoints are set to the beginning of the current collection instance (not the end of a previous instance) by use of the RESET statement:

         EXEC SQL COLLECTION RESET :emp_array ;

Now we will iterate through all elements of the varray and update the salaries as we did before. The existing WHENEVER directive remains in effect for this loop as well:

        while (TRUE)
           {
            EXEC SQL COLLECTION GET :emp_array INTO :emp_p ;
            EXEC SQL OBJECT GET salary FROM :emp_p INTO :salary ;
            salary += (salary * .05) ;
            EXEC SQL OBJECT SET salary OF :emp_p TO :salary ;

When we are finished, we'll update the collection attribute:

            EXEC SQL COLLECTION SET :emp_array TO :emp_p ;
           }

As before, we need to update the column of the table that stores the object that contains the collection that we just finished modifying:

        EXEC SQL UPDATE division SET subdivision = :dept_p ;
    }

Loop termination signifies the end of processing. We can now FREE all of our resources and COMMIT our work:

EXEC SQL CLOSE c ;
EXEC SQL FREE :emp_p ;
EXEC SQL FREE :emp_array ;
EXEC SQL FREE :dept_p ;
EXEC SQL COMMIT WORK ;

This example demonstrates how it is possible to reuse an ALLOCATEd Collection Descriptor for different instances of the same collection type. The COLLECTION RESET statement ensures the slice endpoints are set back to the beginning of the current collection instance. They do not remain in their existing positions after being moved during the referencing of a previous collection instance.

By using the COLLECTION RESET statement in this fashion, application developers need not explicitly FREE and reALLOCATE a Collection Descriptor with each new instance of the same collection type.

Example Program:coldemo1.pc

The following program, coldemo1.pc, is in the demo directory.

This example demonstrates three ways for the Pro*C client to navigate through collection-typed database columns. Although the examples presented use nested tables, they also apply to varrays.

Here is the SQL*Plus file, coldemo1.sql, that sets up the table using the inserts and data contained in calidata.sql:

REM ************************************************************************
REM ** This is a SQL*Plus script to demonstrate collection manipulation
REM ** in Pro*C/C++.
REM ** Run this script before executing OTT for the coldemo1.pc program
REM ************************************************************************

connect scott/tiger;

set serveroutput on;

REM Make sure database has no old version of the table and types

DROP TABLE county_tbl;
DROP TYPE citytbl_t;
DROP TYPE city_t;

REM ABSTRACTION:
REM The counties table contains census information about each of the
REM counties in a particular U.S. state (California is used here).  
REM Each county has a name, and a collection of cities.  
REM Each city has a name and a population.

REM IMPLEMENTATION:
REM Our implementation follows this abstraction
REM Each city is implemented as a "city" OBJECT, and the
REM collection of cities in the county is implemented using 
REM a NESTED TABLE of "city" OBJECTS.

CREATE TYPE city_t AS OBJECT (name CHAR(30), population NUMBER);
/

CREATE TYPE citytbl_t AS TABLE OF city_t;
/

CREATE TABLE county_tbl (name CHAR(30), cities citytbl_t)
  NESTED TABLE cities STORE AS citytbl_t_tbl;

REM Load the counties table with data.  This example uses estimates of
REM California demographics from Janurary 1, 1996.

@calidata.sql;

REM Commit to save
COMMIT;

See the comments at the beginning of the following program for explanations of how to set up the table, and then the functionality demonstrated by this program.

            /* ***************************************** */
            /*   Demo program for Collections in Pro*C   */
            /* ***************************************** */

/*****************************************************************************
 
   In SQL*Plus, run the SQL script coldemo1.sql to create:
     - 2 types: city_t (OBJECT) and citytbl_t (NESTED TABLE)
     - 1 relational table county_tbl which contains a citytbl_t nested table
  
   Next, run the Object Type Translator (OTT) to generate typedefs of C structs
   corresponding to the city_t and citytbl_t types in the databases:
     ott int=coldemo1.typ outt=out.typ hfile=coldemo1.h code=c user=scott/tiger
  
   Then, run the Pro*C/C++ Precompiler as follows:
     proc coldemo1 intype=out.typ
  
   Finally, link the generated code using the Pro*C Makefile:
     (Compiling and Linking applications is a platform dependent step).
  
 ****************************************************************************
 
   Scenario: 
     We consider the example of a database used to store census 
     information for the state of California.  The database has a table
     representing the counties of California.  Each county has a name 
     and a collection of cities.  Each city has a name and a population.

   Application Overview:
     This example demonstrates three ways for the Pro*C client to 
     navigate through collection-typed database columns.  Although the
     examples presented use nested tables, they also apply to varrays.
     Collections-specific functionality is demonstrated in three
     different functions, as described in the following section.
        
     PrintCounties shows examples of 
     * Declaring collection-typed host variables and arrays
     * Allocating and freeing collection-typed host variables
     * Using SQL to load a collection-typed host variable
     * Using indicators for collection-typed host variables
     * Using OCI to examine a collection-typed host variables

     PrintCounty shows examples of 
     * Binding a ref cursor host variable to a nested table column
     * Allocating and freeing a ref cursor
     * Using the SQL "CURSOR" clause

     CountyPopulation shows examples of 
     * Binding a "DECLARED" cursor to a nested table column
     * Using the SQL "THE" clause

****************************************************************************/

/* Include files */

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>                                /* SQL Communications Area */
#include <coldemo1.h>        /* OTT-generated header with C typedefs for the */
                                      /* database types city_t and citytbl_t */
#ifndef EXIT_SUCCESS
# define EXIT_SUCCESS   0
#endif
#ifndef EXIT_FAILURE
# define EXIT_FAILURE   1
#endif 

#define CITY_NAME_LEN    30
#define COUNTY_NAME_LEN  30
#define MAX_COUNTIES     60

/* Function prototypes */

#if defined(__STDC__)
 void OptionLoop( void );
 boolean GetCountyName( char *countyName );
 void PrintCounties( void );
 long CountyPopulation( CONST char *countyName );
 void PrintCounty( CONST char *countyName );
 void PrintSQLError( void );
 void PrintCountyHeader( CONST char *county );
 void PrintCity( city_t *city );
#else
 void OptionLoop();
 boolean GetCountyName(/*_ char *countyName _*/);
 void PrintCounties();
 long CountyPopulation(/*_ CONST char *countyName _*/);
 void PrintCounty(/*_ CONST char *countyName _*/);
 void PrintSQLError(/*_ void _*/);
 void PrintCountyHeader(/*_ CONST char *county _*/);
 void PrintCity(/*_ city_t *city _*/);
#endif

/* 
 * NAME
 *   main
 * COLLECTION FEATURES
 *   none
 */
int main()
{
  char * uid = "scott/tiger";
  
  EXEC SQL WHENEVER SQLERROR DO PrintSQLError();

  printf("\nPro*Census: Release California - Jan 1 1996.\n");
  EXEC SQL CONNECT :uid;
  
  OptionLoop();

  printf("\nGoodbye\n\n");
  EXEC SQL ROLLBACK RELEASE;
  return(EXIT_SUCCESS);
}

/* 
 * NAME
 *   OptionLoop
 * DESCRIPTION
 *   A command dispatch routine.
 * COLLECTION FEATURES
 *   none
 */
void OptionLoop()
{
  char choice[30];
  boolean done = FALSE;
  char countyName[COUNTY_NAME_LEN + 1];

  while (!done)
  {
    printf("\nPro*Census options:\n");
    printf("\tlist information for (A)ll counties\n");
    printf("\tlist information for one (C)ounty\n");
    printf("\tlist (P)opulation total for one county\n");
    printf("\t(Q)uit\n");
    printf("Choice? ");

    fgets(choice, 30, stdin);
    switch(toupper(choice[0]))
    {
    case 'A':
      PrintCounties();
      break;
    case 'C':
      if (GetCountyName(countyName)) 
        PrintCounty(countyName);
      break;
    case 'P':
      if (GetCountyName(countyName)) 
        printf("\nPopulation for %s county: %ld\n", 
               countyName, CountyPopulation(countyName));
      break;
    case 'Q':
      done = TRUE;
      break;
    default:
      break;
    }
  }
}


/* 
 * NAME
 *   GetCountyName
 * DESCRIPTION
 *   Fills the passed buffer with a client-supplied county name.
 *   Returns TRUE if the county is in the database, and FALSE otherwise.
 * COLLECTION FEATURES
 *   none
 */
boolean GetCountyName(countyName)
  char *countyName;
{
  int   count;
  int   i;

  printf("County name? ");
  fgets(countyName, COUNTY_NAME_LEN + 1, stdin);

  /* Convert the name to uppercase and remove the trailing '\n' */
  for (i = 0; countyName[i] != '\0'; i++)
    {
      countyName[i] = (char)toupper(countyName[i]);
      if (countyName[i] == '\n') countyName[i] = '\0';
    }

  EXEC SQL SELECT COUNT(*) INTO :count 
    FROM county_tbl WHERE name = :countyName;

  if (count != 1)
    {
      printf("\nUnable to find %s county.\n", countyName);
      return FALSE;
    }
  else
    return TRUE;
}


/* 
 * NAME
 *   PrintCounties
 * DESCRIPTION
 *   Prints the population and name of each city of every county 
 *   in the database.
 * COLLECTION FEATURES
 *   The following features correspond to the inline commented numbers
 *   1) Host variables for collection-typed objects are declared using 
 *      OTT-generated types.  Both array and scalar declarations are allowed.
 *      Scalar declarations must be of type pointer-to-collection-type, and
 *      array declarations must of type array-of-pointer-to-collection-type.
 *   2) SQL ALLOCATE should be used to allocate space for the collection.
 *      SQL FREE should be used to free the memory once the collection is
 *      no longer needed.  The host variable being allocated or free'd 
 *      can be either array or scalar.
 *   3) SQL is used to load into or store from collection-typed host variables
 *      and arrays.  No special syntax is needed.
 *   4) The type of an indicator variable for a collection is OCIInd.
 *      An indicators for a collections is declared and used just like
 *      an indicator for an int or string.
 *   5) The COLLECTION GET Interface is used to access and manipulate the
 *      contents of collection-typed host variables.  Each member of the
 *      collection used here has type city_t, as generated by OTT.
 */
void PrintCounties()
{
  citytbl_t *cityTable[MAX_COUNTIES];                                 /* 1 */
  OCIInd     cityInd[MAX_COUNTIES];                                   /* 4 */
  char       county[MAX_COUNTIES][COUNTY_NAME_LEN + 1];
  int        i, numCounties;
  city_t    *city;

  EXEC SQL ALLOCATE :cityTable;                                       /* 2 */
  EXEC SQL ALLOCATE :city;
  
  EXEC SQL SELECT name, cities
    INTO :county, :cityTable:cityInd FROM county_tbl;              /* 3, 4 */

  numCounties = sqlca.sqlerrd[2];

  for (i = 0; i < numCounties; i++) 
  {
    if (cityInd[i] == OCI_IND_NULL)                                   /* 4 */
      {
        printf("Unexpected NULL city table for %s county\n", county[i]);
      }
    else
      {                                                               /* 5 */
        PrintCountyHeader(county[i]);
        EXEC SQL WHENEVER NOT FOUND DO break;
        while (TRUE)
          {
            EXEC SQL COLLECTION GET :cityTable[i] INTO :city;
            PrintCity(city);
          }
        EXEC SQL WHENEVER NOT FOUND CONTINUE;
      }
  }

  EXEC SQL FREE :city;
  EXEC SQL FREE :cityTable;                                           /* 2 */
}


/* 
 * NAME
 *   PrintCountyHeader
 * COLLECTION FEATURES
 *   none
 */
void PrintCountyHeader(county)
  CONST char *county;
{
  printf("\nCOUNTY: %s\n", county);
}

/* 
 * NAME
 *   PrintCity
 * COLLECTION FEATURES
 *   none
 */
void PrintCity(city)
  city_t *city;
{
  varchar newCITY[CITY_NAME_LEN];
  int newPOP;

  EXEC SQL OBJECT GET NAME, POPULATION from :city INTO :newCITY, :newPOP;
  printf("CITY: %.*s POP: %d\n", CITY_NAME_LEN, newCITY.arr, newPOP);
}

/* 
 * NAME
 *   PrintCounty
 * DESCRIPTION
 *   Prints the population and name of each city in a particular county.
 * COLLECTION FEATURES
 *   The following features correspond to the inline commented numbers
 *   1) A ref cursor host variable may be used to scroll through the 
 *      rows of a collection. 
 *   2) Use SQL ALLOCATE/FREE to create and destroy the ref cursor.
 *   3) The "CURSOR" clause in SQL can be used to load a ref cursor 
 *      host variable.  In such a case, the SELECT ... INTO does an
 *      implicit "OPEN" of the ref cursor.
 * IMPLEMENTATION NOTES 
 *   In the case of SQL SELECT statements which contain an embedded 
 *   CURSOR(...) clause, the Pro*C "select_error" flag must be "no"
 *   to prevent cancellation of the parent cursor.
 */
void PrintCounty(countyName)
  CONST char *countyName;
{
  sql_cursor cityCursor;                                              /* 1 */
  city_t *city;

  EXEC SQL ALLOCATE :cityCursor;                                      /* 2 */
  EXEC SQL ALLOCATE :city;                                        
  
  EXEC ORACLE OPTION(select_error=no);
  EXEC SQL SELECT 
    CURSOR(SELECT VALUE(c) FROM TABLE(county_tbl.cities) c)
      INTO :cityCursor 
      FROM county_tbl
      WHERE county_tbl.name = :countyName;                            /* 3 */
  EXEC ORACLE OPTION(select_error=yes);
  
  PrintCountyHeader(countyName);

  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      EXEC SQL FETCH :cityCursor INTO :city;
      PrintCity(city);
    }
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

  EXEC SQL CLOSE :cityCursor;

  EXEC SQL FREE :cityCursor;                                         /* 2 */
  EXEC SQL FREE :city; 
}


/* 
 * NAME
 *   CountyPopulation
 * DESCRIPTION
 *   Returns the number of people living in a particular county.
 * COLLECTION FEATURES
 *   The following features correspond to the inline commented numbers
 *   1) A "DECLARED" cursor may be used to scroll through the 
 *      rows of a collection. 
 *   2) The "THE" clause in SQL is used to convert a single nested-table
 *      column into a table.
 */
long CountyPopulation(countyName)
  CONST char *countyName;
{
  long population;
  long populationTotal = 0;

  EXEC SQL DECLARE cityCursor CURSOR FOR
    SELECT c.population 
    FROM THE(SELECT cities FROM county_tbl 
              WHERE name = :countyName) AS c;                     /* 1, 2 */

  EXEC SQL OPEN cityCursor;

  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      EXEC SQL FETCH cityCursor INTO :population;
      populationTotal += population;
    }
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

  EXEC SQL CLOSE cityCursor;
  return populationTotal;
}


/* 
 * NAME
 *   PrintSQLError
 * DESCRIPTION
 *   Prints an error message using info in sqlca and calls exit.
 * COLLECTION FEATURES
 *   none
 */ 
void PrintSQLError()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("SQL error occurred...\n");
  printf("%.*s\n", (int)sqlca.sqlerrm.sqlerrml,
         (CONST char *)sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK RELEASE;
  exit(EXIT_FAILURE);
}