[Oracle 26ai] Complete Guide to Introducing the Next-Generation Standard “CO (Customer Orders)” Sample Schema

Hello, Oracle Engineers! Are you enjoying your 26ai life? We have entered the Oracle Database 26ai (23ai) era, and the way we write SQL and the design philosophy of applications have changed significantly. Isn’t it about time to update the old habit of “just SELECT * FROM EMP for the time being”?

The new playground to fully utilize modern Oracle (JSON, Microservices, AI Vector Search) is the CO (Customer Orders) schema. In this article, I will explain the procedure to install this CO schema into an Oracle AI Database 26ai Free environment, along with actual logs. It is not just “check the box in DBCA and done” like before, so please refer to this guide.

Why CO Schema Now?
Installation Practice Log
Post-Installation Check
Practical Application: Trying 26ai’s Key Feature “JSON Relational Duality View”
Frequently Asked Questions (FAQ)
Summary

Why CO Schema Now?

The traditional HR (Human Resources) schema was optimal for learning relational data (joins and aggregations). However, it does not contain the following elements required in modern app development:

JSON documents
E-commerce data models (Stores, Inventory, Orders)
Text data for vector search

The CO schema was designed to cover all of these and specifically to test JSON Relational Duality Views. If you are touching 26ai, you cannot start without this.

Installation Practice Log

Now, let’s look at the procedure step-by-step when I actually installed it in my verification environment (Oracle AI Database 26ai Free Release 23.26.0.0.0).

0. Prerequisites and Preparation

Sample schemas are no longer included in the DB media. First, obtain them via the following procedure and deploy them on the server.

Access the Official Repository (Releases) on GitHub.
Click the latest version of Source code (zip) to download it (e.g., filename: db-sample-schemas-23.3.zip).
Place the downloaded file in a working directory on the server (e.g., /home/oracle/).
Unzip it using the following command:

cd /home/oracle/
unzip db-sample-schemas-23.3.zip

When unzipped, a folder named db-sample-schemas-23.3 is created. We will use the customer_orders folder inside it.

1. Connecting to the Database and Switching PDBs

Once unzipped, first log in with administrator privileges. The biggest point of caution here is “Switching to the container (PDB).” It is a golden rule to install sample schemas in a Pluggable Database (PDB), not in CDB$ROOT. First, move to the unzipped directory and then start SQL*Plus.

[oracle@26ai-single ~]$ cd /home/oracle/db-sample-schemas-23.3/customer_orders
[oracle@26ai-single customer_orders]$ sqlplus / as sysdba

SQL*Plus: Release 23.26.0.0.0 - Production on Sat Jan 3 02:16:34 2026
Version 23.26.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0

Check the PDBs and switch. Since this is the Free version, FREEPDB1 is the target.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

SQL> alter session set container=freepdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
FREEPDB1

2. Execution of Installation Script

If you started SQL*Plus from the target directory (where co_install.sql is located), simply run the execution command.

SQL> @co_install.sql

When the script starts running, you will be asked a few questions in an interactive format.

Password: Decide the password for the CO user.
Tablespace: Press Enter if the default is fine (usually goes into USERS).
Overwrite Confirmation: If the CO schema already exists, you will be asked whether to recreate it.

Here is the actual log:

Thank you for installing the Oracle Customer Orders Sample Schema.
...(snip)...
Enter a password for the user CO: [Enter Password]

Enter a tablespace for CO [USERS]: [Enter]
Do you want to overwrite the schema, if it already exists? [YES|no]: YES

From here, table creation and data loading will proceed.

****** Creating CUSTOMERS table ....
Table created.
****** Creating STORES table ....
Table created.
...(snip)...
Table altered.

3. Verification

At the end of the script, the check results of the inserted data counts are displayed. If “actual” and “provided” match, it is a success.

Verification:

Table         provided     actual
----------- ---------- ----------
customers          392        392
stores              23         23
products            46         46
orders            1950       1950
shipments         1892       1892
order_items       3914       3914
inventory          566        566

Thank you!
The installation of the sample schema is now finished.

