5 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.
The script xx_drop.sql, where xx is the schema abbreviation, 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 5-1 lists the HR scripts in alphabetical order, while Table 5-2 lists its objects.

Table 5-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 the `HR` schema and all its objects
`hr_idx.sql`	Creates indexes on the `HR` tables
`hr_main.sql`	Main script for the `HR` schema; calls other scripts
`hr_popul.sql`	Populates the objects

Table 5-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 the HR schema, as follows:

Table 5-3, "HR.COUNTRIES Table Description"
Table 5-4, "HR.DEPARTMENTS Table Description"
Table 5-5, "HR.EMPLOYEES Table Description"
Table 5-6, "HR.JOBS Table Description"
Table 5-7, "HR.JOB_HISTORY Table Description"
Table 5-8, "HR.LOCATIONS Table Description"
Table 5-9, "HR.REGIONS Table Description"

Table 5-3 HR.COUNTRIES Table Description

Column Name	Null?	Type
`COUNTRY_ID`	`NOT NULL`	`CHAR(2)`
`COUNTRY_NAME`		`VARCHAR2(40)`
`REGION_ID`		`NUMBER`

Table 5-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 5-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 5-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 5-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 5-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 5-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) schema and describes the objects in the schema. Table 5-10 lists the OE scripts in alphabetical order, while Table 5-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 5-10 OE Schema Scripts

Script Name	Description
`oc_comnt.sql`	Adds comments to the online catalog (`OC`) subschema wherever possible
`oc_cre.sql`	Creates the `OC` subschema
`oc_drop.sql`	Drops the `OC` subschema
`oc_main.sql`	Main script for the `OC` subschema
`oc_popul.sql`^a	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 the `OE` schema 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 5-11 HR 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`, I`NVENTORY_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 columns of each table of the OE schema, as follows:

Table 5-12, "OE.CUSTOMERS Table Description"
Table 5-13, "OE.INVENTORIES Table Description"
Table 5-14, "OE.ORDERS Table Description"
Table 5-15, "OE.ORDER_ITEMS Table Description"
Table 5-16, "OE.PRODUCT_DESCRIPTIONS Table Description"
Table 5-17, "OE.PRODUCT_INFORMATION Table Description"
Table 5-18, "OE.WAREHOUSES Table Description"

Table 5-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 5-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 5-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 5-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 5-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 5-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 5-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`

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 5-19 lists the OE scripts in alphabetical order, while Table 5-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 5-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 the `PM` schema 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 the `PM` schema that calls other scripts

Table 5-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 the PM schema, as follows:

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

Table 5-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 5-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`		`NCLOBO`
`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 5-23 lists the IX scripts in alphabetical order, while Table 5-24 lists its objects.

Table 5-23 Information Exchange (IX) Schema Scripts

Script Name	Description
`cix_v3.sql`	Creates the `IX` schema objects
`dix_v3.sql`	Drops the `IX` schema objects
`ix_main.sql`	Main script for the `IX` schema; calls other scripts
`vix_v3.sql`	Enables, disables, and verifies `IX` objects

Table 5-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 the IX schema, as follows:

Table 5-25, "IX.ORDERS_QUEUETABLE Table Description"
Table 5-26, "IX.STREAMS_QUEUE_TABLE Table Description"

Table 5-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 5-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 5-27 lists the SH scripts in alphabetical order, while Table 5-28 lists its objects.

Table 5-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 the `SH` schema and all its objects
`sh_idx.sql`	Creates indexes on tables in the schema
`sh_main.sql`	Main script for the `SH` schema; 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 5-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 the IX schema, as follows:

Table 5-29, "SH.CHANNELS Table Description"
Table 5-30, "SH.COSTS Table Description"
Table 5-31, "SH.COUNTRIES Table Description"
Table 5-32, "SH.CUSTOMERS Table Description"
Table 5-33, "SH.PRODUCTS Table Description"
Table 5-34, "SH.PROMOTIONS Table Description"
Table 5-35, "SH.SALES Table Description"
Table 5-36, "SH.TIMES Table Description"

Table 5-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 5-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 5-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 5-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 5-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 5-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 5-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 5-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`