Oracle® Database

Sample Schemas

12c Release 1 (12.1)

E15979-04

April 2013

This manual describes the sample database schemas available with Oracle Database and used in some Oracle documentation and curriculum examples.

Oracle Database Sample Schemas, 12c Release 1 (12.1)

E15979-04

Copyright © 2005, 2013, Oracle and/or its affiliates. All rights reserved.

Primary Author: Roza Leyderman

Contributing Authors: David Austin , Christian Bauwens, Vimmika Dinesh, Mark Drake, Nancy Greenberg, Deepti Kamal, Diana Lorentz, Nagavalli Pataballa

Contributor:  The Database 12c documentation is dedicated to Mark Townsend, who was an inspiration to all who worked on this release.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Contents

List of Examples

List of Figures

List of Tables

Preface

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

1 Introduction to Sample Schemas

- About the Sample Schemas

- Design Principles of the Sample Schemas

- Customer Benefits of the Sample Schemas

- Overview of the Sample Schemas

- Schema HR

- Schema OE

- OC Description

- Schema PM

- Schema IX

- Schema SH

2 Installing Sample Schemas

- Using the Database Configuration Assistant

- Manually Installing Sample Schemas

- Schema Dependencies

- Guidelines for Installing Sample Schemas

- Installing the HR Schema

- Installing Schema OE and Subschema OC

- Installing the PM Schema

- Installing the IX Schema

- Installing the SH Schema

- Resetting Sample Schemas

- Uninstalling Sample Schemas

3 Schema Diagrams

- Sample Schema Diagrams

4 Sample Schema Scripts and Object Descriptions

- About the Scripts

- Master Script

- mksample.sql

- HR Schema

- HR Table Descriptions

- OE Schema

- OE Table Descriptions

- Table OE.CUSTOMERS

- Table OE.INVENTORIES

- Table OE.ORDERS

- Table OE.ORDER_ITEMS

- Table OE.PRODUCT_DESCRIPTIONS

- Table OE.PRODUCT_INFORMATION

- Table OE.WAREHOUSES

- Table OE.PURCHASEORDER

- PM Schema

- PM Table Descriptions

- IX Schema

- IX Table Descriptions

- SH Schema

- SH Table Descriptions

A Purchase-Order XML Schema

- Purchase-Order XML Schema: File purchaseOrder.xsd

Index

List of Examples

- 2-1 How to Uninstall Sample Schemas

- A-1 XML Schema purchaseOrder.xsd

List of Figures

- 3-1 Sample Schemas HR and OE

- 3-2 Sample Schemas OE and PM

- 3-3 Sample Schema SH

List of Tables

- 4-1 HR Schema Scripts

- 4-2 HR Objects

- 4-3 HR.COUNTRIES Table Description

- 4-4 HR.DEPARTMENTS Table Description

- 4-5 HR.EMPLOYEES Table Description

- 4-6 HR.JOBS Table Description

- 4-7 HR.JOB_HISTORY Table Description

- 4-8 HR.LOCATIONS Table Description

- 4-9 HR.REGIONS Table Description

- 4-10 OE Schema Scripts

- 4-11 OE Objects

- 4-12 OE.CUSTOMERS Table Description

- 4-13 OE.INVENTORIES Table Description

- 4-14 OE.ORDERS Table Description

- 4-15 OE.ORDER_ITEMS Table Description

- 4-16 OE.PRODUCT_DESCRIPTIONS Table Description

- 4-17 OE.PRODUCT_INFORMATION Table Description

- 4-18 OE.WAREHOUSES Table Description

- 4-19 PM Schema Scripts

- 4-20 PM Objects

- 4-21 PM.ONLINE_MEDIA Table Description

- 4-22 PM.PRINT_MEDIA Table Description

- 4-23 Information Exchange (IX) Schema Scripts

- 4-24 IX Objects

- 4-25 IX.ORDERS_QUEUETABLE Table Description

- 4-26 IX.STREAMS_QUEUE_TABLE Table Description

- 4-27 SH Schema Scripts

- 4-28 SH Objects

- 4-29 SH.CHANNELS Table Description

- 4-30 SH.COSTS Table Description

- 4-31 SH.COUNTRIES Table Description

- 4-32 SH.CUSTOMERS Table Description

- 4-33 SH.PRODUCTS Table Description

- 4-34 SH.PROMOTIONS Table Description

- 4-35 SH.SALES Table Description

- 4-36 SH.TIMES Table Description

Preface

This guide is a primary source of information about the sample database schemas that are used for examples in Oracle Database documentation.

This preface contains the following topics:

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

Audience

This document is intended for all users of the seed database, which is installed when you install Oracle Database.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Related Documents

This guide does not discuss specific programming examples that use data in the sample schemas; see the Oracle Database documentation library for specific books that discuss the technology that you are using.

Sample database schema OE contains tables that use SQL data type XMLType. For information about the use of such data, see Oracle XML DB Developer's Guide.

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

1 Introduction to Sample Schemas

For many years, Oracle used the simple database schema SCOTT, with its two prominent tables EMP and DEPT, for various examples in documentation and training. These tables are inadequate to show the basic features of Oracle Database and other Oracle products. The sample database schemas described here provide more sutiable material that can be used for product documentation, courseware, software development, and application demos.

This chapter contains the following topics:

- About the Sample Schemas

- Design Principles of the Sample Schemas

- Customer Benefits of the Sample Schemas

- Overview of the Sample Schemas

About the Sample Schemas

The sample database schemas provide a common platform for examples in each release of the Oracle Database. The sample schemas are a set of interlinked database schemas. This set provides approach to complexity:

- Schema Human Resources (HR) is useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

- Schema Order Entry (OE) is useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including nonscalar data types.

- Schema Online Catalog (OC) is a collection of object-relational database objects built inside schema OE.

- Schema Product Media (PM) is dedicated to multimedia data types.

- A set of schemas gathered under the main schema name Information Exchange (IX) can be used to demonstrate Oracle Advanced Queuing capabilities.

- Schema Sales History (SH) is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

Design Principles of the Sample Schemas

The sample database schemas have been created with the following design principles in mind:

- Simplicity and ease of use. Schemas HR and OE are intentionally simple. They provide a graduated path from simple to intermediate levels of database use.

- Relevance for typical users. The base schemas and their extensions bring to the foreground the functionality that customers typically use. Only the most commonly used database objects are built automatically in the schemas. The entire set of schemas provides a foundation upon which one can expand to illustrate additional functionality.

- Extensibility. The sample schemas provide a logical and physical foundation for adding objects to demonstrate functionality beyond the fundamental scope.

- Relevance. The sample schemas are designed to be applicable to e-business and other significant industry trends (for example, XML). When this goal conflicts with the goal of simplicity, schema extensions are used to showcase the trends in focus.

Customer Benefits of the Sample Schemas

Benefits provided by the sample schemas include the following:

- Continuity of context. When encountering the same set of tables everywhere, users, students, and developers can spend less time becoming familiar with the schema and more time understanding or explaining the technical concepts.

- Usability. Customers can use these schemas in the seed database to run examples that are shown in Oracle Database documentation and training materials. This first-hand access to examples facilitates both conceptual understanding and application development.

- Quality. Through central maintenance and testing of both the creation scripts that build the sample schemas and the examples that run against the schemas, the quality of Oracle Database documentation and training materials is enhanced.

Overview of the Sample Schemas

The Oracle Database sample schemas are based on a fictitious sample company that sells goods through various channels. The company operates worldwide to fill orders for products. It has several divisions, each of which is represented by a sample database schema.

- Schema HR – Division Human Resources tracks information about the company employees and facilities.

- Schema OE – Division Order Entry tracks product inventories and sales of company products through various channels.

- Schema PM – Division Product Media maintains descriptions and detailed information about each product sold by the company.

- Schema IX – Division Information Exchange manages shipping through B2B applications.

- Schema SH – Division Sales tracks business statistics to facilitate business decisions.

Schema HR

In the Human Resource (HR) records, each employee has an identification number, e-mail address, job identification code, salary, and manager. Some employees earn commissions in addition to their salary.

The company also tracks information about jobs within the organization. Each job has an identification code, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different positions within the company. When an employee resigns, the duration the employee was working, the job identification number, and the department are recorded.

The sample company is regionally diverse, so it tracks the locations of its warehouses and departments. Each employee is assigned to a department, and each department is identified either by a unique department number or a short name. Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code.

In places where the departments and warehouses are located, the company records details such as the country name, currency symbol, currency name, and the region where the country is located geographically.

Schema OE

The company sells several products, such as computer hardware and software, music, clothing, and tools. The company maintains information about these products, such as product identification numbers, the category into which the product falls, order entry (OE), the weight group (for shipping purposes), the warranty period if applicable, the supplier, the availability status of the product, a list price, a minimum price at which a product will be sold, and a URL address for manufacturer information. Inventory information is also recorded for all products, including the warehouse where the product is available and the quantity on hand. Because products are sold worldwide, the company maintains the names of the products and their descriptions in several languages.