Completed successfully! You will be automatically disconnected.

Post-Installation Check

Just to be safe, let’s confirm if we can log in with the created CO user and if the tables are visible.

[oracle@26ai-single customer_orders]$ sqlplus co/co@26ai-single:1521/freepdb1

Note: Please change the connection string according to your environment. Here, Easy Connect naming is used.

SQL*Plus: Release 23.26.0.0.0 - Production on Sat Jan 3 02:19:45 2026
...
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free

SQL> show con_name
CON_NAME
------------------------------
FREEPDB1

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
STORES
PRODUCTS
ORDERS
SHIPMENTS
ORDER_ITEMS
INVENTORY

7 rows selected.

Perfect. 7 tables have been created.

Practical Application: Trying 26ai’s Key Feature “JSON Relational Duality View”

Installing is not the end. This is where the real work with 26ai begins. The greatest feature of the CO schema is that “Normalized relational tables can be treated as a single JSON document from the application.”

What is JSON Relational Duality View in the first place?

Usually, app developers face the following dilemmas when handling “Order Data”:

The RDB (SQL) World: You need to JOIN the ORDERS table and ORDER_ITEMS table to fetch data. You also have to INSERT them separately when saving. It is tedious.
The NoSQL (JSON) World: You can save it all in one JSON file. It is easy, but maintaining data integrity (consistency with inventory master, etc.) is difficult.

The Duality View offers the “best of both worlds.”

Looks like JSON: Appears as a single document to the app.
Actually Tables: Automatically decomposed and saved into normalized tables in the background.

Let’s actually create a writable JSON view that joins ORDER_ITEMS (details), PRODUCTS (products), and CUSTOMERS (customers) centering on the ORDERS (order) table. Note: Column names may differ depending on the schema version (ORDER_DATETIME, ORDER_TSTAMP, ORDER_DATE, etc.). If an error occurs, check the definition with DESC ORDERS. Here, we use ORDER_TMS based on the provided information.

1. Creation of Duality View

Execute the following SQL to create the JSON view ORDERS_DV. Key Points:

SELECT JSON {...}: Defines the JSON structure.
WITH INSERT UPDATE DELETE: Specifies whether to allow data addition, update, or deletion to the underlying tables via this view. For example, since we don’t want PRODUCTS (product master) to be arbitrarily rewritten from order information, we can restrict it to read-only (though allowed here for practice).
Primary Key Designation: To create a view, the primary key of each table (equivalent to _id) must be included.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW ORDERS_DV AS
SELECT JSON {
  '_id'          : o.order_id,
  'orderDate'    : o.order_tms,
  'orderStatus'  : o.order_status,
  'customer'     : (
    SELECT JSON {
      'customerId' : c.customer_id,
      'fullName'   : c.full_name,
      'email'      : c.email_address
    }
    FROM customers c WITH UPDATE
    WHERE c.customer_id = o.customer_id
  ),
  'lineItems'    : [
    SELECT JSON {
      'lineItemId' : i.line_item_id,
      'orderId'    : i.order_id,
      'quantity'   : i.quantity,
      'unitPrice'  : i.unit_price,
      'product'    : (
         SELECT JSON {
           'productId'   : p.product_id,
           'productName' : p.product_name,
           'price'       : p.unit_price
         }
         FROM products p
         WHERE p.product_id = i.product_id
      )
    }
    FROM order_items i WITH INSERT UPDATE DELETE
    WHERE i.order_id = o.order_id
  ]
}
FROM orders o WITH INSERT UPDATE DELETE;
/

2. Retrieving Data as JSON

You don’t need to write traditional join queries; simply specifying the ID returns a JSON with a hierarchical structure. Explanation of SQL to execute:

json_serialize(data PRETTY): Converts internal binary JSON data in the database into a human-readable text format (with line breaks and indentation) for display.
WHERE v.data."_id" = 1: Searches by looking at the value of the _id field inside the JSON. The trick is to enclose _id in double quotes " because it contains a special character.

