REM ************************************************************************
REM ** This is a SQL*Plus script to demonstrate collection manipulation
REM ** in Pro*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 tables 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;

EXIT;