The company maintains warehouses in several locations to fulfill customer needs. Each warehouse has a warehouse identification number, name, facility description, and location identification number.

Customer information is also tracked. Each customer has an identification number. Customer records include customer name, street name, city or province, country, phone numbers (up to five phone numbers for each customer), and postal code. Some customers place orders through the Internet, so e-mail addresses are also recorded. Because of language differences among customers, the company records the native language and territory of each customer.

The company places a credit limit on its customers, to limit the amount of products they can purchase at one time. Some customers have an account manager, and this information is also recorded.

When a customer places an order, the company tracks the date of the order, how the order was placed, the current status of the order, shipping mode, total amount of the order, and the sales representative who helped place the order. The sales representative may or may not be the same person as the account manager for a customer. If an order is placed over the Internet, no sales representative is recorded. In addition to order information, the company also tracks the number of items ordered, the unit price, and the products ordered.

Schema OE also contains XML purchase-order documents. These are stored in Oracle XML DB Repository after validation against the registered XML schema purchaseorder.xsd. You can access these documents in various ways, such as by querying table purchaseorder using SQL, querying public views RESOURCE_VIEW and PATH_VIEW, and querying the repository using XPath expressions.

The purchase-order XML documents are located in Oracle XML DB Repository folder $ORACLE_HOME/rdbms/demo/order_entry/2002/month, where month is a three-letter month abbreviation (for example, Jan, Feb, Mar).

See Also:
Appendix A, "Purchase-Order XML Schema" for the structure of the XML data in table OE.purchaseorder

OC Description

The Online Catalog (OC) subschema of database schema OE addresses an online catalog merchandising scenario. The same customers and products are used in OC as in schema OE proper, but subschema OC organizes the products into a hierarchy of parent categories and subcategories. This hierarchy corresponds to the arrangement on an e-commerce portal site, where users navigate to specific products by drilling down through increasingly specialized categories of products.

Schema PM

The company stores multimedia and print information about its products in a database. The Product Media (PM) schema is used to store such information. Examples of such information are:

- Promotional audio and video clips

- Product images and thumbnails for Web publishing

- Press release texts

- Print media advertisements

- Other promotional texts and translations

Schema IX

The company has decided to test the use of messaging to manage its proposed B2B applications. The plan calls for a small test that will allow a user from outside the firewall to place an order and track its status. The order must be booked into the main system. Then, depending on the location of the customer, the order is routed to the nearest region for shipping. The Information Exchange (IX) schema stores such information.

Eventually, the company intends to expand beyond its current in-house distribution system to a system that will allow other businesses to provide the shipping. The messages sent must be in a self-contained format. XML is the perfect format for sending messages, and both Advanced Queuing Servlet and Oracle Internet Directory provide the required routing between the queues.

After the orders are either shipped or back ordered, a message must be sent back to the employee concerned to inform about the status of the order and to initiate the billing. It is important that the message be delivered only once and that there be a system for tracking and reviewing messages to facilitate resolution of any discrepancies with the order.

For the purpose of this test application, the company uses a database server and an application server. The application provides a mechanism for examining the XML messages as well as monitoring the queues. To demonstrate connectivity from outside the firewall, both the generation of a new order and customer service reporting are performed using queues. The new order application directly enables a queue, while the customer service queries require XML messaging to disable a queue.

Schema SH

The sample company does a high volume of business, so it runs business statistics reports to aid in decision making. Many of these reports are time-based and nonvolatile. That is, they analyze past data trends. The company loads data into its data warehouse regularly to gather statistics for these reports. These reports include annual, quarterly, monthly, and weekly sales figures by product. These reports are stored with the help of schema Sales History (SH).

The company also runs reports on distribution channels through which its sales are delivered. When the company runs special promotions on its products, it analyzes the impact of the promotions on sales. It also analyzes sales by geographical area.

2 Installing Sample Schemas

During a complete installation of your Oracle Database, the sample schemas can be installed automatically with the seed database. If the seed database is removed from the system, you will need to reinstall the sample schemas before you can perform the steps given in the examples that you find in Oracle documentation and training materials.

This chapter describes how to install Product_Name. It contains the following sections:

- Using the Database Configuration Assistant

- Manually Installing Sample Schemas

- Resetting Sample Schemas

Caution:
By installing any of the Oracle Database sample schemas, you will destroy any previously installed schemas that use any of the following user names: HR, OE, PM, SH, IX, BI.
Data contained in any of these schemas will be lost if you run any of the installation scripts described in this section. You should not use the sample schemas for your personal or business data and applications. They are meant to be used for demonstration purposes only.

Using the Database Configuration Assistant

When you install Oracle Database with the Oracle Universal Installer, the sample schemas are installed by default if you select the Basic Installation option. Selecting the sample schemas option installs all sample schemas (HR, OE, PM, SH, IX) in the database. If you choose not to install the sample schemas at that time, you can add them later by following the instructions in section "Manually Installing Sample Schemas".

At the end of the installation process, a dialog box displays the accounts that have been created and their lock status. By default, all sample schemas are locked and their passwords are expired. Before you can use a locked account, you must unlock it and reset its password. You can unlock the accounts at this point in the installation process. Alternatively, after the installation completes, you can unlock the schemas and reset their passwords by using the ALTER USER ... ACCOUNT UNLOCK statement. For example:

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY Password;

See Also:
"Guidelines for Securing Passwords" in Oracle Database Security Guide for guidelines related to creating secure passwords

The sample schemas available to you depend on the edition of Oracle Database that you have installed and its configuration. Refer to the following table:

Schema

Oracle Database Personal edition

Oracle Database Standard edition

Oracle Database Enterprise edition

HR

OK

OK

OK

OE

OK

OK

OK

PM

OK

OK

OK

IX

OK

OK

OK

SH

Not available

Not available

Needs Partitioning Option installed

Manually Installing Sample Schemas

If you decide not to install the sample schemas at the time of your initial database installation using DBCA, then you can also create the sample schemas manually by running SQL scripts. Install Oracle Database Examples (Companion CD, part of the media kit) to include these scripts in the demo directory under $ORACLE_HOME.

See Also:
Oracle Database Examples Installation Guide for download and installation information

Schema Dependencies

Various dependencies have been established among the schemas. So, when you create the schemas manually, you must create them in the following order: HR, OE, PM, IX, and SH.

Use this sequence to create the schemas:

1. Create schema HR.

2. Create schema OE. Schema HR is already present, and you must know the password for schema HR to grant HR object privileges to OE. Some HR tables are visible to user OE with the use of private synonyms. In addition, some OE tables have foreign key relationships to HR tables.

3. Create schema PM: Foreign key relationships require that schema OE already exist when schema PM is created. You must know the password for OE, to grant to PM the right to establish and use these foreign keys.

Note:
Schema PM requires that the database be enabled for the Java Virtual Machine (JVM) and interMedia. You can accomplish this during installation or later using the DBCA.

4. Create schema IX: The information exchange schema, IX, is based on order entry data in schema OE. Again, foreign key relationships require that schema OE already be present when schema IX is created. You must know the password for OE to grant to IX, the right to establish and use the foreign keys.

5. Create schema SH. Schema SH logically depends on schema OE, but you can create schema SH without creating the other four schemas.

Guidelines for Installing Sample Schemas

All scripts necessary to install sample schemas reside in $ORACLE_HOME/demo/schema directory. Before you install sample schemas by running these scripts, follow these guidelines:

1. You must connect as a system administrator using the SYSDBA privilege.

2. When prompted to enter a password for the schema, enter a secure password that meets the requirements described in Oracle Database Security Guide.

3. When prompted for tablespace names while running scripts:

- Enter an appropriate tablespace name, for example, users as the default tablespace for a schema

- Enter temp as the temporary tablespace for a schema

4. When prompted for the log directory name, enter $ORACLE_HOME/demo/schema/log/ or any other existing directory name.

Note:
Make sure that you end the log directory name with a trailing slash, for example, $ORACLE_HOME/demo/schema/log/

Installing the HR Schema

All scripts necessary to create the Human Resource (HR) schema reside in $ORACLE_HOME/demo/schema/human_resources.

You need to call only one script, hr_main.sql, to create all the objects and load the data. The following steps provide a summary of the installation process:

1. Log on to SQL*Plus as SYS and connect using the AS SYSDBA privilege.

sqlplus connect sys as sysdba
Enter password: password

2. To run the hr_main.sql script, use the following command:

SQL> @?/demo/schema/human_resources/hr_main.sql

3. Enter a secure password for HR

specify password for HR as parameter 1:
Enter value for 1:

See Also:
Oracle Database Security Guide for the minimum password requirements

4. Enter an appropriate tablespace, for example, users as the default tablespace for HR

specify default tablespace for HR as parameter 2:
Enter value for 2:

5. Enter temp as the temporary tablespace for HR

specify temporary tablespace for HR as parameter 3:
Enter value for 3:

6. Enter your SYS password

specify password for SYS as parameter 4:
Enter value for 4:

7. Enter the directory path, for example, $ORACLE_HOME/demo/schema/log/, for your log directory

specify log path as parameter 5:
Enter value for 5:

