The following example shows how to use the INTEGRATE statement
with the ALTER FILES clause. In this example, fields (domains)
are defined in the repository. Then, using SQL, a table is
created based on the repository definitions. Subsequently, the
repository definitions are changed so the definitions in the
database file and the repository no longer match. The INTEGRATE
statement resolves this situation by altering the database
definitions using the repository definitions as the source.
$ !
$ ! Define CDD$DEFAULT
$ !
$ DEFINE CDD$DEFAULT SYS$COMMON:[REPOSITORY]CATALOG
$ !
$ ! Enter the CDO to create new field and record definitions:
$ !
$ REPOSITORY
CDO> !
CDO> ! Create two field (domain) definitions in the repository:
CDO> !
CDO> DEFINE FIELD PART_NUMBER DATATYPE IS WORD.
CDO> DEFINE FIELD PRICE DATATYPE IS WORD.
CDO> !
CDO> ! Define a record called INVENTORY using the two
CDO> ! fields previously defined:
CDO> !
CDO> DEFINE RECORD INVENTORY.
CDO> PART_NUMBER.
CDO> PRICE.
CDO> END RECORD INVENTORY.
CDO> !
CDO> EXIT
$ !
$ ! Enter SQL:
$ !
$ SQL
SQL> !
SQL> ! In SQL, create the database ORDERS:
SQL> !
SQL> CREATE DATABASE ALIAS ORDERS PATHNAME ORDERS;
SQL> !
SQL> ! Create a table in the database ORDERS using the
SQL> ! INVENTORY record (table) just created in the repository:
SQL> !
SQL> CREATE TABLE FROM SYS$COMMON:[REPOSITORY]CATALOG.INVENTORY
cont> ALIAS ORDERS;
SQL> !
SQL> ! Use the SHOW TABLE statement to see information about
SQL> ! INVENTORY the table:
SQL> !
SQL> SHOW TABLE ORDERS.INVENTORY
Information for table ORDERS.INVENTORY
CDD Pathname: SYS$COMMON:[REPOSITORY]CATALOG.INVENTORY;1
Columns for table ORDERS.INVENTORY:
Column Name Data Type Domain
----------- --------- ------
PART_NUMBER SMALLINT ORDERS.PART_NUMBER
PRICE SMALLINT ORDERS.PRICE
.
.
.
SQL> COMMIT;
SQL> EXIT
$ !
$ ! Enter CDO again:
$ !
$ REPOSITORY
CDO> !
CDO> ! Verify that the fields PART_NUMBER and PRICE are
cdo> ! in the record INVENTORY:
CDO> !
CDO> SHOW RECORD INVENTORY
Definition of record INVENTORY
| Contains field PART_NUMBER
| Contains field PRICE
CDO> !
CDO> ! Define the fields VENDOR_NAME and QUANTITY. Add them to
CDO> ! the record INVENTORY using the CDO CHANGE RECORD command. Now, the
CDO> ! definitions used by the database no longer match the definitions
CDO> ! in the respository, as the CDO message indicates:
CDO> !
CDO> DEFINE FIELD VENDOR_NAME DATATYPE IS TEXT 20.
CDO> DEFINE FIELD QUANTITY DATATYPE IS WORD.
CDO> !
CDO> CHANGE RECORD INVENTORY.
CDO> DEFINE VENDOR_NAME.
CDO> END.
CDO> DEFINE QUANTITY.
CDO> END.
CDO> END INVENTORY RECORD.
%CDO-I-DBMBR, database SQL_USER:[PRODUCTION]CATALOG.ORDERS(1) may need
to be INTEGRATED
CDO> !
CDO> ! Use the SHOW RECORD command to see if the fields VENDOR_NAME
CDO> ! and QUANTITY are part of the INVENTORY record:
CDO> !
CDO> SHOW RECORD INVENTORY
Definition of record INVENTORY
| Contains field PART_NUMBER
| Contains field PRICE
| Contains field VENDOR_NAME
| Contains field QUANTITY
CDO> !
CDO> EXIT
$ !
$ ! Enter SQL again:
$ !
$ SQL
SQL> !
SQL> ! Use the INTEGRATE . . . ALTER FILES statement to update
SQL> ! the definitions in the database file, using the repository definitions
SQL> ! as the source. Note the INTEGRATE statement implicitly attaches to
SQL> ! the database.
SQL> !
SQL> INTEGRATE DATABASE PATHNAME SYS$COMMON:[REPOSITORY]CATALOG.ORDERS
cont> ALTER FILES;
SQL> !
SQL> ! Use the SHOW TABLE statement to see if the table INVENTORY has
SQL> ! changed. SQL has added the VENDOR_NAME and QUANTITY domains
SQL> ! to the database file:
SQL> !
SQL> SHOW TABLE INVENTORY
Information for table INVENTORY
CDD Pathname: SYS$COMMON:[REPOSITORY]CATALOG.INVENTORY;1
Columns for table INVENTORY:
Column Name Data Type Domain
----------- --------- ------
PART_NUMBER SMALLINT PART_NUMBER
PRICE SMALLINT PRICE
VENDOR_NAME CHAR(20) VENDOR_NAME
QUANTITY SMALLINT QUANTITY
.
.
.
SQL> COMMIT;
SQL> EXIT
The following example shows how to update the repository using
the database files as the source by issuing the INTEGRATE
statement with the ALTER DICTIONARY clause. The example starts
with the definitions in the repository matching the definitions
in the database file. There is a table in the database and a
record in the repository, both called CUSTOMER_ORDERS. The
CUSTOMER_ORDERS table has four columns based on four domains
of the same name: FIRST_ORDER, SECOND_ORDER, THIRD_ORDER, and
FOURTH_ORDER.
This example adds to the database file a domain called FIFTH_DOM,
on which the local column called FIFTH_ORDER is based. At this
point, the database file and the repository definitions no longer
match. The INTEGRATE . . . ALTER DICTIONARY statement resolves
this situation by altering the repository using the database file
definitions as the source.
SQL> ! Create the database using the PATHNAME clause:
SQL> !
SQL> CREATE DATABASE FILENAME TEST1
cont> PATHNAME SYS$COMMON:[REPOSITORY]TEST1;
SQL> !
SQL> ! Create domains for the TEST1 database:
SQL> !
SQL> CREATE DOMAIN FIRST_ORDER CHAR(4);
SQL> CREATE DOMAIN SECOND_ORDER CHAR(4);
SQL> CREATE DOMAIN THIRD_ORDER CHAR(4);
SQL> CREATE DOMAIN FOURTH_ORDER CHAR(4);
SQL> CREATE TABLE CUSTOMER_ORDERS
cont> (FIRST_ORDER FIRST_ORDER,
cont> SECOND_ORDER SECOND_ORDER,
cont> THIRD_ORDER THIRD_ORDER,
cont> FOURTH_ORDER FOURTH_ORDER);
SQL> COMMIT;
SQL> DISCONNECT DEFAULT;
SQL> !
SQL> ! Attach to the database with the FILENAME clause so the
SQL> ! repository is not updated:
SQL> !
SQL> ATTACH 'ALIAS TEST1 FILENAME TEST1';
SQL> !
SQL> ! Use the SHOW TABLE statement to see what columns and domains
SQL> ! are part of the table CUSTOMER_ORDERS:
SQL> !
SQL> SHOW TABLE (COLUMNS) TEST1.CUSTOMER_ORDERS;
Information on table TEST1.CUSTOMER_ORDERS
Columns for table TEST1.CUSTOMER_ORDERS:
Column Name Data Type Domain
----------- --------- ------
FIRST_ORDER CHAR(4) FIRST_ORDER
SECOND_ORDER CHAR(4) SECOND_ORDER
THIRD_ORDER CHAR(4) THIRD_ORDER
FOURTH_ORDER CHAR(4) FOURTH_ORDER
SQL> !
SQL> ! Create a new domain called FIFTH_DOM. Add a new
SQL> ! column to the CUSTOMER_ORDERS table called FIFTH_ORDER
SQL> ! and base it on the domain FIFTH_DOM:
SQL> !
SQL> CREATE DOMAIN TEST1.FIFTH_DOM CHAR(4);
SQL> ALTER TABLE TEST1.CUSTOMER_ORDERS ADD FIFTH_ORDER TEST1.FIFTH_DOM;
SQL> !
SQL> ! Check the CUSTOMER_ORDERS table to verify that the column FIFTH_ORDER
SQL> ! was created:
SQL> !
SQL> SHOW TABLE (COLUMNS) TEST1.CUSTOMER_ORDERS;
Information on table TEST1.CUSTOMER_ORDERS
Column Name Data Type Domain
----------- --------- ------
FIRST_ORDER CHAR(4) TEST1.FIRST_ORDER
SECOND_ORDER CHAR(4) TEST1.SECOND_ORDER
THIRD_ORDER CHAR(4) TEST1.THIRD_ORDER
FOURTH_ORDER CHAR(4) TEST1.FOURTH_ORDER
FIFTH_ORDER CHAR(4) TEST1.FIFTH_DOM
SQL> COMMIT;
SQL> EXIT
$ !
$ ! Invoke CDO:
$ !
$ REPOSITORY
CDO> !
CDO> ! Note that only the database definition for TEST1 appears in the
CDO> ! repository directory:
CDO> !
DIRECTORY
Directory SYS$COMMON:[REPOSITORY]
TEST1(1) CDD$DATABASE
CDO> !
CDO> ! Check the record CUSTOMER_ORDERS. The field FIFTH_ORDER is not part of
CDO> ! the record CUSTOMER_ORDERS. This means that the definitions in the
CDO> ! database file do not match the definitions in the repository.
CDO> !
CDO> !
CDO> SHOW RECORD CUSTOMER_ORDERS FROM DATABASE TEST1
Definition of the record CUSTOMER_ORDERS
| Contains field FIRST_ORDER
| Contains field SECOND_ORDER
| Contains field THIRD_ORDER
| Contains field FOURTH_ORDER
CDO> EXIT
$ !
$ ! Enter SQL again:
$ !
$ SQL
SQL> !
SQL> ! To make the definitions in the repository match those in the database
SQL> ! file, use the INTEGRATE statement with the ALTER DICTIONARY clause.
SQL> ! Note that the INTEGRATE statement implicitly attaches to the
SQL> ! database.
SQL> !
SQL> INTEGRATE DATABASE PATHNAME TEST1 ALTER DICTIONARY;
SQL> COMMIT;
SQL> EXIT
$ !
$ ! Enter CDO again:
$ !
$ REPOSITORY
CDO> !
CDO> ! Use the SHOW RECORD command to verify that the field FIFTH_ORDER is now
CDO> ! part of the record CUSTOMER_ORDERS. Now, the definitions in both the
CDO> ! repository and the database file are the same.
CDO> !
CDO> SHOW RECORD CUSTOMER_ORDERS FROM DATABASE TEST1
Definition of record CUSTOMER_ORDERS
| Contains field FIRST_ORDER
| Contains field SECOND_ORDER
| Contains field THIRD_ORDER
| Contains field FOURTH_ORDER
| Contains field FIFTH_ORDER
CDO> !
CDO> ! Use the ENTER command to make the record (table) CUSTOMER_ORDERS and
CDO> ! its fields (domains) appear in the repository. The ENTER command
CDO> ! assigns a repository directory name to an element.
CDO> !
CDO> ENTER FIELD FIRST_ORDER FROM DATABASE TEST1
CDO> !
CDO> ! Verify that a repository path name was assigned to the field
CDO> ! FIRST_ORDER:
CDO> !
CDO> DIRECTORY
Directory SYS$COMMON:[REPOSITORY]
FIRST_ORDER(1) FIELD
TEST1(1) CDD$DATABASE
CDO> ENTER FIELD SECOND_ORDER FROM DATABASE TEST1
.
.
.
CDO> ENTER FIELD FIFTH_DOM FROM DATABASE TEST1
CDO> !
CDO> ! Now all the domains and tables in TEST1 have been assigned a
CDO> ! repository directory name:
CDO> DIRECTORY
Directory SYS$COMMON:[REPOSITORY]
CUSTOMER_ORDERS(1) RECORD
FIFTH_DOM(1) FIELD
FIRST_ORDER(1) FIELD
FOURTH_ORDER(1) FIELD
SECOND_ORDER(1) FIELD
TEST1(1) CDD$DATABASE
THIRD_ORDER(1) FIELD
To store existing database file definitions in the repository
for the first time, use the INTEGRATE statement with the CREATE
PATHNAME clause. This statement builds repository definitions
using the database file as the source.
The following example shows how to store existing database system
file definitions in the repository for the first time. This
example first creates a database only in a database file, not
in the repository. Next, the INTEGRATE statement with the CREATE
PATHNAME clause updates the repository with the data definitions
from the database system file.
SQL> !
SQL> ! Create a database without requiring the repository (the default)
SQL> ! or specifying a path name:
SQL> !
SQL> CREATE DATABASE ALIAS DOGS;
SQL> !
SQL> ! Now create a table for the breed of dog, poodles. The
SQL> ! columns in the table are types of poodles:
SQL> !
SQL> CREATE TABLE DOGS.POODLES
cont> ( STANDARD CHAR(10),
cont> MINIATURE CHAR(10),
cont> TOY CHAR(10) );
SQL> !
SQL> ! Use the SHOW TABLE statement to see the table POODLES:
SQL> !
SQL> SHOW TABLE (COLUMNS) DOGS.POODLES
Information on table DOGS.POODLES
Columns for table DOGS.POODLES:
Column Name Data Type Domain
----------- --------- ------
STANDARD CHAR(10)
MINIATURE CHAR(10)
TOY CHAR(10)
SQL> COMMIT;
SQL> EXIT
$ !
$ ! Enter CDO:
$ !
$ REPOSITORY
CDO> !
CDO> ! Use the DIRECTORY command to check if the database definition DOGS is
CDO> ! in the repository:
CDO> !
CDO> DIRECTORY
Directory SYS$COMMON:[REPOSITORY]
%CDO-E-NOTFOUND, entity not found in dictionary
CDO> !
CDO> ! DOGS is not in the repository.
CDO> !
CDO> EXIT
$ !
$ ! Enter SQL again:
$ !
$ SQL
SQL> !
SQL> ! Use the INTEGRATE statement using the CREATE PATHNAME clause to
SQL> ! update the repository using the DOGS database file:
SQL> !
SQL> INTEGRATE DATABASE FILENAME SQL_USER:[PRODUCTION.ANIMALS]DOGS
cont> CREATE PATHNAME SYS$COMMON:[REPOSITORY]DOGS;
SQL> COMMIT;
SQL> EXIT
$ !
$ ! Enter CDO again:
$ !
$ REPOSITORY
CDO> !
CDO> ! Use the DIRECTORY command to check if the database definition DOGS
CDO> ! has been integrated into the repository:
CDO> !
CDO> DIRECTORY
Directory SYS$COMMON:[REPOSITORY]
DOGS(1) CDD$DATABASE
CDO> !
CDO> ! You can also use the SHOW USED_BY command to see
CDO> ! if the record (table) POODLES and the fields (columns)
CDO> ! STANDARD, MINIATURE, and TOY are part of the database
CDO> ! definition DOGS.
CDO> !
CDO> SHOW USED_BY/FULL DOGS
Members of SYS$COMMON:[REPOSITORY]DOGS(1)
| DOGS (Type : CDD$RDB_DATABASE)
| | via CDD$DATABASE_SCHEMA
.
.
.
| SYS$COMMON:[REPOSITORY]CDD$RDB_SYSTEM_METADATA.RDB$CDD_NAME;1(Type : FIELD)
| | | | via CDD$DATA_AGGREGATE_CONTAINS
| | POODLES (Type : RECORD)
| | | via CDD$RDB_DATA_AGGREGATE
| | | STANDARD (Type : FIELD)
| | | | via CDD$DATA_AGGREGATE_CONTAINS
| | | | SQL$10CHR (Type : FIELD)
| | | | | via CDD$DATA_ELEMENT_BASED_ON
| | | MINIATURE (Type : FIELD)
| | | | via CDD$DATA_AGGREGATE_CONTAINS
| | | | SQL$10CHR (Type : FIELD)
| | | | | via CDD$DATA_ELEMENT_BASED_ON
| | | TOY (Type : FIELD)
| | | | via CDD$DATA_AGGREGATE_CONTAINS
| | | | SQL$10CHR (Type : FIELD)
| | | | | via CDD$DATA_ELEMENT_BASED_ON
.
.
.
CDO> EXIT
The following example shows how to update a repository field
using the database files as the source by issuing the INTEGRATE
DOMAIN statement with the ALTER DICTIONARY clause. The example
starts with the definitions in the repository matching the
definitions in the database file. There is a domain in the
database and a field in the repository, both called DOMTEST.
This example alters the domain in the database file name TESTDB.
At this point, the database file and the repository definitions
no longer match. The INTEGRATE DOMAIN . . . ALTER DICTIONARY
statement resolves this situation by altering the repository
using the database file definitions as the source.
SQL> -- Create a database, domain, and table.
SQL> --
SQL> CREATE DATABASE FILENAME TESTDB PATHNAME TESTDB;
SQL> CREATE COLLATING SEQUENCE FRENCH FRENCH;
SQL> CREATE DOMAIN DOMTEST
cont> CHAR(5)
cont> COLLATING SEQUENCE IS FRENCH;
SQL> CREATE DOMAIN TEST_DOM_1
cont> CHAR(1);
SQL> CREATE TABLE TEMP_TAB
cont> (ROW1 CHAR(5),
cont> ROW2 DOMTEST,
cont> ROW3 TEST_DOM_1,
cont> ROW4 INT);
SQL> COMMIT;
SQL> SHOW DOMAIN DOMTEST
DOMTEST CHAR(5)
Collating sequence: FRENCH
SQL> --
SQL> -- Disconnect from the database and invoke Oracle CDD/Repository
SQL> -- user interface and show the field DOMTEST from the TESTDB
SQL> -- database.
SQL> --
SQL> DISCONNECT ALL;
SQL> EXIT
$ CDO
CDO> SHOW FIELD DOMTEST FROM DATABASE TESTDB
Definition of field DOMTEST
| Datatype text size is 5 characters
| Collating sequence 'FRENCH'
CDO> !
CDO> ! Exit from Oracle CDD/Repository and attach to the database by file name
CDO> ! only.
CDO> !
CDO> EXIT
SQL> ATTACH 'FILENAME TESTDB';
SQL> --
SQL> -- Alter the domain DOMTEST.
SQL> --
SQL> ALTER DOMAIN DOMTEST
cont> CHAR(10)
cont> COLLATING SEQUENCE IS FRENCH;
SQL> COMMIT;
SQL> SHOW DOMAIN DOMTEST
DOMTEST CHAR(10)
Collating sequence: FRENCH
SQL> --
SQL> -- Disconnect from the database and attach by path name only to issue
SQL> -- the INTEGRATE DOMAIN statement.
SQL> --
SQL> DISCONNECT ALL;
SQL> ATTACH 'PATHNAME TESTDB';
SQL> INTEGRATE DOMAIN DOMTEST ALTER DICTIONARY;
SQL> COMMIT;
SQL> --
SQL> -- Disconnect from the database and invoke Oracle CDD/Repository V6.1
SQL> -- user interface and show the altered field DOMTEST from the TESTDB
SQL> -- database.
SQL> --
SQL> DISCONNECT ALL;
SQL> EXIT
$ CDO
CDO> SHOW FIELD DOMTEST FROM DATABASE TESTDB
Definition of field DOMTEST
| Datatype text size is 10 characters
| Collating sequence 'FRENCH'
| Generic CDD$DATA_ELEMENT_CHARSET is '0'