3 OCDS Schemas

This chapter describes the instructions for building the OCDS schemas on an Oracle 12c Pluggable Database (PDB).

Prerequisites

1. Oracle Database 12c (Release 12.1.0.2) has been installed.

2. Container Database (CDB) has been created.

3. Pluggable Database (PDB) for OCDS schema has been created.

4. Configured ORDS 19.2 for the OCDS database:

   • Set the location of the ORDS configuration files

     java -jar ords.war configdir </path/to/ords/config>

   • Configure database connection to the OCDS database

     java -jar ords.war setup --database <database name>

   • Configure the request routing rule for OCDS services

     java -jar ords.war map-url --type base-path <path prefix> <database name>

5. The two OCDS database users have been created with the following names and empty schemas:

   • ocds_ifc

   • ocds_txn

Preparation

Perform the following procedure to prepare for these schema creation of the OCDS database. This archive file contains scripts to populate the two OCDS schemas, enable and secure the OCDS REST services.

• Unzip ocds-database-creation.zip. The location where the files were extracted will be referenced as <dbScripts> in the following steps.

Database Schema Population

Perform the following steps to populate the OCDS schemas.

1. Connect to the ocds_ifc schema and execute the following scripts:

   • <dbScripts>/scripts/rtg_ifc/ddl/BDI_BATCH_JOB_INFRA_CREATE.sql

   • <dbScripts>/scripts/rtg_ifc/ddl/BDI_RECEIVER_INFRA_CREATE.sql

   • <dbScripts>/scripts/rtg_ifc/ddl/ocds_ddl.sql

2. Connect to the ocds_ifc schema as a user with permissions to grant access to tables in the ocds_ifc schema and execute the following scripts:

   • <dbScripts>/ocds_txn/plsql/Interface_Schema_Access.sql

3. Connect to the ocds_txn schema and execute the following scripts:

   • <dbScripts>/scripts/ocds_txn/ddl/ocds-txn-ddl.sql

   • <dbScripts>/scripts/ocds_txn/plsql/ocds-txn-plsql.sql

4. Connect to the ocds_txn schema as a user with permissions to grant access to packages on the ocds_txn schema and execute the following scripts:

   • <dbScripts>/scripts/ocds_ifc/plsql/Transaction_Schema_Access.sql

5. Connect to the ocds_ifc schema and execute the following scripts:

   • <dbScripts>/scripts/ocds_ifc/plsql/ocds-ifc-plsql.sql

Enable REST Services on OCDS Database

Perform the following procedure to enable the OCDS web services on the ocds_txn schema.

1. Connect to the ocds_txn schema and execute the following script:

   • <dbScriptRoot>/scripts/ocds_txn/rest/ocds-enable-rest.sql

Secure OCDS Web Services on OCDS Database

Perform the following procedure to secure the OCDS web services on the ocds_txn schema.

1. Connect to the ocds_txn schema and execute the following script:

   • <dbScriptRoot>/scripts/ocds_txn/rest/ocds-secure-rest.sql