After script hr_main.sql runs successfully and schema HR is installed, you are connected as user HR. To verify that the schema was created, use the following command:

SQL> SELECT       table_name FROM user_tables;

Running hr_main.sql accomplishes the following tasks:

1. Removes any previously installed HR schema

2. Creates user HR and grants the necessary privileges

3. Connects as HR

4. Calls the scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 4-1.

A pair of optional scripts, hr_dn_c.sql and hr_dn_d.sql, is provided as a schema extension. To prepare schema HR for use with the directory capabilities of Oracle Internet Directory, run the hr_dn_c.sql script. If you want to return to the initial setup of schema HR, use script hr_dn_d.sql to undo the effects of script hr_dn_c.sql.

You can use script hr_drop.sql to drop schema HR.

Installing Schema OE and Subschema OC

All scripts necessary to create the Order Entry (OE) schema and its Online Catalog (OC) subschema reside in $ORACLE_HOME/demo/schema/order_entry.

See Also:
Guidelines for Installing Sample Schemas before you run oe_main.sql

You need to call only one script, oe_main.sql, to create all the objects and load the data. Running oe_main.sql accomplishes the following tasks:

1. Removes any previously installed OE schema

2. Creates schema (user) OE and grants it the necessary privileges

3. Connects as OE

4. Calls the scripts that create and populate the schema objects

After the oe_main.sql script runs successfully and schema OE is installed, you are connected as user OE. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 4-10.

You can use scripts oe_drop.sql and oc_drop.sql to drop schema OE and subschema OC, respectively.

Installing the PM Schema

All scripts necessary to create the Product Media (PM) schema reside in $ORACLE_HOME/demo/schema/product_media.

See Also:
Guidelines for Installing Sample Schemas before you run pm_main.sql

You need to call only one script, pm_main.sql, to create all the objects and load the data. Running pm_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts as well as datafile and log file directories

2. Removes any previously installed PM schema

3. Creates user PM and grants it the necessary privileges

4. Connects as PM

5. Calls the scripts that create and populate the schema objects

After script pm_main.sql runs successfully and schema PM is installed, you are connected as user PM. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 4-19 .

You can use script pm_drop.sql to drop schema PM.

Note:
The SQL*Loader data file pm_p_lob.dat contains hard-coded absolute path names that have been set during installation. Before attempting to load the data in a different environment, you should first edit the path names in this file.

Installing the IX Schema

All scripts necessary to create the Information Exchange (IX) schema reside in $ORACLE_HOME/demo/schema/info_exchange.

See Also:
Guidelines for Installing Sample Schemas before you run ix_main.sql

To install schema IX, you need to call only one script, ix_main.sql, which creates all the objects and loads the data.

Running ix_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts

2. Removes any previously installed IX schema

3. Creates user IX and grants the necessary privileges

4. Connects as IX

5. Calls the scripts that create and populate the schema objects

After the ix_main.sql script runs successfully and schema IX is installed, you are connected as user IX. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 4-23.

You can use script dix_v3.sql to drop schema IX.

Installing the SH Schema

All scripts necessary to create the Sales History (SH) schema reside in $ORACLE_HOME/demo/schema/sales_history.

See Also:
Guidelines for Installing Sample Schemas before you run sh_main.sql

You need to call only one script, sh_main.sql, to create all the objects and load the data. Running sh_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts as well as datafile and log file directories

2. Removes any previously installed SH schema

3. Creates user SH and grants the necessary privileges

4. Connects as SH

5. Calls the scripts that create and populate the schema objects

After script sh_main.sql runs successfully and schema SH is installed, you are connected as user SH. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 4-27.

Note:
The dimension tables PROMOTIONS, CUSTOMERS, PRODUCTS and the fact table SALES are loaded by SQL*Loader, after which directory paths are created inside the database to point to the load and log file locations. This allows the loading of the COSTS table by using the external table sales_transactions_ext.

A pair of optional scripts, sh_olp_c.sql and sh_olp_d.sql, is provided as a schema extension. To prepare schema SH for use with the advanced analytical capabilities of OLAP Services, run the sh_olp_c.sql create script. If you want to return to the initial setup of schema SH, then use script sh_olp_d.sql to undo the effects of sh_olp_c.sql and reinstate dimensions as they were before.

You can use script sh_drop.sql to drop schema SH.

Resetting Sample Schemas

To reset sample schemas to their initial state, use the following syntax from the SQL*Plus command-line interface:

@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temp_tablespace log_file_directory/

The mksample script expects 11 parameters. Provide the password for SYSTEM and SYS, and for schemas HR, OE, PM, IX, and SH. Specify a temporary and a default tablespace, and make sure to end the name of the log file directory with a trailing slash.

The mksample script produces several log files:

- mkverify.log is the Sample Schema creation log file.

- hr_main.log is the HR schema creation log file.

- oe_oc_main.log is the OE schema creation log file.

- pm_main.log is the PM schema creation log file.

- pm_p_lob.log is the SQL*Loader log file for PM.PRINT_MEDIA.

- ix_main.log is the IX schema creation log file.

- sh_main.log is the SH schema creation log file.

- cust.log is the SQL*Loader log file for SH.CUSTOMERS.

- prod.log is the SQL*Loader log file for SH.PRODUCTS.

- promo.log is the SQL*Loader log file for SH.PROMOTIONS.

- sales.log is the SQL*Loader log file for SH.SALES.

- sales_ext.log is the external table log file for SH.COSTS.

In most situations, there is no difference between installing a Sample Schema for the first time or reinstalling it over a previously installed version. The *_main.sql scripts drop the schema users and all of their objects.

In some cases, complex interobject relationships in schema OE or IX prevent DROP USER ... CASCADE operations from completing normally. To correct these rare cases, use one of the following procedures:

For the OC catalog subschema of schema OE:

1. Connect as user OE.

2. Run script oc_drop.sql.

3. Connect as SYSTEM.

4. Ensure that user OE is not connected:

SELECT username FROM v$session;

5. Drop user OE:

DROP USER oe CASCADE;

For the IX schemas:

1. Connect as SYSTEM.

2. Ensure that no user is connected as an IX user:

SELECT username FROM v$session WHERE username like 'IX%';

3. Drop the schemas by running script dix.sql. You will be prompted for passwords for the individual users.

Uninstalling Sample Schemas

If you need to remove the sample schemas from the installation, run script drop_sch.sql on the SQL*Plus command line. This script ships with Oracle Database.

Example 2-1 How to Uninstall Sample Schemas

@?/demo/schema/drop_sch.sql systempwd spool_file_name

Script drop_sch.sql uses two parameters: systempwd is the password for SYSTEM user, and spool_file_name is the name of the spool file that captures the log of the operation.

3 Schema Diagrams

This chapter contains diagrams of the sample database schemas.

Sample Schema Diagrams

Figure 3-1, "Sample Schemas HR and OE". illustrates sample schemas HR and OE and their relationship. The scripts and table descriptions for these schemas are in section "HR Schema" and "OE Schema" , respectively.

Figure 3-2, "Sample Schemas OE and PM" illustrates schema PM. The scripts and table description for schema PM are at "PM Schema" .

Figure 3-3, "Sample Schema SH" illustrates schema SH. The scripts and table description for schema SH are in section "SH Schema" .

This edition of the book does not illustrate schema IX, but its scripts and table description are in section "IX Schema" .

Figure 3-1 Sample Schemas HR and OE

Figure 3-2 Sample Schemas OE and PM

Figure 3-3 Sample Schema SH

4 Sample Schema Scripts and Object Descriptions

This chapter describes the scripts used to generate the Oracle Database Sample Schemas. It contains the following sections:

- About the Scripts

- Master Script

- HR Schema

- OE Schema

- PM Schema

- IX Schema

- SH Schema

About the Scripts

Sample schemas script directories are located in $ORACLE_HOME/demo/schema. You must install the Oracle Database Examples media to populate the directories with the Sample Schema scripts. Each schema has two primary scripts:

- The xx_main.sql script, where xx is the schema abbreviation, resets and creates all objects and data for a particular schema. This main script calls all other scripts necessary to build and load the schema.

- Script xx_drop.sql, where xx is the schema name, removes all objects from a particular schema.

Sample Schemas script directories are located in the $ORACLE_HOME/demo/schema directory after completing the Oracle Database Examples installation.

Note:
This chapter contains only the master script for the entire sample schemas environment. It does not include the scripts for the individual schemas because these scripts are very lengthy.

Master Script

The master script, mksample.sql, sets up the overall Sample Schema environment and creates all the schemas.

Note:
In the master script (mksample.sql), which follows, you will notice variables such as %s_pmPath%, %s_logPath%, and %s_shPath%. These variables are instantiated on installation.

mksample.sql

The text of the mksample.sql script follows:

