Makes definitions in a database and in a repository correspond by
changing definitions in either the database or the repository.
The INTEGRATE statement can also create database definitions in
the repository by copying from a database file to a specified
repository.
1 – Environment
You can issue the INTEGRATE statement only in interactive SQL.
2 – Format
INTEGRATE ---+
+------------+
++-> DATABASE --+
| +------------+
| ++--> FILENAME -> <file-name> -> CREATE PATHNAME -> <path-name-2> +->
| +--> PATHNAME -> <path-name-1> +-+-> ALTER FILES ------+---------+
| | +-> ALTER DICTIONARY -+
+-> DOMAIN --> <domain-name> -----+
+-> TABLE ---> <table-name> ------+
domain-name =
-+---------------------------+-> <name-of-domain> --->
+-+-> <schema-name> -+-> . -+
+-> <alias> -------+
table-name =
-+-------------------------+-> <name-of-table> +->
+-+-> <schema-name> +> . -+ |
| +-> <alias> ------+ |
+---> " <alias.name-of-table> " --------------+
3 – Arguments
3.1 – DATABASE_FILENAME_CREATE_PATHNAME
Stores existing database system file definitions in the
repository for the first time. Use the INTEGRATE DATABASE
FILENAME clause if you did not specify PATHNAME or the repository
was not installed when you created the database.
If you use the INTEGRATE DATABASE FILENAME clause, the repository
database node specified in the path name must not exist. If
older repository definitions do exist with the path name you
are specifying, specify a different repository path name, placing
the new database definitions elsewhere.
The file-name clause is the full or partial file specification
that specifies the source of the database definitions. You do
not need to specify the file extension. The database system
automatically uses the database root file ending with the .rdb
file extension.
Path-name-2 is the repository path name for the repository where
the INTEGRATE statement creates the database definitions (using
the database system files as the source). You can specify either
a full repository path name or a relative repository path name.
This must be the path name, not the name of the database itself.
3.2 – DATABASE_PATHNAME_ALTER_FILES
Alters any table and domain definitions created with the CREATE
TABLE FROM statement or the CREATE DOMAIN FROM statement so they
match their sources in the repository. The INTEGRATE . . . ALTER
FILES statement has no effect on definitions not created with the
FROM clause. This is useful if the database file definitions no
longer match the definitions in the repository.
Path-name-1 is the repository path name for the repository
database that is the source for altering the definitions in the
database. You can specify either a full repository path name or a
relative repository path name.
CAUTION
Using the ALTER FILES clause may destroy data associated
with definitions in your database file if those definitions
are not defined in your repository. In this situation, you
will lose real data. For this reason, use the ALTER FILES
clause with caution.
3.3 – DATABASE_PATHNAME_ALTER_DICTIONARY
Alters the database definitions in the dictionary so they are
the same as those in the database. This is useful if repository
definitions no longer match the definitions in the database
file. Note, though, that altering database definitions in the
repository may affect other applications that refer to these
definitions.
The repository must already exist and may contain definitions.
Path-name-1 is the repository path name for the repository
database that SQL alters using the definitions in the database
file as a source. You can specify either a full repository path
name or a relative path name.
3.4 – DOMAIN_ALTER_FILES
Alters the domain definitions in the database to match the field
definitions in the repository. Collating sequences referenced
by the domain and columns that are based on the domain and the
tables that contain them may also be altered if they have changed
in the repository.
3.5 – DOMAIN_ALTER_DICTIONARY
Alters the field definitions in the repository to match the
domain definitions in the database. Collating sequences
referenced by the domain and columns that are based on the domain
and the tables that contain them may also be altered if they have
changed in the database.
3.6 – TABLE_ALTER_FILES
Alters the table definitions in the database to match the record
definitions in the repository. Other objects referencing the
table or that are referenced by it and have changed definition in
the repository may be altered. These other objects are:
o Domains
o Collating sequences
o Other referenced tables and columns
o Foreign key constraints and check constraints
o Indexes
o Views that reference the table
o Storage maps and storage areas referenced by an index
3.7 – TABLE_ALTER_DICTIONARY
Alters the record definitions in the repository to match the
table definitions in the database. Other objects referencing the
table or that are referenced by it and have changed definitions
in the database may be altered. These other objects are:
o Fields
o Collating sequences
o Other referenced records and fields
o Foreign key constraints and check constraints
o Indexes
4 – Examples
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'