SELECT json_serialize(data PRETTY) 
FROM ORDERS_DV v
WHERE v.data."_id" = 1;

Execution Result: Look at this! Data from 4 tables—ORDERS, CUSTOMERS, ORDER_ITEMS, PRODUCTS—was retrieved as one beautiful JSON with just a single SELECT.

{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "CAAE1C659045B944A28889B7C49C33FB",
    "asof" : "0000000000201928"
  },
  "orderDate" : "2021-02-04T13:20:22.245677",
  "orderStatus" : "CANCELLED",
  "customer" :
  {
    "customerId" : 3,
    "fullName" : "Gary Jenkins",
    "email" : "gary.jenkins@internalmail"
  },
  "lineItems" :
  [
    {
      "lineItemId" : 1,
      "orderId" : 1,
      "quantity" : 4,
      "unitPrice" : 37,
      "product" :
      {
        "productId" : 33,
        "productName" : "Boy's Pyjamas (Grey)",
        "price" : 23.32
      }
    },
    ...
  ]
}

Focus Point _metadata: A field named _metadata has been added automatically. The etag here is a hash value representing the “version” of the data. By using this, you can automatically check “Has anyone rewritten the data between the time it was displayed on the screen and the time the save button was pressed?” (Optimistic Locking). This is the amazing part of 26ai.

3. Updating Data as a JSON Document

This is important. When you perform a JSON operation on the view, Oracle automatically judges “which column of which table needs to be updated” and executes it. Explanation of SQL to execute:

UPDATE ORDERS_DV: Applies an update to the view.
json_transform(data, SET ...): A function to pinpoint and rewrite only a part of the JSON data.
'$.lineItems[0].quantity' = 5: An instruction to “Change quantity in the 0th (first element) of the array lineItems to 5.”

Although you could rewrite the entire JSON on the app side and do SET data = 'New JSON', json_transform is faster because it allows partial updates on the DB side.

-- Change quantity from 4 to 5 in the line item of Order ID: 1
UPDATE ORDERS_DV v
SET data = json_transform(data, SET '$.lineItems[0].quantity' = 5)
WHERE v.data."_id" = 1;

COMMIT;

Confirmation of Execution Result: Let’s look directly into the underlying relational table ORDER_ITEMS.

SELECT order_id, quantity FROM order_items WHERE order_id = 1;

  ORDER_ID   QUANTITY
---------- ----------
         1          5
         1          2

Success! Even though we only tweaked a part of the JSON, the corresponding row (quantity) in the ORDER_ITEMS table has been properly updated to 5.

Frequently Asked Questions (FAQ)

I have summarized points where users often stumble during installation or operation.

Q: I get the error ORA-65096: invalid common user or role name during installation. A: You may be connected to CDB$ROOT (the root of the container database). Sample schemas must be created in a PDB (Pluggable Database). Please execute alter session set container=freepdb1; or similar to switch to the PDB before execution.

Q: When executing co_install.sql, I get SP2-0310: unable to open file "co_create.sql". A: The @@ command in the script refers to files in the current directory where SQLPlus is running. Before starting SQLPlus, ensure you use the OS cd command to move to the customer_orders folder.

Q: Should I stop using the HR schema? A: No, HR is still optimal for practicing pure SQL such as joins and aggregations. It is recommended to use CO for verifying modern features like JSON, microservices, and domain definitions.

Q: I get an ORA-40607 error when creating the Duality View. A: Check if the view definition includes all “Primary Keys” of the underlying tables. For example, for the ORDER_ITEMS table, both order_id and line_item_id are mandatory, and for the nested PRODUCTS table, product_id must also be selected.

Q: The script on GitHub has been updated, and the column names differ from the article. A: Sample schemas are updated from time to time on GitHub. Especially date columns (order_date vs order_tms) are subject to change. If an error occurs, I recommend checking the definition with DESC Table_Name.

Summary

Now you are ready to try “Modern Oracle.” It looks like just tables, but this CO schema is the best sandbox for experiencing the powerful multi-model features of 26ai. Please install it in your environment and play around with it!