Rem
Rem $Header: mksample.sql.sbs 02-apr-2003.14:55:17 $
Rem
Rem mksample.sql
Rem
Rem Copyright (c) 2001, 2003, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem mksample.sql - creates all 5 Sample Schemas
Rem
Rem DESCRIPTION
Rem This script rees and creates all Schemas belonging
Rem to the Oracle Database 10g Sample Schemas.
Rem If you are unsure about the prerequisites for the Sample Schemas,
Rem please use the Database Configuration Assistant DBCA to
Rem configure the Sample Schemas.
Rem
Rem NOTES
Rem - OUI instantiates this script during install and saves it
Rem as mksample.sql. The instantiated scripts matches
Rem the directory structure on your system
Rem - Tablespace EXAMPLE created with:
Rem CREATE TABLESPACE example
Rem NOLOGGING
Rem DATAFILE '<filename>' SIZE 150M REUSE
Rem AUTOEXTEND ON NEXT 640k
Rem MAXSIZE UNLIMITED
Rem EXTENT MANAGEMENT LOCAL
Rem SEGMENT SPACE MANAGEMENT AUTO;
Rem
Rem - CAUTION: This script will erase the following schemas:
Rem - HR
Rem - OE
Rem - PM
Rem - SH
Rem - IX
Rem - BI
Rem - CAUTION: Never use the preceding Sample Schemas for
Rem anything other than demos and examples
Rem - USAGE: To return the Sample Schemas to their initial
Rem state, you can call this script and pass the passwords
Rem for SYS, SYSTEM and the schemas as parameters.
Rem Example: @?/demo/schema/mksample mgr secure h1 o2 p3 q4 s5
Rem (please choose your own passwords for security purposes)
Rem
Rem MODIFIED (MM/DD/YY)
Rem
Rem

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 999
SET ECHO OFF
SET CONCAT '.'
SET SHOWMODE OFF

PROMPT
PROMPT specify password for SYSTEM as parameter 1:
DEFINE password_system = &1
PROMPT
PROMPT specify password for SYS as parameter 2:
DEFINE password_sys = &2
PROMPT
PROMPT specify password for HR as parameter 3:
DEFINE password_hr = &3
PROMPT
PROMPT specify password for OE as parameter 4:
DEFINE password_oe = &4
PROMPT
PROMPT specify password for PM as parameter 5:
DEFINE password_pm = &5
PROMPT
PROMPT specify password for IX as parameter 6:
DEFINE password_ix = &6
PROMPT
PROMPT specify password for SH as parameter 7:
DEFINE password_sh = &7
PROMPT
PROMPT specify password for BI as parameter 8:
DEFINE password_bi = &8
PROMPT
PROMPT specify default tablespace as parameter 9:
DEFINE default_ts = &9
PROMPT
PROMPT specify temporary tablespace as parameter 10:
DEFINE temp_ts = &10
PROMPT
PROMPT specify log file directory (including trailing delimiter) as parameter
11:
DEFINE logfile_dir = &11
PROMPT
PROMPT Sample Schemas are being created ...
PROMPT
DEFINE vrs = v3

CONNECT system/&&password_system

DROP USER hr CASCADE;
DROP USER oe CASCADE;
DROP USER pm CASCADE;
DROP USER ix CASCADE;
DROP USER sh CASCADE;
DROP USER bi CASCADE;

CONNECT system/&&password_system

SET SHOWMODE OFF

@?/demo/schema/human_resources/hr_main.sql &&password_hr &&default_ts &&temp_ts
&&password_sys &&logfile_dir

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts
&&password_hr &&password_sys %s_oePath% &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/product_media/pm_main.sql &&password_pm &&default_ts &&temp_ts
&&password_oe &&password_sys %s_pmPath% &&logfile_dir %s_pmPath%

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/info_exchange/ix_main.sql &&password_ix &&default_ts &&temp_ts
&&password_sys &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/sales_history/sh_main &&password_sh &&default_ts &&temp_ts
&&password_sys %s_shPath% &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/bus_intelligence/bi_main &&password_bi &&default_ts &&temp_ts
&&password_sys &&password_oe &&password_sh &&logfile_dir &vrs

CONNECT system/&&password_system

SPOOL OFF

DEFINE veri_spool = &&logfile_dir.mkverify_&vrs..log

@?/demo/schema/mkverify &&password_system &veri_spool

EXIT

HR Schema

This section lists the names of the scripts that create the human resources (HR) schema and describes the objects in the schema. Table 4-1 lists the HR scripts in alphabetical order, while Table 4-2 lists its objects.

Table 4-1 HR Schema Scripts

Script Name

Description

hr_analz.sql

Collects statistics on the tables in the schema

hr_code.sql

Creates procedural objects in the schema

hr_comnt.sql

Creates comments for each object in the schema

hr_cre.sql

Creates the HR objects

hr_dn_c.sql

Adds the distinguished name column used by Oracle Internet Directory to the employees and departments tables

hr_dn_d.sql

Drops the Oracle Internet Directory distinguished name column from employees and departments

hr_drop.sql

Drops schema HR and all its objects

hr_idx.sql

Creates indexes on the HR tables

hr_main.sql

Main script for schema HR ; calls other scripts

hr_popul.sql

Populates the objects

Table 4-2 HR Objects

Object Type

Objects

Index

COUNTRY_C_ID_PK, DEPT_ID_PK, DEPT_LOCATION_IX, EMP_DEPARTMENT_IX, EMP_EMAIL_UK, EMP_EMP_ID_PK, EMP_JOB_IX, EMP_MANAGER_IX, EMP_NAME_IX, JHIST_DEPARTMENT_IX, JHIST_EMPLOYEE_IX, JHIST_EMP_ID_ST_DATE_PK, JHIST_JOB_IX, JOB_ID_PK, LOC_CITY_IX, LOC_COUNTRY_IX, LOC_ID_PK, LOC_STATE_PROVINCE_IX, REG_ID_PK

Procedure

ADD_JOB_HISTORY, SECURE_DML

Sequence

DEPARTMENTS_SEQ, EMPLOYEES_SEQ, LOCATIONS_SEQ

Table

COUNTRIES, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY, LOCATIONS, REGIONS

Trigger

SECURE_EMPLOYEES, UPDATE_JOB_HISTORY

View

EMP_DETAILS_VIEW

HR Table Descriptions

This section describes the columns of each table of schema HR.

- Table 4-3, "HR.COUNTRIES Table Description"

- Table 4-4, "HR.DEPARTMENTS Table Description"

- Table 4-5, "HR.EMPLOYEES Table Description"

- Table 4-6, "HR.JOBS Table Description"

- Table 4-7, "HR.JOB_HISTORY Table Description"

- Table 4-8, "HR.LOCATIONS Table Description"

- Table 4-9, "HR.REGIONS Table Description"

Table 4-3 HR.COUNTRIES Table Description

Column Name

Null?

Type

COUNTRY_ID

NOT NULL

CHAR(2)

COUNTRY_NAME

VARCHAR2(40)

REGION_ID

NUMBER

Table 4-4 HR.DEPARTMENTS Table Description

Column Name

Null?

Type

DEPARTMENT_ID

NOT NULL

NUMBER(4)

DEPARTMENT_NAME

NOT NULL

VARCHAR2(30)

MANAGER_ID

NUMBER(6)

LOCATION_ID

NUMBER(4)

Table 4-5 HR.EMPLOYEES Table Description

Column Name

Null?

Type

EMPLOYEE_ID

NOT NULL

NUMBER(6)

FIRST_NAME

VARCHAR2(20)

LAST_NAME

NOT NULL

VARCHAR2(25)

EMAIL

NOT NULL

VARCHAR2(20)

PHONE_NUMBER

VARCHAR2(20)

HIRE_DATE

NOT NULL

DATE

JOB_ID

NOT NULL

VARCHAR2(10)

SALARY

NUMBER(8,2)

COMMISSION_PCT

NUMBER(2,2)

MANAGER_ID

NUMBER(6)

DEPARTMENT_ID

NUMBER(4)

Table 4-6 HR.JOBS Table Description

Column Name

Null?

Type

JOB_ID

NOT NULL

VARCHAR2(10)

JOB_TITLE

NOT NULL

VARCHAR2(35)

MIN_SALARY

NUMBER(6)

MAX_SALARY

NUMBER(6)

Table 4-7 HR.JOB_HISTORY Table Description

Column Name

Null?

Type

EMPLOYEE_ID

NOT NULL

NUMBER(6)

START_DATE

NOT NULL

DATE

END_DATE

NOT NULL

DATE

JOB_ID

NOT NULL

VARCHAR2(10)

DEPARTMENT_ID

NUMBER(4)

Table 4-8 HR.LOCATIONS Table Description

Column Name

Null?

Type

LOCATION_ID

NOT NULL

NUMBER(4)

STREET_ADDRESS

VARCHAR2(40)

POSTAL_CODE

VARCHAR2(12)

CITY

NOT NULL

VARCHAR2(30)

STATE_PROVINCE

VARCHAR2(25)

COUNTRY_ID

CHAR(2)

Table 4-9 HR.REGIONS Table Description

Column Name

Null?

Type

REGION_ID

NOT NULL

NUMBER

REGION_NAME

VARCHAR2(25)

OE Schema

