Announcing a New Sample Schema: Customer Orders

August 27, 2019 | 4 minute read
Chris Saxon
Developer Advocate
Text Size 100%:

Calling all Oracle Database developers! There's a new sample schema in town!

Customer Orders

Customer Orders is a simple new schema for you to use in your presentations, demos, and blogs.

Why Have We Created a New Schema?

The current sample schemas (HR, SH, CO, PM, IX, & BI) were created in the early 2000s, back when 9i was still hip and new.

Nearly two decades and five major releases later, Oracle Database has improved significantly. And the development community has evolved too. JSON has replaced XML as the de facto data transfer format. And some are taking to storing JSON as-in their tables.

So we wanted a data set showing you how to use JSON in Oracle Database. While also highlighting other enhancements added over the years, such as identity columns.

Customer Orders requires Oracle Database 12c or higher.

While we could have added new features to one of the existing schemas, one of the key reasons for using them is they've become a standard you can rely upon. Everyone works from the same definition. Which means you can use the tables in your blog posts, scripts, and presentations without further explanation. Changing the existing schemas breaks this common reference point.

So we decided to have a fresh start. And Customer Orders was born!

What Is the New Schema?

Customer Orders models a simple retail application using these tables:

The sample data represents a basic clothing line. PRODUCTS.PRODUCT_DETAILS stores a JSON document describing each clothing item and its customer reviews. For example:

  "colour" : "red",
  "gender" : "Girl's",
  "brand" : "BRANDNAME",
  "description" : "description",
  "sizes" : [ 
    "1 Yr", "2 Yr", "3-4 Yr", "5-6 Yr", "7-8 Yr", "9-10 Yr" 
  "reviews" :  [
      "rating" : 9,
      "review" : "Review text"

This allows you the flexibility to use this schema to store a wide range of products. All you need to do is add the relevant attributes to your JSON!

Where Can I Find Customer Orders?

Download the schema from the Oracle sample schemas GitHub repository.

How Do I Install Customer Orders?

To install customer orders, you must use Oracle Database or higher.

Once you've downloaded the scripts, run co_main with the following parameters:

This will drop and recreate the user CO.

@co_main <CO_password> <connect string> <tablespace> <temp tablespace>

For example:

@co_main copassword localhost:1521/pdb USERS TEMP

If you want to install the tables in an existing schema, run the following scripts:


For further details, see the readme on GitHub or the Sample Schemas Documentation.

What Can I Do with This Schema?

Whatever you like! Please use this to build demos, write scripts, and use in presentations.

Looking for inspiration?

The installation includes several views and sample queries to get your SQL juices flowing.

Here are a few examples:

Extract Product Reviews from PRODUCT_DETAILS JSON

The sample data include an array of reviews for each product in its JSON data.

Using 12c's JSON_table, you can extract these out to traditional rows-and-columns, like so:

select p.product_name, r.rating, 
       round ( 
         avg ( r.rating ) over (
           partition by product_name
       ) avg_rating,
from   products p,
       json_table (
         p.product_details, '$'
         columns ( 
           nested path '$.reviews[*]'
           columns (
             rating integer path '$.rating',
             review varchar2(4000) path '$.review'
       ) r;

Find High-Value Customers

Knowing who your best customers are and keeping them happy is a great way to keep your company going.

The following uses 12c's row pattern matching to find all the people who placed at least £100 worth of orders for three consecutive months:

with rws as (
         o.customer_id, trunc ( o.order_datetime, 'mm' ) order_month,
         sum ( oi.quantity * oi.unit_price ) month_total
  from   products p
  join   order_items oi
  on     p.product_id = oi.product_id
  join   orders o
  on     oi.order_id = o.order_id
  group  by o.customer_id, trunc ( o.order_datetime, 'mm' )
  select * from rws 
  match_recognize (
    partition by customer_id
    order by order_month
      count(*) as num_months,
      sum ( month_total ) as total_value 
    pattern ( high_value consecutive{2,} )
      high_value as 
        month_total >= 100,
      consecutive as 
        order_month = prev ( add_months ( order_month, 1 ) )
        and month_total >= 100

Product a Matrix of Sales Value by Month and Year

Finance teams often want sales broken down into a table with months across the top and years down the side.

The following shows you how to do this with the PIVOT clause:

with order_totals as (
  select extract ( year from o.order_datetime ) order_year,
         to_char ( o.order_datetime, 'MON', 'NLS_DATE_LANGUAGE = english' ) order_month,
         sum ( oi.quantity * oi.unit_price ) value_of_orders
  from   orders o
  join   order_items oi
  on     o.order_id = oi.order_id
  group  by extract ( year from o.order_datetime ),
         to_char ( o.order_datetime, 'MON', 'NLS_DATE_LANGUAGE = english' )
  select * from order_totals
  pivot (
    sum ( value_of_orders ) value
    for order_month in (
order by order_year;

So what are you waiting for? Head over to GitHub and download it today!

UPDATE 15 Nov 2019: Added link to documentation

Chris Saxon

Developer Advocate

Chris Saxon is an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL!

To help you with this he blogs at All Things SQL. He also creates videos combining SQL and magic on YouTube at the The Magic of SQL.

If you have questions about working with Oracle Database technology, please reach out to him. You can do this via Twitter or on Ask Tom.

Previous Post

Announcing the Winners of the 2018 Oracle Dev Gym Championship for Database Design

Chris Saxon | 2 min read

Next Post

OGB Appreciation Day: SQL Pattern Matching (match_recognize) Use Cases

Chris Saxon | 10 min read