3 OCDS Schemas

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

Prerequisites

1. Oracle Database 19c (Release 19.3.0.0.0) 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:

   
   

   Note: For information on the ORDS version recommended for Oracle Database 19c, see the Oracle REST Data Services documentation on the Oracle Help Center (docs.oracle.com) at: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/ ORDS should be installed AND validated prior to any OCDS installation.

   
   

   • 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 interface ocds_ifc schema and populate it. The interface schema needs to be complete prior to proceeding with step 2.

   
   

   Note: Starting with patch release 19.1.002 you only need to run the <dbScripts>/scripts/rtg_ifc/ddl/bdi_ocds_ddl.sql script. You do not need to run the <dbScripts>/scripts/rtg_ifc/ddl/BDI_PUBLIC_INTERFACE_TABLE_CREATE.sql script.

   
   

   1. <dbScripts>/scripts/rtg_ifc/ddl/BDI_PUBLIC_INTERFACE_TABLE_CREATE.sql

   2. <dbScripts>/scripts/rtg_ifc/ddl/bdi_ocds_ddl.sql

   See the Oracle Retail Bulk Data Integration Cloud Service Installation Guide, for more information on how to create the interface schema or to troubleshoot issues.

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/SecureRest.sql