This section lists the names of the scripts that create the Order Entry (OE) sample schema and describes the objects in the schema. Table 4-10 lists the OE scripts in alphabetical order, while Table 4-11 lists its objects. Note that language-specific statements for product names and descriptions are stored in these files (each representing a different language): INSERToe_p_us.sqloe_p_ar.sql, oe_p_cs.sql, oe_p_d.sql, oe_p_dk.sql, oe_p_e.sql, oe_p_el.sql, oe_p_esa.sql, oe_p_f.sql, oe_p_frc.sql, oe_p_hu.sql, oe_p_i.sql, oe_p_iw.sql, oe_p_ja.sql, oe_p_ko.sql, oe_p_n.sql, oe_p_nl.sql, oe_p_pl.sql, oe_p_pt.sql, oe_p_ptb.sql, oe_p_ro.sql, oe_p_ru.sql, oe_p_s.sql, oe_p_sf.sql, oe_p_sk.sql, oe_p_th.sql, oe_p_tr.sql, oe_p_zhs.sql, oe_p_zht.sql.

Table 4-10 OE Schema Scripts

Script Name

Description

oc_comnt.sql

Adds comments to the online catalog (OC) subschema wherever possible

oc_cre.sql

Creates subschema OC

oc_drop.sql

Drops subschema OC

oc_main.sql

Main script for subschema OC

oc_popul.sqla

Populates the object tables

oe_analz.sql

Gathers statistics on the OE objects

oe_comnt.sql

Creates comments for the objects in the schema

oe_cre.sql

Creates the OE objects

oe_drop.sql

Drops schema OE and all its objects

oe_idx.sql

Creates indexes on the OE tables

oe_main.sql

Main script for the OE schema; calls other scripts

oe_views.sql

Creates the OE schema views

Table 4-11 OE Objects

Object Type

Objects

Index

CUSTOMERS_PK, CUST_ACCOUNT_MANAGER_IX, CUST_EMAIL_IX, CUST_LNAME_IX, CUST_UPPER_NAME_IX, INVENTORY_IX, INV_PRODUCT_IX, ITEM_ORDER_IX, ITEM_PRODUCT_IX, ORDER_ITEMS_PK, ORDER_ITEMS_UK, ORDER_PK, ORD_CUSTOMER_IX, ORD_ORDER_DATE_IX, ORD_SALES_REP_IX, PRD_DESC_PK, PRODUCT_INFORMATION_PK, PROD_NAME_IX, PROD_SUPPLIER_IX, PROMO_ID_PK, REFERENCE_IS_UNIQUE, SYS_C003584, SYS_C003587, SYS_C003588, SYS_C003589, SYS_C003590, WAREHOUSES_PK, WHS_LOCATION_IX

Function

GET_PHONE_NUMBER_F

Sequence

ORDERS_SEQ

Lob

SYS_LOB0000045843C00022$$, SYS_LOB0000045843C00023$$, SYS_LOB0000045852C00003$$, SYS_LOB0000045852C00012$$, SYS_LOB0000045852C00013$$, SYS_LOB0000046019C00004$$, SYS_LOB0000046019C00005$$, SYS_LOB0000046019C00007$$, SYS_LOB0000046019C00011$$, SYS_LOB0000046019C00012$$, SYS_LOB0000046019C00015$$, SYS_LOB0000046019C00024$$, SYS_LOB0000046019C00031$$, SYS_LOB0000046019C00032$$, SYS_LOB0000046044C00003$$

Synonym

COUNTRIES, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY, LOCATIONS

Table

CUSTOMERS, INVENTORIES, ORDERS, ORDER_ITEMS, PRODUCT_DESCRIPTIONS, PRODUCT_INFORMATION, WAREHOUSES

Trigger

INSERT_ORD_LINE, ORDERS_ITEMS_TRG, ORDERS_TRG

Type

CATALOG_TYP, CATEGORY_TYP, COMPOSITE_CATEGORY_TYP, CORPORATE_CUSTOMER_TYP, CUSTOMER_TYP, CUST_ADDRESS_TYP, INVENTORY_LIST_TYP, INVENTORY_TYP, LEAF_CATEGORY_TYP, ORDER_ITEM_LIST_TYP, ORDER_ITEM_TYP, ORDER_LIST_TYP, ORDER_TYP, PHONE_LIST_TYP, PRODUCT_INFORMATION_TYP, PRODUCT_REF_LIST_TYP, SUBCATEGORY_REF_LIST_TYP, SYS_YOID0000046073$, SYS_YOID0000046075$, SYS_YOID0000046077$, SYS_YOID0000046079$, SYS_YOID0000046081$, WAREHOUSE_TYP, XDBPO_ACTIONS_TYPE, XDBPO_ACTION_COLLECTION, XDBPO_ACTION_TYPE, XDBPO_LINEITEMS_TYPE, XDBPO_LINEITEM_COLLECTION, XDBPO_LINEITEM_TYPE, XDBPO_PART_TYPE, XDBPO_REJECTION_TYPE, XDBPO_SHIPINSTRUCTIONS_TYPE, XDBPO_TYPE

Type Body

CATALOG_TYP, COMPOSITE_CATEGORY_TYP, LEAF_CATEGORY_TYP

View

ACCOUNT_MANAGERS, BOMBAY_INVENTORY, CUSTOMERS_VIEW, DEPTVIEW, OC_CORPORATE_CUSTOMERS, OC_CUSTOMERS, OC_INVENTORIES, OC_ORDERS, OC_PRODUCT_INFORMATION, ORDERS_VIEW, PRODUCTS, PRODUCT_PRICES, SYDNEY_INVENTORY, TORONTO_INVENTORY

OE Table Descriptions

This section describes the tables of sample database schema OE.

- Table OE.CUSTOMERS

- Table OE.INVENTORIES

- Table OE.ORDERS

- Table OE.ORDER_ITEMS

- Table OE.PRODUCT_DESCRIPTIONS

- Table OE.PRODUCT_INFORMATION

- Table OE.WAREHOUSES

- Table OE.PURCHASEORDER

Table OE.CUSTOMERS

Table 4-12 OE.CUSTOMERS Table Description

Column Name

Null?

Type

CUSTOMER_ID

NOT NULL

NUMBER(6)

CUST_FIRST_NAME

NOT NULL

VARCHAR2(20)

CUST_LAST_NAME

NOT NULL

VARCHAR2(20)

CUST_ADDRESS

CUST_ADDRESS_TYP

PHONE_NUMBERS

PHONE_LIST_TYP

NLS_LANGUAGE

VARCHAR2(3)

NLS_TERRITORY

VARCHAR2(30)

CREDIT_LIMIT

NUMBER(9,2)

CUST_EMAIL

VARCHAR2(30)

ACCOUNT_MGR_ID

NUMBER(6)

CUST_GEO_LOCATION

MDSYS.SDO_GEOMETRY

DATE_OF_BIRTH

DATE

MARITAL_STATUS

VARCHAR2(20)

GENDER

VARCHAR2(1)

INCOME_LEVEL

VARCHAR2(20)

Table OE.INVENTORIES

Table 4-13 OE.INVENTORIES Table Description

Column Name

Null?

Type

PRODUCT_ID

NOT NULL

NUMBER(6)

WAREHOUSE_ID

NOT NULL

NUMBER(3)

QUANTITY_ON_HAND

NOT NULL

NUMBER(8)

Table OE.ORDERS

Table 4-14 OE.ORDERS Table Description

Column Name

Null?

Type

ORDER_ID

NOT NULL

NUMBER(12)

ORDER_DATE

NOT NULL

TIMESTAMP(6) WITH LOCAL TIME ZONE

ORDER_MODE

VARCHAR2(8)

CUSTOMER_ID

NOT NULL

NUMBER(6)

ORDER_STATUS

NUMBER(2)

ORDER_TOTAL

NUMBER(8,2)

SALES_REP_ID

NUMBER(6)

PROMOTION_ID

NUMBER(6)

Table OE.ORDER_ITEMS

Table 4-15 OE.ORDER_ITEMS Table Description

Column Name

Null?

Type

ORDER_ID

NOT NULL

NUMBER(12)

LINE_ITEM_ID

NOT NULL

NUMBER(3)

PRODUCT_ID

NOT NULL

NUMBER(6)

UNIT_PRICE

NUMBER(8,2)

QUANTITY

NUMBER(8)

Table OE.PRODUCT_DESCRIPTIONS

Table 4-16 OE.PRODUCT_DESCRIPTIONS Table Description

Column Name

Null?

Type

PRODUCT_ID

NOT NULL

NUMBER(6)

LANGUAGE_ID

NOT NULL

VARCHAR2(3)

TRANSLATED_NAME

NOT NULL

NVARCHAR2(50)

TRANSLATED_DESCRIPTION

NOT NULL

NVARCHAR2(2000)

Table OE.PRODUCT_INFORMATION

Table 4-17 OE.PRODUCT_INFORMATION Table Description

Column Name

Null?

Type

PRODUCT_ID

NOT NULL

NUMBER(6)

PRODUCT_NAME

VARCHAR2(50)

PRODUCT_DESCRIPTION

VARCHAR2(2000)

CATEGORY_ID

NUMBER(2)

WEIGHT_CLASS

NUMBER(1)

WARRANTY_PERIOD

INTERVAL YEAR(2) TO MONTH

SUPPLIER_ID

NUMBER(6))

PRODUCT_STATUS

VARCHAR2(20)

LIST_PRICE

NUMBER(8,2)

MIN_PRICE

NUMBER(8,2)

CATALOG_URL

VARCHAR2(50)

Table OE.WAREHOUSES

Table 4-18 OE.WAREHOUSES Table Description

Column Name

Null?

Type

WAREHOUSE_ID

NOT NULL

NUMBER(3)

WAREHOUSE_SPEC

SYS.XMLTYPE

WAREHOUSE_NAME

VARCHAR2(35)

LOCATION_ID

NUMBER(4)

WH_GEO_LOCATION

MDSYS.SDO_GEOMETRY

Column warehouse_spec of table OE.warehouses contains XMLType data. This data is not based on any XML schema, which means that it can take any form. However, the actual data in column warehouse_spec at the outset (before any changes you might have made to it) has a top-level element Warehouse with the following child elements:

- Building, with text node Owned or Rented

- Area, with text node a number (representing, for example, square feet)

- Docks, with text node the number of loading docks (for example, 1, 2, or 3)

- DockType, with text node empty or Rear Load or Side Load

- WaterAccess, with text node Y or N

- RailAccess, with text node Y or N

- Parking, with text node Street or Lot

- VClearance (vertical clearance), with text node a number followed by a linear unit (for example, 11.5 ft)

See Also:
Oracle XML DB Developer's Guide for examples using the XMLType data in column warehouse_spec

Table OE.PURCHASEORDER

Table OE.purchaseorder is an object-relational table with XMLType data. The data conforms to XML schema purchaseOrder.xsd — see Appendix A, "Purchase-Order XML Schema".

PM Schema

This section lists the names of the scripts that create the Product Media (PM) schema and describes the objects in the schema. Table 4-19 lists the OE scripts in alphabetical order, while Table 4-20 lists its objects. Note that the SQL*Loader data file pm_p_lob.dat contains hard-coded absolute path names that have been set during installation. Before attempting to load the data in a different environment, you should first edit the path names in this file.

Table 4-19 PM Schema Scripts

Script Name

Description

pm_analz.sql

Gathers statistics on the PM objects

pm_cre.sql

Creates the PM objects

pm_drop.sql

Drops schema PM and all its objects

pm_p_ord.sql, pm_p_lob.sql, pm_p_lob.ctl, pm_p_lob.dat

Populates the objects in the schema

pm_main.sql

Main script for schema PM, which calls other scripts

Table 4-20 PM Objects

Object Type

Objects

Index

ONLINEMEDIA_PK, PRINTMEDIA_PK, SYS_C003538

Lob

SYS_LOB0000045882C00003$$, SYS_LOB0000045882C00017$$, SYS_LOB0000045882C00019$$, SYS_LOB0000045882C00034$$, SYS_LOB0000045882C00042$$, SYS_LOB0000045882C00054$$, SYS_LOB0000045882C00062$$, SYS_LOB0000045882C00069$$, SYS_LOB0000045882C00071$$, SYS_LOB0000045882C00080$$, SYS_LOB0000045907C00003$$, SYS_LOB0000045907C00004$$, SYS_LOB0000045907C00005$$, SYS_LOB0000045907C00006$$, SYS_LOB0000045907C00009$$, SYS_LOB0000045907C00015$$, SYS_LOB0000045908C00004$$

Table

ONLINE_MEDIA, PRINT_MEDIA

Type

ADHEADER_TYP, TEXTDOC_TAB, TEXTDOC_TYP

PM Table Descriptions

This section describes the columns of each table of schema PM, as follows:

- Table 4-21, "PM.ONLINE_MEDIA Table Description"

- Table 4-22, "PM.PRINT_MEDIA Table Description"

Table 4-21 PM.ONLINE_MEDIA Table Description

Column Name

Null?

Type

PRODUCT_ID

NOT NULL

NUMBER(6)

PRODUCT_PHOTO

ORDSYS.ORDIMAGE

PRODUCT_PHOTO_SIGNATURE

ORDSYS.ORDIMAGESIGNATURE

This type, ORDImageSignature, is deprecated; do not use it in new code. Existing occurrences of this object type will continue to function as in the past.

PRODUCT_THUMBNAIL

ORDSYS.ORDIMAGE

PRODUCT_VIDEO

ORDSYS.ORDVIDEO

PRODUCT_AUDIO

ORDSYS.ORDAUDIO

PRODUCT_TEXT

CLOB

PRODUCT_TESTIMONIALS

ORDSYS.ORDDOC

Table 4-22 PM.PRINT_MEDIA Table Description

Column Name

Null?

Type

PRODUCT_ID

NOT NULL

NUMBER(6)

AD_ID

NOT NULL

NUMBER(6)

AD_COMPOSITE

BLOB

AD_SOURCETEXT

CLOB

AD_FINALTEXT

CLOB

AD_FLTEXTN

NCLOB

AD_TEXTDOCS_NTAB

TEXTDOC_TAB

AD_PHOTO

BLOB

AD_GRAPHIC

BINARY FILE LOB

AD_HEADER

ADHEADER_TYP

IX Schema

This section lists the names of the scripts that create the Information Exchange (IX) schema group and describes the objects in the schemas. Table 4-23 lists the IX scripts in alphabetical order, while Table 4-24 lists its objects.

Table 4-23 Information Exchange (IX) Schema Scripts

Script Name

Description

cix_v3.sql

Creates the IX schema objects

dix_v3.sql

Drops schema IX objects

ix_main.sql

Main script for schema IX; calls other scripts

vix_v3.sql

Enables, disables, and verifies IX objects

Table 4-24 IX Objects

Object Type

Objects

Evaluation Context

AQ$_ORDERS_QUEUETABLE_V, AQ$_STREAMS_QUEUE_TABLE_V

Index

SYS_C003540, SYS_C003543, SYS_C003548, SYS_C003551, SYS_IOT_TOP_45932, SYS_IOT_TOP_45934, SYS_IOT_TOP_45936, SYS_IOT_TOP_45939, SYS_IOT_TOP_45949, SYS_IOT_TOP_45951, SYS_IOT_TOP_45953, SYS_IOT_TOP_45956

Lob

SYS_LOB0000045926C00036$$, SYS_LOB0000045941C00028$$, SYS_LOB0000045941C00029$$

Queue

AQ$_ORDERS_QUEUETABLE_E, AQ$_STREAMS_QUEUE_TABLE_E, ORDERS_QUEUE, STREAMS_QUEUE

Rule Set

ORDERS_QUEUE_N, ORDERS_QUEUE_R, STREAMS_QUEUE_N, STREAMS_QUEUE_R

Sequence

AQ$_ORDERS_QUEUETABLE_N, AQ$_STREAMS_QUEUE_TABLE_N

Table

ORDERS_QUEUETABLE, STREAMS_QUEUE_TABLE

Type

ORDER_EVENT_TYP

View

AQ$ORDERS_QUEUETABLE, AQ$ORDERS_QUEUETABLE_R, AQ$ORDERS_QUEUETABLE_S, AQ$STREAMS_QUEUE_TABLE, AQ$STREAMS_QUEUE_TABLE_R, AQ$STREAMS_QUEUE_TABLE_S

IX Table Descriptions

This section describes the columns of each table of schema IX.

- Table 4-25, "IX.ORDERS_QUEUETABLE Table Description"

- Table 4-26, "IX.STREAMS_QUEUE_TABLE Table Description"

Table 4-25 IX.ORDERS_QUEUETABLE Table Description

Column Name

Null?

Type

Q_NAME

VARCHAR2(30)

MSGID

NOT NULL

RAW(16)

CORRID

VARCHAR2(128)

PRIORITY

NUMBER

STATE

NUMBER

DELAY

TIMESTAMP(6)

EXPIRATION

NUMBER

TIME_MANAGER_INFO

TIMESTAMP(6)

LOCAL_ORDER_NO

NUMBER

CHAIN_NO

NUMBER

CSCN

NUMBER

DSCN

NUMBER

ENQ_TIME

TIMESTAMP(6)

ENQ_UID

VARCHAR2(30)

ENQ_TID

VARCHAR2(30)

DEQ_TIME

TIMESTAMP(6)

EEQ_UID

VARCHAR2(30)

DEQ_TID

VARCHAR2(30)

RETRY_COUNT

NUMBER

EXCEPTION_QSCHEMA

VARCHAR2(30)

EXCEPTION_QUEUE

VARCHAR2(30)

STEP_NO

NUMBER

RECIPIENT_KEY

NUMBER

DEQUEUE_MSGID

RAW(16)

SENDER_NAME

VARCHAR2(30)

SENDER_ADDRESS

VARCHAR2(1024)

SENDER_PROTOCOL

NUMBER

USER_DATA

ORDER_EVENT_TYP

USER_PROP

SYS.ANYDATA

Table 4-26 IX.STREAMS_QUEUE_TABLE Table Description

Column Name

Null?

Type

Q_NAME

VARCHAR2(30)

MSGID

NOT NULL

RAW(16)

CORRID

VARCHAR2(128)

PRIORITY

NUMBER

STATE

NUMBER

DELAY

TIMESTAMP(6)

EXPIRATION

NUMBER

TIME_MANAGER_INFO

TIMESTAMP(6)

LOCAL_ORDER_NO

NUMBER

CHAIN_NO

NUMBER

CSCN

NUMBER

DSCN

NUMBER

ENQ_TIME

TIMESTAMP(6)

ENQ_UID

VARCHAR2(30)

ENQ_TID

VARCHAR2(30)

DEQ_TIME

TIMESTAMP(6)

EEQ_UID

VARCHAR2(30)

DEQ_TID

VARCHAR2(30)

RETRY_COUNT

NUMBER

EXCEPTION_QSCHEMA

VARCHAR2(30)

EXCEPTION_QUEUE

VARCHAR2(30)

STEP_NO

NUMBER

RECIPIENT_KEY

NUMBER

DEQUEUE_MSGID

RAW(16)

SENDER_NAME

VARCHAR2(30)

SENDER_ADDRESS

VARCHAR2(1024)

SENDER_PROTOCOL

NUMBER

USER_DATA

ORDER_EVENT_TYP

USER_PROP

SYS.ANYDATA

SH Schema

This section lists the names of the scripts that create the Sales History (SH) schema and describes the objects in the schema. Table 4-27 lists the SH scripts in alphabetical order, while Table 4-28 lists its objects.

Table 4-27 SH Schema Scripts

Script Name

Description

sh_analz.sql

Gathers statistics on the schema objects

sh_comnt.sql

Creates comments for the objects in the schema

sh_cons.sql

Modifies constraints on objects in the schema

sh_cre.sql

Creates the objects in the schema

sh_cremv.sql

Creates materialized views and bitmapped indexes

sh_drop.sql

Drops schema SH and all its objects

sh_idx.sql

Creates indexes on tables in the schema

sh_main.sql

Main script for schema SH; calls other scripts

olp_v3.sql

Creates dimensions and hierarchies used by the OLAP server

sh_olp_d.sql

Drops the objects used by the OLAP server

Table 4-28 SH Objects

Object Type

Objects

Dimension

CHANNELS_DIM, CUSTOMERS_DIM, PRODUCTS_DIM, PROMOTIONS_DIM, TIMES_DIM

Index

CHANNELS_PK, COSTS_PROD_BIX, COSTS_TIME_BIX, COUNTRIES_PK, CUSTOMERS_GENDER_BIX, CUSTOMERS_MARITAL_BIX, CUSTOMERS_PK, CUSTOMERS_YOB_BIX, DR$SUP_TEXT_IDX$X, FW_PSC_S_MV_CHAN_BIX, FW_PSC_S_MV_PROMO_BIX, FW_PSC_S_MV_SUBCAT_BIX, FW_PSC_S_MV_WD_BIX, PRODUCTS_PK, PRODUCTS_PROD_CAT_IX, PRODUCTS_PROD_STATUS_BIX, PRODUCTS_PROD_SUBCAT_IX, PROMO_PK, SALES_CHANNEL_BIX, SALES_CUST_BIX, SALES_PROD_BIX, SALES_PROMO_BIX, SALES_TIME_BIX, SUP_TEXT_IDX, SYS_IOT_TOP_45927, SYS_IOT_TOP_45932, TIMES_PK

Index Partition

COSTS_PROD_BIX, COSTS_TIME_BIX, SALES_CHANNEL_BIX, SALES_CUST_BIX, SALES_PROD_BIX, SALES_PROMO_BIX, SALES_TIME_BIX

Lob

SYS_LOB0000045924C00006$$, SYS_LOB0000045929C00002$$

Materialized View

CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV

Table

CHANNELS, COSTS, COUNTRIES, CUSTOMERS, PRODUCTS, PROMOTIONS, SALES, TIMES

Table Partition

COSTS, SALES

View

PROFITS

SH Table Descriptions

This section describes the columns of each table of schema SH, as follows:

- Table 4-29, "SH.CHANNELS Table Description"

- Table 4-30, "SH.COSTS Table Description"

- Table 4-31, "SH.COUNTRIES Table Description"

- Table 4-32, "SH.CUSTOMERS Table Description"

- Table 4-33, "SH.PRODUCTS Table Description"

- Table 4-34, "SH.PROMOTIONS Table Description"

- Table 4-35, "SH.SALES Table Description"

- Table 4-36, "SH.TIMES Table Description"

Table 4-29 SH.CHANNELS Table Description

Column Name

Null?

Type

CHANNEL_ID

NOT NULL

NUMBER

CHANNEL_DESC

NOT NULL

VARCHAR2(20)

CHANNEL_CLASS

NOT NULL

VARCHAR2(20)

CHANNEL_CLASS_ID

NOT NULL

NUMBER

CHANNEL_TOTAL

NOT NULL

VARCHAR2(13)

CHANNEL_TOTAL_ID

NOT NULL

NUMBER

Table 4-30 SH.COSTS Table Description

Column Name

Null?

Type

PROD_ID

NOT NULL

NUMBER

TIME_DESC

NOT NULL

DATE

PROMO_ID

NOT NULL

NUMBER

CHANNEL_ID

NOT NULL

NUMBER

UNIT_COST

NOT NULL

NUMBER(10,2)

UNIT_PRICE

NOT NULL

NUMBER(10,2)

Table 4-31 SH.COUNTRIES Table Description

Column Name

Null?

Type

COUNTRY_ID

NOT NULL

NUMBER

COUNTRY_ISO_CODE

NOT NULL

CHAR(2)

COUNTRY_NAME

NOT NULL

VARCHAR2(40)

COUNTRY_SUBREGION

NOT NULL

VARCHAR2(30)

COUNTRY_SUBREGION_ID

NOT NULL

NUMBER

COUNTRY_REGION

NOT NULL

VARCHAR2(20)

COUNTRY_REGION_ID

NOT NULL

NUMBER

COUNTRY_TOTAL

NOT NULL

VARCHAR2(11)

COUNTRY_TOTAL_ID

NOT NULL

NUMBER

COUNTRY_NAME_HIST

VARCHAR2(40)

Table 4-32 SH.CUSTOMERS Table Description

Column Name

Null?

Type

CUST_ID

NOT NULL

NUMBER

CUST_FIRST_NAME

NOT NULL

VARCHAR2(20)

CUST_LAST_NAME

NOT NULL

VARCHAR2(40)

CUST_GENDER

NOT NULL

CHAR(1)

CUST_YEAR_OF_BIRTH

NOT NULL

NUMBER(4)

CUST_MARITAL_STATUS

VARCHAR2(20)

CUST_STREET_ADDRESS

NOT NULL

VARCHAR2(40)

CUST_POSTAL_CODE

NOT NULL

VARCHAR2(10)

CUST_CITY

NOT NULL

VARCHAR2(30)

CUST_CITY_ID

NOT NULL

NUMBER

CUST_STATE_PROVINCE

NOT NULL

VARCHAR2(40)

CUST_STATE_PROVINCE_ID

NOT NULL

NUMBER

COUNTRY_ID

NOT NULL

NUMBER

CUST_MAIN_PHONE_NUMBER

NOT NULL

VARCHAR2(25)

CUST_INCOME_LEVEL

VARCHAR2(30)

CUST_CREDIT_LIMIT

NUMBER

CUST_EMAIL

VARCHAR2(30)

CUST_TOTAL

NOT NULL

VARCHAR2(14)

CUST_TOTAL_ID

NOT NULL

NUMBER

CUST_SRC_ID

NUMBER

CUST_EFF_FROM

DATE

CUST_EFF_TO

DATE

CUST_VALID

VARCHAR2(1)

Table 4-33 SH.PRODUCTS Table Description

Column Name

Null?

Type

PROD_ID

NOT NULL

NUMBER(6)

PROD_NAME

NOT NULL

VARCHAR2(50)

PROD_DESC

NOT NULL

VARCHAR2(4000)

PROD_SUBCATEGORY

NOT NULL

VARCHAR2(50)

PROD_SUBCATEGORY_ID

NOT NULL

NUMBER

PROD_SUBCATEGORY_DESC

NOT NULL

VARCHAR2(2000)

PROD_CATEGORY

NOT NULL

VARCHAR2(50)

PRD_CATEGORY_ID

NOT NULL

NUMBER

PROD_CATEGORY_DESC

NOT NULL

VARCHAR2(2000)

PROD_WEIGHT_CLASS

NOT NULL

NUMBER(3)

PROD_UNIT_OF_MEASURE

VARCHAR2(20)

PRD_PACK_SIZE

NOT NULL

VARCHAR2(30)

PROD_SUPPLIER_ID

NOT NULL

NUMBER(6)

PROD_STATUS

NOT NULL

VARCHAR2(20)

PROD_LIST_PRICE

NOT NULL

NUMBER(8,2)

PRD_MIN_PRICE

NOT NULL

NUMBER(8,2)

PROD_TOTAL

NOT NULL

VARCHAR2(13)

PROD_TOTAL_ID

NOT NULL

NUMBER

PROD_SRC_ID

NUMBER

PRD_EFF_FROM

DATE

PROD_EFF_TO

DATE

PROD_VALID

VARCHAR2(1)

Table 4-34 SH.PROMOTIONS Table Description

Column Name

Null?

Type

PROMO_ID

NOT NULL

NUMBER(6)

PROMO_NAME

NOT NULL

VARCHAR2(30)

PROMO_SUBCATEGORY

NOT NULL

VARCHAR2(30)

PROMO_SUBCATEGORY_ID

NOT NULL

NUMBER

PROMO_CATEGORY

NOT NULL

VARCHAR2(30)

PRMO_CATEGORY_ID

NOT NULL

NUMBER

PROMO_COST

NOT NULL

NUMBER(10,2)

PROMO_BEGIN_DATE

NOT NULL

DATE

PROMO_END_DATE

NOT NULL

DATE

PROMO_TOTAL

NOT NULL

VARCHAR2(15)

PROMO_TOTAL_ID

NOT NULL

NUMBER

Table 4-35 SH.SALES Table Description

Column Name

Null?

Type

PROD_ID

NOT NULL

NUMBER

CUST_ID

NOT NULL

NUMBER

TIME_ID

NOT NULL

DATE

CHANNEL_ID

NOT NULL

NUMBER

PROMO_ID

NOT NULL

NUMBER

QUANTITY_SOLD

NOT NULL

NUMBER(10,2)

AMOUNT_SOLD

NOT NULL

NUMBER(10,2)

Table 4-36 SH.TIMES Table Description

Column Name

Null?

Type

TIME_ID

NOT NULL

DATE

DAY_NAME

NOT NULL

VARCHAR2(9)

DAY_NUMBER_IN_WEEK

NOT NULL

NUMBER(1)

DAY_NUMBER_IN_MONTH

NOT NULL

NUMBER(2)

CALENDAR_WEEK_NUMBER

NOT NULL

NUMBER(2)

FISCAL_WEEK_NUMBER

NOT NULL

NUMBER(2)

WEEK_ENDING_DAY

NOT NULL

DATE

WEEK_ENDING_DAY_ID

NOT NULL

NUMBER

CALENDAR_MONTH_NUMBER

NOT NULL

NUMBER(2)

FISCAL_MONTH_NUMBER

NOT NULL

NUMBER(2)

CALENDAR_MONTH_DESC

NOT NULL

VARCHAR2(8)

CALENDAR_MONTH_ID

NOT NULL

NUMBER

FISCAL_MONTH_DESC

NOT NULL

VARCHAR2(8)

FISCAL_MONTH_ID

NOT NULL

NUMBER

DAYS_IN_CAL_MONTH

NOT NULL

NUMBER

DAYS_IN_FIS_MONTH

NOT NULL

NUMBER

END_OF_CAL_MONTH

NOT NULL

DATE

END_OF_FIS_MONTH

NOT NULL

DATE

CALENDAR_MONTH_NAME

NOT NULL

VARCHAR2(9)

FISCAL_MONTH_NAME

NOT NULL

VARCHAR2(9)

CALENDAR_QUARTER_DESC

NOT NULL

CHAR(7)

CALENBDAR_QUARTER_ID

NOT NULL

NUMBER

FISCAL_QUARTER_DESC

NOT NULL

CHAR(7)

FISCAL_QUARTER_ID

NOT NULL

NUMBER

DAYS_IN_CAL_QUARTER

NOT NULL

NUMBER

DAYS_IN_FIS_QUARTER

NOT NULL

NUMBER

END_OF_CAL_QUARTER

NOT NULL

DATE

END_OF_FIS_QUARTER

NOT NULL

DATE

CALENDAR_QUARTER_NUMBER

NOT NULL

NUMBER(1)

FISCAL_QUARTER_NUMBER

NOT NULL

NUMBER(1)

CALENDAR_YEAR

NOT NULL

NUMBER(4)

CALENDAR_YEAR_ID

NOT NULL

NUMBER

FISCAL_YEAR

NOT NULL

NUMBER(4)

FISCAL_YEAR_ID

NOT NULL

NUMBER

DAYS_IN_CAL_YEAR

NOT NULL

NUMBER

DAYS_IN_FIS_YEAR

NOT NULL

NUMBER

END_OF_CAL_YEAR

NOT NULL

DATE

END_OF_FIS_YEAR

NOT NULL

DATE

A Purchase-Order XML Schema

This appendix includes a full listing of the purchase-order XML schema that is used to define the XML data in table OE.purchaseorder.

See Also:

- Oracle XML DB Developer's Guide for information about XML Schema

- Oracle XML DB Developer's Guide for information about using XML schemas with Oracle XML DB

Purchase-Order XML Schema: File purchaseOrder.xsd

Example A-1 is the complete listing of the XML schema used to define the structure of the data in table OE.purchaseorder. It is in file purchaseOrder.xsd.

Example A-1 XML Schema purchaseOrder.xsd

<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"
version="1.0" xdb:storeVarrayAsTable="true">
<xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/>
<xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
<xs:sequence>
<xs:element name="Reference" type="ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/>
<xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/>
<xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/>
<xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/>
<xs:element name="User" type="UserType" minOccurs="1" xdb:SQLName="USERID"/>
<xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/>
<xs:element name="ShippingInstructions" type="ShippingInstructionsType"
xdb:SQLName="SHIPPING_INSTRUCTIONS"/>
<xs:element name="SpecialInstructions" type="SpecialInstructionsType"
xdb:SQLName="SPECIAL_INSTRUCTIONS"/>
<xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
<xs:sequence>
<xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"
xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
<xs:sequence>
<xs:element name="Description" type="DescriptionType" xdb:SQLName="DESCRIPTION"/>
<xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
</xs:sequence>
<xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER"
xdb:SQLType="NUMBER"/>
</xs:complexType>
<xs:complexType name="PartType" xdb:SQLType="PART_T">
<xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="10"/>
<xs:maxLength value="14"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="Quantity" type="quantityType" xdb:SQLName="QUANTITY"/>
<xs:attribute name="UnitPrice" type="moneyType" xdb:SQLName="UNITPRICE"/>
</xs:complexType>
<xs:simpleType name="ReferenceType">
<xs:restriction base="xs:string">
<xs:minLength value="18"/>
<xs:maxLength value="30"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T">
<xs:sequence>
<xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V">
<xs:complexType xdb:SQLType="ACTION_T">
<xs:sequence>
<xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/>
<xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T">
<xs:all>
<xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/>
<xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/>
<xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/>
</xs:all>
</xs:complexType>
<xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T">
<xs:sequence>
<xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/>
<xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/>
<xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/>
</xs:sequence>
</xs:complexType>
<xs:simpleType name="moneyType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2"/>
<xs:totalDigits value="12"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="quantityType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="4"/>
<xs:totalDigits value="8"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="UserType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="10"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="RequestorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="128"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="CostCenterType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="4"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="VendorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="PurchaseOrderNumberType">
<xs:restriction base="xs:integer"/>
</xs:simpleType>
<xs:simpleType name="SpecialInstructionsType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="NameType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="AddressType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="TelephoneType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="24"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DateType">
<xs:restriction base="xs:date"/>
</xs:simpleType>
<xs:simpleType name="CommentsType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DescriptionType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>

Index
D  E  H  I  O  P  R  S

D

Database Configuration Assistant

using to install Sample Schemas, 2.1

dependencies among schemas, 2.2.1 DEPT table, 1

E

EMP table, 1

H

HR schema

general description, 1.4.1, 1.4.1

installing, 2.2.3

I

installation

manual, of Sample Schemas, 2.2

of IX schema, 2.2.6

of OC subschema, 2.2.4

of OE schema, 2.2.4

of PM schema, 2.2.5

of SH schema, 2.2.7

order of, 2.2.1

using Database Configuration Assistant, 2.1

installation of Sample Schemas, 2 IX schema

general description, 1.4.4, 1.4.4

installing, 2.2.6

scripts, 4.6

O

OC subschema

general description, 1.4.2.1

installing, 2.2.4

OE schema

general description, 1.4.2, 1.4.2

installing, 2.2.4

scripts, 4.4

P

PM schema

general description, 1.4.3, 1.4.3

installing, 2.2.5

scripts, 4.5

purchase-order XML schema, A.1 purchaseOrder.xsd XML schema, A.1

R

resetting the Sample Schemas, 2.3

S

Sample Schema

scripts

OE, 4.4

PM, 4.5

scripts, general information, 4.1 Sample Schemas

design principles, 1.2

general information, 1.1

installing, 2

scripts

master, 4.2

SCOTT schema, 1 SH schema

general description, 1.4.5

installing, 2.2.7

scripts, 4.7

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2015, Oracle and/or its affiliates. All rights reserved.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Alpha and Beta Draft Documentation Notice

If this document is in preproduction status:

This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.