| Project | Oracle Demo Schema |
| Version | v090824 |
| Version Date | 2009-08-24 |
| Column | Data Type | Nullable | Default | PK / Index |
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49905 |
|
| SUBSCRIBER# | NUMBER | not null |
PK SYS_IOT_TOP_49905 |
|
| NAME | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49905 |
|
| ADDRESS# | NUMBER | not null |
PK SYS_IOT_TOP_49905 |
|
| SIGN | SYS.AQ$_SIG_PROP | null | ||
| DBS_SIGN | SYS.AQ$_SIG_PROP | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49903 |
|
| SUBSCRIBER# | NUMBER | not null |
PK SYS_IOT_TOP_49903 |
|
| NAME | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49903 |
|
| ADDRESS# | NUMBER | not null |
PK SYS_IOT_TOP_49903 |
|
| DEQUEUE_TIME | TIMESTAMP(6) | null | ||
| TRANSACTION_ID | VARCHAR2(30) | null | ||
| DEQUEUE_USER | VARCHAR2(30) | null | ||
| PROPAGATED_MSGID | RAW(16) | null | ||
| RETRY_COUNT | NUMBER | null | ||
| HINT | ROWID | null | ||
| SPARE | RAW(16) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| SUBSCRIBER# | NUMBER | not null |
PK SYS_IOT_TOP_49908 |
|
| NAME | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49908 |
|
| QUEUE# | NUMBER | not null |
PK SYS_IOT_TOP_49908 |
|
| MSG_ENQ_TIME | TIMESTAMP(6) | not null |
PK SYS_IOT_TOP_49908 |
|
| MSG_STEP_NO | NUMBER | not null |
PK SYS_IOT_TOP_49908 |
|
| MSG_CHAIN_NO | NUMBER | not null |
PK SYS_IOT_TOP_49908 |
|
| MSG_LOCAL_ORDER_NO | NUMBER | not null |
PK SYS_IOT_TOP_49908 |
|
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49908 |
|
| HINT | ROWID | null | ||
| SPARE | RAW(16) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| SUBSCRIBER_ID | NUMBER | not null |
PK SYS_C005062 |
|
| QUEUE_NAME | VARCHAR2(30) | not null | ||
| NAME | VARCHAR2(30) | null | ||
| ADDRESS | VARCHAR2(1024) | null | ||
| PROTOCOL | NUMBER | null | ||
| SUBSCRIBER_TYPE | NUMBER | null | ||
| RULE_NAME | VARCHAR2(30) | null | ||
| TRANS_NAME | VARCHAR2(61) | null | ||
| RULESET_NAME | VARCHAR2(65) | null | ||
| NEGATIVE_RULESET_NAME | VARCHAR2(65) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| NEXT_DATE | TIMESTAMP(6) | not null |
PK SYS_IOT_TOP_49901 |
|
| TXN_ID | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49901 |
|
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49901 |
|
| ACTION | NUMBER | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49922 |
|
| SUBSCRIBER# | NUMBER | not null |
PK SYS_IOT_TOP_49922 |
|
| NAME | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49922 |
|
| ADDRESS# | NUMBER | not null |
PK SYS_IOT_TOP_49922 |
|
| SIGN | SYS.AQ$_SIG_PROP | null | ||
| DBS_SIGN | SYS.AQ$_SIG_PROP | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49920 |
|
| SUBSCRIBER# | NUMBER | not null |
PK SYS_IOT_TOP_49920 |
|
| NAME | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49920 |
|
| ADDRESS# | NUMBER | not null |
PK SYS_IOT_TOP_49920 |
|
| DEQUEUE_TIME | TIMESTAMP(6) | null | ||
| TRANSACTION_ID | VARCHAR2(30) | null | ||
| DEQUEUE_USER | VARCHAR2(30) | null | ||
| PROPAGATED_MSGID | RAW(16) | null | ||
| RETRY_COUNT | NUMBER | null | ||
| HINT | ROWID | null | ||
| SPARE | RAW(16) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| SUBSCRIBER# | NUMBER | not null |
PK SYS_IOT_TOP_49925 |
|
| NAME | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49925 |
|
| QUEUE# | NUMBER | not null |
PK SYS_IOT_TOP_49925 |
|
| MSG_ENQ_TID | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49925 |
|
| SENDER# | NUMBER | not null |
PK SYS_IOT_TOP_49925 |
|
| TXN_STEP# | NUMBER | not null |
PK SYS_IOT_TOP_49925 |
|
| MSG_ENQ_TIME | TIMESTAMP(6) | not null |
PK SYS_IOT_TOP_49925 |
|
| MSG_STEP_NO | NUMBER | not null |
PK SYS_IOT_TOP_49925 |
|
| MSG_CHAIN_NO | NUMBER | not null |
PK SYS_IOT_TOP_49925 |
|
| MSG_LOCAL_ORDER_NO | NUMBER | not null |
PK SYS_IOT_TOP_49925 |
|
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49925 |
|
| HINT | ROWID | null | ||
| SPARE | RAW(16) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| SUBSCRIBER_ID | NUMBER | not null |
PK SYS_C005070 |
|
| QUEUE_NAME | VARCHAR2(30) | not null | ||
| NAME | VARCHAR2(30) | null | ||
| ADDRESS | VARCHAR2(1024) | null | ||
| PROTOCOL | NUMBER | null | ||
| SUBSCRIBER_TYPE | NUMBER | null | ||
| RULE_NAME | VARCHAR2(30) | null | ||
| TRANS_NAME | VARCHAR2(61) | null | ||
| RULESET_NAME | VARCHAR2(65) | null | ||
| NEGATIVE_RULESET_NAME | VARCHAR2(65) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| NEXT_DATE | TIMESTAMP(6) | not null |
PK SYS_IOT_TOP_49918 |
|
| TXN_ID | VARCHAR2(30) | not null |
PK SYS_IOT_TOP_49918 |
|
| MSGID | RAW(16) | not null |
PK SYS_IOT_TOP_49918 |
|
| ACTION | NUMBER | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| CALENDAR_MONTH_DESC | VARCHAR2(8) | not null | ||
| DOLLARS | NUMBER | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| CATEGORY_NAME | VARCHAR2(50) | null | ||
| CATEGORY_DESCRIPTION | VARCHAR2(1000) | null | ||
| CATEGORY_ID | NUMBER(2, 0) | not null |
PK SYS_C005251 |
|
| PARENT_CATEGORY_ID | NUMBER(2, 0) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| CHANNEL_ID | NUMBER | not null |
PK CHANNELS_PK |
|
| CHANNEL_DESC | VARCHAR2(20) | not null | ||
| CHANNEL_CLASS | VARCHAR2(20) | not null | ||
| CHANNEL_CLASS_ID | NUMBER | not null | ||
| CHANNEL_TOTAL | VARCHAR2(13) | not null | ||
| CHANNEL_TOTAL_ID | NUMBER | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| COSTS (SH) | CHANNEL_ID | CHANNEL_ID |
| SALES (SH) | CHANNEL_ID | CHANNEL_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PROD_ID | NUMBER | not null |
index COSTS_PROD_BIX |
|
| TIME_ID | DATE(7) | not null |
index COSTS_TIME_BIX |
|
| PROMO_ID | NUMBER | not null | ||
| CHANNEL_ID | NUMBER | not null | ||
| UNIT_COST | NUMBER(10, 2) | not null | ||
| UNIT_PRICE | NUMBER(10, 2) | not null |
| Foreign Key | Column | Referenced Column |
| CHANNELS (SH) | CHANNEL_ID | CHANNEL_ID |
| PRODUCTS (SH) | PROD_ID | PROD_ID |
| PROMOTIONS (SH) | PROMO_ID | PROMO_ID |
| TIMES (SH) | TIME_ID | TIME_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| COUNTRY_ID | NUMBER | not null |
PK COUNTRIES_PK |
|
| COUNTRY_ISO_CODE | CHAR(2) | not null | ||
| COUNTRY_NAME | VARCHAR2(40) | not null | ||
| COUNTRY_SUBREGION | VARCHAR2(30) | not null | ||
| COUNTRY_SUBREGION_ID | NUMBER | not null | ||
| COUNTRY_REGION | VARCHAR2(20) | not null | ||
| COUNTRY_REGION_ID | NUMBER | not null | ||
| COUNTRY_TOTAL | VARCHAR2(11) | not null | ||
| COUNTRY_TOTAL_ID | NUMBER | not null | ||
| COUNTRY_NAME_HIST | VARCHAR2(40) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| CUSTOMERS (SH) | COUNTRY_ID | COUNTRY_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| COUNTRY_ID | CHAR(2) | not null |
PK COUNTRY_C_ID_PK |
|
| COUNTRY_NAME | VARCHAR2(40) | null | ||
| REGION_ID | NUMBER | null |
| Foreign Key | Column | Referenced Column |
| REGIONS (HR) | REGION_ID | REGION_ID |
| Detail Table | Column | Referencing Column |
| LOCATIONS (HR) | COUNTRY_ID | COUNTRY_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| CUSTOMER_ID | NUMBER(6, 0) | not null |
PK CUSTOMERS_PK |
|
| CUST_FIRST_NAME | VARCHAR2(20) | not null | ||
| CUST_LAST_NAME | VARCHAR2(20) | not null |
index CUST_LNAME_IX |
|
| CUST_ADDRESS | OE.CUST_ADDRESS_TYP | null | ||
| PHONE_NUMBERS | OE.PHONE_LIST_TYP | null | ||
| NLS_LANGUAGE | VARCHAR2(3) | null | ||
| NLS_TERRITORY | VARCHAR2(30) | null | ||
| CREDIT_LIMIT | NUMBER(9, 2) | null | ||
| CUST_EMAIL | VARCHAR2(30) | null |
index CUST_EMAIL_IX |
|
| ACCOUNT_MGR_ID | NUMBER(6, 0) | null |
index CUST_ACCOUNT_MANAGER_IX |
|
| CUST_GEO_LOCATION | MDSYS.SDO_GEOMETRY | null | ||
| DATE_OF_BIRTH | DATE(7) | null | ||
| MARITAL_STATUS | VARCHAR2(20) | null | ||
| GENDER | VARCHAR2(1) | null | ||
| INCOME_LEVEL | VARCHAR2(20) | null |
| Foreign Key | Column | Referenced Column |
| EMPLOYEES (HR) | ACCOUNT_MGR_ID | EMPLOYEE_ID |
| Detail Table | Column | Referencing Column |
| ORDERS (OE) | CUSTOMER_ID | CUSTOMER_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| CUST_ID | NUMBER | not null |
PK CUSTOMERS_PK |
|
| CUST_FIRST_NAME | VARCHAR2(20) | not null | ||
| CUST_LAST_NAME | VARCHAR2(40) | not null | ||
| CUST_GENDER | CHAR(1) | not null |
index CUSTOMERS_GENDER_BIX |
|
| CUST_YEAR_OF_BIRTH | NUMBER(4, 0) | not null |
index CUSTOMERS_YOB_BIX |
|
| CUST_MARITAL_STATUS | VARCHAR2(20) | null |
index CUSTOMERS_MARITAL_BIX |
|
| CUST_STREET_ADDRESS | VARCHAR2(40) | not null | ||
| CUST_POSTAL_CODE | VARCHAR2(10) | not null | ||
| CUST_CITY | VARCHAR2(30) | not null | ||
| CUST_CITY_ID | NUMBER | not null | ||
| CUST_STATE_PROVINCE | VARCHAR2(40) | not null | ||
| CUST_STATE_PROVINCE_ID | NUMBER | not null | ||
| COUNTRY_ID | NUMBER | not null | ||
| CUST_MAIN_PHONE_NUMBER | VARCHAR2(25) | not null | ||
| CUST_INCOME_LEVEL | VARCHAR2(30) | null | ||
| CUST_CREDIT_LIMIT | NUMBER | null | ||
| CUST_EMAIL | VARCHAR2(30) | null | ||
| CUST_TOTAL | VARCHAR2(14) | not null | ||
| CUST_TOTAL_ID | NUMBER | not null | ||
| CUST_SRC_ID | NUMBER | null | ||
| CUST_EFF_FROM | DATE(7) | null | ||
| CUST_EFF_TO | DATE(7) | null | ||
| CUST_VALID | VARCHAR2(1) | null |
| Foreign Key | Column | Referenced Column |
| COUNTRIES (SH) | COUNTRY_ID | COUNTRY_ID |
| Detail Table | Column | Referencing Column |
| SALES (SH) | CUST_ID | CUST_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| DEPARTMENT_ID | NUMBER(4, 0) | not null |
PK DEPT_ID_PK |
|
| DEPARTMENT_NAME | VARCHAR2(30) | not null | ||
| MANAGER_ID | NUMBER(6, 0) | null | ||
| LOCATION_ID | NUMBER(4, 0) | null |
index DEPT_LOCATION_IX |
| Foreign Key | Column | Referenced Column |
| LOCATIONS (HR) | LOCATION_ID | LOCATION_ID |
| EMPLOYEES (HR) | MANAGER_ID | EMPLOYEE_ID |
| Detail Table | Column | Referencing Column |
| EMPLOYEES (HR) | DEPARTMENT_ID | DEPARTMENT_ID |
| JOB_HISTORY (HR) | DEPARTMENT_ID | DEPARTMENT_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| TOKEN_TEXT | VARCHAR2(64) | not null |
index DR$SUP_TEXT_IDX$X |
|
| TOKEN_TYPE | NUMBER(3, 0) | not null |
index DR$SUP_TEXT_IDX$X |
|
| TOKEN_FIRST | NUMBER(10, 0) | not null |
index DR$SUP_TEXT_IDX$X |
|
| TOKEN_LAST | NUMBER(10, 0) | not null |
index DR$SUP_TEXT_IDX$X |
|
| TOKEN_COUNT | NUMBER(10, 0) | not null |
index DR$SUP_TEXT_IDX$X |
|
| TOKEN_INFO | BLOB(4000) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| DOCID | NUMBER(38, 0) | null | ||
| TEXTKEY | ROWID | not null |
PK SYS_IOT_TOP_50215 |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| NLT_DOCID | NUMBER(38, 0) | not null |
PK SYS_IOT_TOP_50220 |
|
| NLT_MARK | CHAR(1) | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| ROW_NO | NUMBER(3, 0) | null | ||
| DATA | BLOB(4000) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| EMPLOYEE_ID | NUMBER(6, 0) | not null |
PK EMP_EMP_ID_PK |
|
| FIRST_NAME | VARCHAR2(20) | null |
index EMP_NAME_IX |
|
| LAST_NAME | VARCHAR2(25) | not null |
index EMP_NAME_IX |
|
| VARCHAR2(25) | not null |
index EMP_EMAIL_UK |
||
| PHONE_NUMBER | VARCHAR2(20) | null | ||
| HIRE_DATE | DATE(7) | not null | ||
| JOB_ID | VARCHAR2(10) | not null |
index EMP_JOB_IX |
|
| SALARY | NUMBER(8, 2) | null | ||
| COMMISSION_PCT | NUMBER(2, 2) | null | ||
| MANAGER_ID | NUMBER(6, 0) | null |
index EMP_MANAGER_IX |
|
| DEPARTMENT_ID | NUMBER(4, 0) | null |
index EMP_DEPARTMENT_IX |
| Foreign Key | Column | Referenced Column |
| DEPARTMENTS (HR) | DEPARTMENT_ID | DEPARTMENT_ID |
| JOBS (HR) | JOB_ID | JOB_ID |
| EMPLOYEES (HR) | MANAGER_ID | EMPLOYEE_ID |
| Detail Table | Column | Referencing Column |
| CUSTOMERS (OE) | EMPLOYEE_ID | ACCOUNT_MGR_ID |
| DEPARTMENTS (HR) | EMPLOYEE_ID | MANAGER_ID |
| EMPLOYEES (HR) | EMPLOYEE_ID | MANAGER_ID |
| JOB_HISTORY (HR) | EMPLOYEE_ID | EMPLOYEE_ID |
| ORDERS (OE) | EMPLOYEE_ID | SALES_REP_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| WEEK_ENDING_DAY | DATE(7) | not null |
index FW_PSC_S_MV_WD_BIX |
|
| PROD_SUBCATEGORY | VARCHAR2(50) | not null |
index FW_PSC_S_MV_SUBCAT_BIX |
|
| DOLLARS | NUMBER | null | ||
| CHANNEL_ID | NUMBER | not null |
index FW_PSC_S_MV_CHAN_BIX |
|
| PROMO_ID | NUMBER | not null |
index FW_PSC_S_MV_PROMO_BIX |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PRODUCT_ID | NUMBER(6, 0) | not null |
PK INVENTORY_IX index INV_PRODUCT_IX |
|
| WAREHOUSE_ID | NUMBER(3, 0) | not null |
PK INVENTORY_IX |
|
| QUANTITY_ON_HAND | NUMBER(8, 0) | not null |
| Foreign Key | Column | Referenced Column |
| PRODUCT_INFORMATION (OE) | PRODUCT_ID | PRODUCT_ID |
| WAREHOUSES (OE) | WAREHOUSE_ID | WAREHOUSE_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| EMPLOYEE_ID | NUMBER(6, 0) | not null |
PK JHIST_EMP_ID_ST_DATE_PK index JHIST_EMPLOYEE_IX |
|
| START_DATE | DATE(7) | not null |
PK JHIST_EMP_ID_ST_DATE_PK |
|
| END_DATE | DATE(7) | not null | ||
| JOB_ID | VARCHAR2(10) | not null |
index JHIST_JOB_IX |
|
| DEPARTMENT_ID | NUMBER(4, 0) | null |
index JHIST_DEPARTMENT_IX |
| Foreign Key | Column | Referenced Column |
| DEPARTMENTS (HR) | DEPARTMENT_ID | DEPARTMENT_ID |
| EMPLOYEES (HR) | EMPLOYEE_ID | EMPLOYEE_ID |
| JOBS (HR) | JOB_ID | JOB_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| JOB_ID | VARCHAR2(10) | not null |
PK JOB_ID_PK |
|
| JOB_TITLE | VARCHAR2(35) | not null | ||
| MIN_SALARY | NUMBER(6, 0) | null | ||
| MAX_SALARY | NUMBER(6, 0) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| EMPLOYEES (HR) | JOB_ID | JOB_ID |
| JOB_HISTORY (HR) | JOB_ID | JOB_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| LOCATION_ID | NUMBER(4, 0) | not null |
PK LOC_ID_PK |
|
| STREET_ADDRESS | VARCHAR2(40) | null | ||
| POSTAL_CODE | VARCHAR2(12) | null | ||
| CITY | VARCHAR2(30) | not null |
index LOC_CITY_IX |
|
| STATE_PROVINCE | VARCHAR2(25) | null |
index LOC_STATE_PROVINCE_IX |
|
| COUNTRY_ID | CHAR(2) | null |
index LOC_COUNTRY_IX |
| Foreign Key | Column | Referenced Column |
| COUNTRIES (HR) | COUNTRY_ID | COUNTRY_ID |
| Detail Table | Column | Referencing Column |
| DEPARTMENTS (HR) | LOCATION_ID | LOCATION_ID |
| WAREHOUSES (OE) | LOCATION_ID | LOCATION_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| OWNER | VARCHAR2(30) | not null | ||
| TABLE_NAME | VARCHAR2(30) | not null | ||
| DIMENSION_NAME | VARCHAR2(30) | not null | ||
| RELATIONSHIP | VARCHAR2(11) | not null | ||
| BAD_ROWID | ROWID | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PRODUCT_ID | NUMBER(6, 0) | not null |
PK ONLINEMEDIA_PK |
|
| PRODUCT_PHOTO | ORDSYS.ORDIMAGE | null | ||
| PRODUCT_PHOTO_SIGNATURE | ORDSYS.ORDIMAGESIGNATURE | null | ||
| PRODUCT_THUMBNAIL | ORDSYS.ORDIMAGE | null | ||
| PRODUCT_VIDEO | ORDSYS.ORDVIDEO | null | ||
| PRODUCT_AUDIO | ORDSYS.ORDAUDIO | null | ||
| PRODUCT_TEXT | CLOB(4000) | null | ||
| PRODUCT_TESTIMONIALS | ORDSYS.ORDDOC | null |
| Foreign Key | Column | Referenced Column |
| PRODUCT_INFORMATION (OE) | PRODUCT_ID | PRODUCT_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| ORDER_ID | NUMBER(12, 0) | not null |
PK ORDER_ITEMS_PK index ITEM_ORDER_IX index ORDER_ITEMS_UK |
|
| LINE_ITEM_ID | NUMBER(3, 0) | not null |
PK ORDER_ITEMS_PK |
|
| PRODUCT_ID | NUMBER(6, 0) | not null |
index ITEM_PRODUCT_IX index ORDER_ITEMS_UK |
|
| UNIT_PRICE | NUMBER(8, 2) | null | ||
| QUANTITY | NUMBER(8, 0) | null |
| Foreign Key | Column | Referenced Column |
| ORDERS (OE) | ORDER_ID | ORDER_ID |
| PRODUCT_INFORMATION (OE) | PRODUCT_ID | PRODUCT_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| ORDER_ID | NUMBER(12, 0) | not null |
PK ORDER_PK |
|
| ORDER_DATE | TIMESTAMP(6) WITH LOCAL TIME ZONE | not null |
index ORD_ORDER_DATE_IX |
|
| ORDER_MODE | VARCHAR2(8) | null | ||
| CUSTOMER_ID | NUMBER(6, 0) | not null |
index ORD_CUSTOMER_IX |
|
| ORDER_STATUS | NUMBER(2, 0) | null | ||
| ORDER_TOTAL | NUMBER(8, 2) | null | ||
| SALES_REP_ID | NUMBER(6, 0) | null |
index ORD_SALES_REP_IX |
|
| PROMOTION_ID | NUMBER(6, 0) | null |
| Foreign Key | Column | Referenced Column |
| CUSTOMERS (OE) | CUSTOMER_ID | CUSTOMER_ID |
| EMPLOYEES (HR) | SALES_REP_ID | EMPLOYEE_ID |
| Detail Table | Column | Referencing Column |
| ORDER_ITEMS (OE) | ORDER_ID | ORDER_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| Q_NAME | VARCHAR2(30) | null | ||
| MSGID | RAW(16) | not null |
PK SYS_C005059 |
|
| CORRID | VARCHAR2(128) | null | ||
| PRIORITY | NUMBER | null | ||
| STATE | NUMBER | null | ||
| DELAY | TIMESTAMP(6) | null | ||
| EXPIRATION | NUMBER | null | ||
| TIME_MANAGER_INFO | TIMESTAMP(6) | null | ||
| LOCAL_ORDER_NO | NUMBER | null | ||
| CHAIN_NO | NUMBER | null | ||
| CSCN | NUMBER | null | ||
| DSCN | NUMBER | null | ||
| ENQ_TIME | TIMESTAMP(6) | null | ||
| ENQ_UID | VARCHAR2(30) | null | ||
| ENQ_TID | VARCHAR2(30) | null | ||
| DEQ_TIME | TIMESTAMP(6) | null | ||
| DEQ_UID | VARCHAR2(30) | null | ||
| DEQ_TID | VARCHAR2(30) | null | ||
| RETRY_COUNT | NUMBER | null | ||
| EXCEPTION_QSCHEMA | VARCHAR2(30) | null | ||
| EXCEPTION_QUEUE | VARCHAR2(30) | null | ||
| STEP_NO | NUMBER | null | ||
| RECIPIENT_KEY | NUMBER | null | ||
| DEQUEUE_MSGID | RAW(16) | null | ||
| SENDER_NAME | VARCHAR2(30) | null | ||
| SENDER_ADDRESS | VARCHAR2(1024) | null | ||
| SENDER_PROTOCOL | NUMBER | null | ||
| USER_DATA | IX.ORDER_EVENT_TYP | null | ||
| USER_PROP | SYS.ANYDATA | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PRODUCT_ID | NUMBER(6, 0) | not null |
PK PRINTMEDIA_PK |
|
| AD_ID | NUMBER(6, 0) | not null |
PK PRINTMEDIA_PK |
|
| AD_COMPOSITE | BLOB(4000) | null | ||
| AD_SOURCETEXT | CLOB(4000) | null | ||
| AD_FINALTEXT | CLOB(4000) | null | ||
| AD_FLTEXTN | NCLOB(4000) | null | ||
| AD_TEXTDOCS_NTAB | PM.TEXTDOC_TAB | null |
index SYS_C005057 |
|
| AD_PHOTO | BLOB(4000) | null | ||
| AD_GRAPHIC | BFILE(530) | null | ||
| AD_HEADER | PM.ADHEADER_TYP | null |
| Foreign Key | Column | Referenced Column |
| PRODUCT_INFORMATION (OE) | PRODUCT_ID | PRODUCT_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PRODUCT_ID | NUMBER(6, 0) | not null |
PK PRD_DESC_PK |
|
| LANGUAGE_ID | VARCHAR2(3) | not null |
PK PRD_DESC_PK |
|
| TRANSLATED_NAME | NVARCHAR2(150) | not null |
index PROD_NAME_IX |
|
| TRANSLATED_DESCRIPTION | NVARCHAR2(4000) | not null |
| Foreign Key | Column | Referenced Column |
| PRODUCT_INFORMATION (OE) | PRODUCT_ID | PRODUCT_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PRODUCT_ID | NUMBER(6, 0) | not null |
PK PRODUCT_INFORMATION_PK |
|
| PRODUCT_NAME | VARCHAR2(50) | null | ||
| PRODUCT_DESCRIPTION | VARCHAR2(2000) | null | ||
| CATEGORY_ID | NUMBER(2, 0) | null | ||
| WEIGHT_CLASS | NUMBER(1, 0) | null | ||
| WARRANTY_PERIOD | INTERVAL YEAR(2) TO MONTH | null | ||
| SUPPLIER_ID | NUMBER(6, 0) | null |
index PROD_SUPPLIER_IX |
|
| PRODUCT_STATUS | VARCHAR2(20) | null | ||
| LIST_PRICE | NUMBER(8, 2) | null | ||
| MIN_PRICE | NUMBER(8, 2) | null | ||
| CATALOG_URL | VARCHAR2(50) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| INVENTORIES (OE) | PRODUCT_ID | PRODUCT_ID |
| ONLINE_MEDIA (PM) | PRODUCT_ID | PRODUCT_ID |
| ORDER_ITEMS (OE) | PRODUCT_ID | PRODUCT_ID |
| PRODUCT_DESCRIPTIONS (OE) | PRODUCT_ID | PRODUCT_ID |
| PRINT_MEDIA (PM) | PRODUCT_ID | PRODUCT_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PROD_ID | NUMBER(6, 0) | not null |
PK PRODUCTS_PK |
|
| PROD_NAME | VARCHAR2(50) | not null | ||
| PROD_DESC | VARCHAR2(4000) | not null | ||
| PROD_SUBCATEGORY | VARCHAR2(50) | not null |
index PRODUCTS_PROD_SUBCAT_IX |
|
| PROD_SUBCATEGORY_ID | NUMBER | not null | ||
| PROD_SUBCATEGORY_DESC | VARCHAR2(2000) | not null | ||
| PROD_CATEGORY | VARCHAR2(50) | not null |
index PRODUCTS_PROD_CAT_IX |
|
| PROD_CATEGORY_ID | NUMBER | not null | ||
| PROD_CATEGORY_DESC | VARCHAR2(2000) | not null | ||
| PROD_WEIGHT_CLASS | NUMBER(3, 0) | not null | ||
| PROD_UNIT_OF_MEASURE | VARCHAR2(20) | null | ||
| PROD_PACK_SIZE | VARCHAR2(30) | not null | ||
| SUPPLIER_ID | NUMBER(6, 0) | not null | ||
| PROD_STATUS | VARCHAR2(20) | not null |
index PRODUCTS_PROD_STATUS_BIX |
|
| PROD_LIST_PRICE | NUMBER(8, 2) | not null | ||
| PROD_MIN_PRICE | NUMBER(8, 2) | not null | ||
| PROD_TOTAL | VARCHAR2(13) | not null | ||
| PROD_TOTAL_ID | NUMBER | not null | ||
| PROD_SRC_ID | NUMBER | null | ||
| PROD_EFF_FROM | DATE(7) | null | ||
| PROD_EFF_TO | DATE(7) | null | ||
| PROD_VALID | VARCHAR2(1) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| COSTS (SH) | PROD_ID | PROD_ID |
| SALES (SH) | PROD_ID | PROD_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PROMO_ID | NUMBER(6, 0) | not null |
PK PROMO_PK |
|
| PROMO_NAME | VARCHAR2(30) | not null | ||
| PROMO_SUBCATEGORY | VARCHAR2(30) | not null | ||
| PROMO_SUBCATEGORY_ID | NUMBER | not null | ||
| PROMO_CATEGORY | VARCHAR2(30) | not null | ||
| PROMO_CATEGORY_ID | NUMBER | not null | ||
| PROMO_COST | NUMBER(10, 2) | not null | ||
| PROMO_BEGIN_DATE | DATE(7) | not null | ||
| PROMO_END_DATE | DATE(7) | not null | ||
| PROMO_TOTAL | VARCHAR2(15) | not null | ||
| PROMO_TOTAL_ID | NUMBER | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| COSTS (SH) | PROMO_ID | PROMO_ID |
| SALES (SH) | PROMO_ID | PROMO_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PROMO_ID | NUMBER(6, 0) | not null |
PK PROMO_ID_PK |
|
| PROMO_NAME | VARCHAR2(20) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| SYS_NC_ROWINFO$ | SYS.XMLTYPE | null |
| Foreign Key | Column | Referenced Column |
| () |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| REGION_ID | NUMBER | not null |
PK REG_ID_PK |
|
| REGION_NAME | VARCHAR2(25) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| COUNTRIES (HR) | REGION_ID | REGION_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PROD_ID | NUMBER | not null |
index SALES_PROD_BIX |
|
| CUST_ID | NUMBER | not null |
index SALES_CUST_BIX |
|
| TIME_ID | DATE(7) | not null |
index SALES_TIME_BIX |
|
| CHANNEL_ID | NUMBER | not null |
index SALES_CHANNEL_BIX |
|
| PROMO_ID | NUMBER | not null |
index SALES_PROMO_BIX |
|
| QUANTITY_SOLD | NUMBER(10, 2) | not null | ||
| AMOUNT_SOLD | NUMBER(10, 2) | not null |
| Foreign Key | Column | Referenced Column |
| CHANNELS (SH) | CHANNEL_ID | CHANNEL_ID |
| CUSTOMERS (SH) | CUST_ID | CUST_ID |
| PRODUCTS (SH) | PROD_ID | PROD_ID |
| PROMOTIONS (SH) | PROMO_ID | PROMO_ID |
| TIMES (SH) | TIME_ID | TIME_ID |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| PROD_ID | NUMBER | null | ||
| CUST_ID | NUMBER | null | ||
| TIME_ID | DATE(7) | null | ||
| CHANNEL_ID | NUMBER | null | ||
| PROMO_ID | NUMBER | null | ||
| QUANTITY_SOLD | NUMBER | null | ||
| AMOUNT_SOLD | NUMBER(10, 2) | null | ||
| UNIT_COST | NUMBER(10, 2) | null | ||
| UNIT_PRICE | NUMBER(10, 2) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| Q_NAME | VARCHAR2(30) | null | ||
| MSGID | RAW(16) | not null |
PK SYS_C005067 |
|
| CORRID | VARCHAR2(128) | null | ||
| PRIORITY | NUMBER | null | ||
| STATE | NUMBER | null | ||
| DELAY | TIMESTAMP(6) | null | ||
| EXPIRATION | NUMBER | null | ||
| TIME_MANAGER_INFO | TIMESTAMP(6) | null | ||
| LOCAL_ORDER_NO | NUMBER | null | ||
| CHAIN_NO | NUMBER | null | ||
| CSCN | NUMBER | null | ||
| DSCN | NUMBER | null | ||
| ENQ_TIME | TIMESTAMP(6) | null | ||
| ENQ_UID | VARCHAR2(30) | null | ||
| ENQ_TID | VARCHAR2(30) | null | ||
| DEQ_TIME | TIMESTAMP(6) | null | ||
| DEQ_UID | VARCHAR2(30) | null | ||
| DEQ_TID | VARCHAR2(30) | null | ||
| RETRY_COUNT | NUMBER | null | ||
| EXCEPTION_QSCHEMA | VARCHAR2(30) | null | ||
| EXCEPTION_QUEUE | VARCHAR2(30) | null | ||
| STEP_NO | NUMBER | null | ||
| RECIPIENT_KEY | NUMBER | null | ||
| DEQUEUE_MSGID | RAW(16) | null | ||
| SENDER_NAME | VARCHAR2(30) | null | ||
| SENDER_ADDRESS | VARCHAR2(1024) | null | ||
| SENDER_PROTOCOL | NUMBER | null | ||
| USER_DATA | SYS.ANYDATA | null | ||
| USER_PROP | SYS.ANYDATA | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| ID | NUMBER | null | ||
| STYLESHEET | OE.XMLTYPE | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| CUST_ID | NUMBER | not null | ||
| EDUCATION | VARCHAR2(21) | null | ||
| OCCUPATION | VARCHAR2(21) | null | ||
| HOUSEHOLD_SIZE | VARCHAR2(21) | null | ||
| YRS_RESIDENCE | NUMBER | null | ||
| AFFINITY_CARD | NUMBER(10, 0) | null | ||
| BULK_PACK_DISKETTES | NUMBER(10, 0) | null | ||
| FLAT_PANEL_MONITOR | NUMBER(10, 0) | null | ||
| HOME_THEATER_PACKAGE | NUMBER(10, 0) | null | ||
| BOOKKEEPING_APPLICATION | NUMBER(10, 0) | null | ||
| PRINTER_SUPPLIES | NUMBER(10, 0) | null | ||
| Y_BOX_GAMES | NUMBER(10, 0) | null | ||
| OS_DOC_SET_KANJI | NUMBER(10, 0) | null | ||
| COMMENTS | VARCHAR2(4000) | null |
index SUP_TEXT_IDX |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| TIME_ID | DATE(7) | not null |
PK TIMES_PK |
|
| DAY_NAME | VARCHAR2(9) | not null | ||
| DAY_NUMBER_IN_WEEK | NUMBER(1, 0) | not null | ||
| DAY_NUMBER_IN_MONTH | NUMBER(2, 0) | not null | ||
| CALENDAR_WEEK_NUMBER | NUMBER(2, 0) | not null | ||
| FISCAL_WEEK_NUMBER | NUMBER(2, 0) | not null | ||
| WEEK_ENDING_DAY | DATE(7) | not null | ||
| WEEK_ENDING_DAY_ID | NUMBER | not null | ||
| CALENDAR_MONTH_NUMBER | NUMBER(2, 0) | not null | ||
| FISCAL_MONTH_NUMBER | NUMBER(2, 0) | not null | ||
| CALENDAR_MONTH_DESC | VARCHAR2(8) | not null | ||
| CALENDAR_MONTH_ID | NUMBER | not null | ||
| FISCAL_MONTH_DESC | VARCHAR2(8) | not null | ||
| FISCAL_MONTH_ID | NUMBER | not null | ||
| DAYS_IN_CAL_MONTH | NUMBER | not null | ||
| DAYS_IN_FIS_MONTH | NUMBER | not null | ||
| END_OF_CAL_MONTH | DATE(7) | not null | ||
| END_OF_FIS_MONTH | DATE(7) | not null | ||
| CALENDAR_MONTH_NAME | VARCHAR2(9) | not null | ||
| FISCAL_MONTH_NAME | VARCHAR2(9) | not null | ||
| CALENDAR_QUARTER_DESC | CHAR(7) | not null | ||
| CALENDAR_QUARTER_ID | NUMBER | not null | ||
| FISCAL_QUARTER_DESC | CHAR(7) | not null | ||
| FISCAL_QUARTER_ID | NUMBER | not null | ||
| DAYS_IN_CAL_QUARTER | NUMBER | not null | ||
| DAYS_IN_FIS_QUARTER | NUMBER | not null | ||
| END_OF_CAL_QUARTER | DATE(7) | not null | ||
| END_OF_FIS_QUARTER | DATE(7) | not null | ||
| CALENDAR_QUARTER_NUMBER | NUMBER(1, 0) | not null | ||
| FISCAL_QUARTER_NUMBER | NUMBER(1, 0) | not null | ||
| CALENDAR_YEAR | NUMBER(4, 0) | not null | ||
| CALENDAR_YEAR_ID | NUMBER | not null | ||
| FISCAL_YEAR | NUMBER(4, 0) | not null | ||
| FISCAL_YEAR_ID | NUMBER | not null | ||
| DAYS_IN_CAL_YEAR | NUMBER | not null | ||
| DAYS_IN_FIS_YEAR | NUMBER | not null | ||
| END_OF_CAL_YEAR | DATE(7) | not null | ||
| END_OF_FIS_YEAR | DATE(7) | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| COSTS (SH) | TIME_ID | TIME_ID |
| SALES (SH) | TIME_ID | TIME_ID |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| WAREHOUSE_ID | NUMBER(3, 0) | not null |
PK WAREHOUSES_PK |
|
| WAREHOUSE_SPEC | SYS.XMLTYPE | null | ||
| WAREHOUSE_NAME | VARCHAR2(35) | null | ||
| LOCATION_ID | NUMBER(4, 0) | null |
index WHS_LOCATION_IX |
|
| WH_GEO_LOCATION | MDSYS.SDO_GEOMETRY | null |
| Foreign Key | Column | Referenced Column |
| LOCATIONS (HR) | LOCATION_ID | LOCATION_ID |
| Detail Table | Column | Referencing Column |
| INVENTORIES (OE) | WAREHOUSE_ID | WAREHOUSE_ID |
| Triggers |
CREATE OR REPLACE VIEW OE.ACCOUNT_MANAGERS AS
SELECT c.account_mgr_id ACCT_MGR,
cr.region_id REGION,
c.cust_address.country_id COUNTRY,
c.cust_address.state_province PROVINCE,
count(*) NUM_CUSTOMERS
FROM customers c, countries cr
WHERE c.cust_address.country_id = cr.country_id
GROUP BY ROLLUP (c.account_mgr_id,
cr.region_id,
c.cust_address.country_id,
c.cust_address.state_province)
CREATE OR REPLACE VIEW IX.AQ$ORDERS_QUEUETABLE AS
SELECT q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY, decode(h.dequeue_time, NULL,
(decode(state, 0, 'READY',
1, 'WAIT',
2, 'PROCESSED',
3, 'EXPIRED',
8, 'DEFERRED')),
(decode(h.transaction_id,
NULL, 'UNDELIVERABLE',
'PROCESSED'))) MSG_STATE, cast(FROM_TZ(qt.delay, '-08:00')
at time zone sessiontimezone as date) delay, delay DELAY_TIMESTAMP, expiration, cast(FROM_TZ(qt.enq_time, '-08:00')
at time zone sessiontimezone as date) enq_time, cast(FROM_TZ(qt.enq_time, '-08:00')
at time zone sessiontimezone as timestamp)
enq_timestamp, enq_uid ENQ_USER_ID, enq_tid ENQ_TXN_ID, decode(h.transaction_id, NULL, TO_DATE(NULL),
cast(FROM_TZ(h.dequeue_time, '-08:00')
CREATE OR REPLACE VIEW IX.AQ$ORDERS_QUEUETABLE_R AS SELECT queue_name QUEUE, s.name NAME , address ADDRESS , protocol PROTOCOL, rule_condition RULE, ruleset_name RULE_SET, trans_name TRANSFORMATION FROM "AQ$_ORDERS_QUEUETABLE_S" s , sys.all_rules r WHERE (bitand(s.subscriber_type, 1) = 1) AND s.rule_name = r.rule_name and r.rule_owner = 'IX' WITH READ ONLY
CREATE OR REPLACE VIEW IX.AQ$ORDERS_QUEUETABLE_S AS SELECT queue_name QUEUE, name NAME , address ADDRESS , protocol PROTOCOL, trans_name TRANSFORMATION FROM "AQ$_ORDERS_QUEUETABLE_S" s WHERE (bitand(s.subscriber_type, 1) = 1) WITH READ ONLY
CREATE OR REPLACE VIEW IX.AQ$STREAMS_QUEUE_TABLE AS
SELECT q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY, decode(h.dequeue_time, NULL,
(decode(state, 0, 'READY',
1, 'WAIT',
2, 'PROCESSED',
3, 'EXPIRED',
8, 'DEFERRED')),
(decode(h.transaction_id,
NULL, 'UNDELIVERABLE',
'PROCESSED'))) MSG_STATE, cast(FROM_TZ(qt.delay, '-08:00')
at time zone sessiontimezone as date) delay, delay DELAY_TIMESTAMP, expiration, cast(FROM_TZ(qt.enq_time, '-08:00')
at time zone sessiontimezone as date) enq_time, cast(FROM_TZ(qt.enq_time, '-08:00')
at time zone sessiontimezone as timestamp)
enq_timestamp, enq_uid ENQ_USER_ID, enq_tid ENQ_TXN_ID, decode(h.transaction_id, NULL, TO_DATE(NULL),
cast(FROM_TZ(h.dequeue_time, '-08:00')
CREATE OR REPLACE VIEW IX.AQ$STREAMS_QUEUE_TABLE_R AS SELECT queue_name QUEUE, s.name NAME , address ADDRESS , protocol PROTOCOL, rule_condition RULE, ruleset_name RULE_SET, trans_name TRANSFORMATION FROM "AQ$_STREAMS_QUEUE_TABLE_S" s , sys.all_rules r WHERE (bitand(s.subscriber_type, 1) = 1) AND s.rule_name = r.rule_name and r.rule_owner = 'IX' WITH READ ONLY
CREATE OR REPLACE VIEW IX.AQ$STREAMS_QUEUE_TABLE_S AS SELECT queue_name QUEUE, name NAME , address ADDRESS , protocol PROTOCOL, trans_name TRANSFORMATION FROM "AQ$_STREAMS_QUEUE_TABLE_S" s WHERE (bitand(s.subscriber_type, 1) = 1) WITH READ ONLY
CREATE OR REPLACE VIEW OE.BOMBAY_INVENTORY AS SELECT p.product_id , p.product_name , i.quantity_on_hand FROM inventories i , warehouses w , products p WHERE p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id AND w.warehouse_name = 'Bombay'
CREATE OR REPLACE VIEW OE.CUSTOMERS_VIEW AS SELECT c.customer_id, c.cust_first_name, c.cust_last_name, c.cust_address.street_address street_address, c.cust_address.postal_code postal_code, c.cust_address.city city, c.cust_address.state_province state_province, co.country_id, co.country_name, co.region_id, c.nls_language, c.nls_territory, c.credit_limit, c.cust_email, substr(get_phone_number_f(1,phone_numbers),1,25) Primary_Phone_number, substr(get_phone_number_f(2,phone_numbers),1,25) Phone_number_2, substr(get_phone_number_f(3,phone_numbers),1,25) Phone_number_3, substr(get_phone_number_f(4,phone_numbers),1,25) Phone_number_4, substr(get_phone_number_f(5,phone_numbers),1,25) Phone_number_5, c.account_mgr_id, c.cust_geo_location.sdo_gtype location_gtype, c.cust_geo_location.sdo_srid location_srid, c.cust_geo_location.sdo_point.x location_x, c.cust_geo_location.sdo_point.y location_y, c.cust_geo_location.sdo_point.z location_z FROM countries co, customers c WHERE c.cust_address.country_id = co.coun
CREATE OR REPLACE VIEW OE.DEPTVIEW AS
SELECT xmlelement("Department", xmlForest(department_name, location_id),
(SELECT sys_xmlagg(xmlelement("Employee",xmlForest(last_name,
job_id,manager_id,
hire_date,
salary,
commission_pct)
), xmlformat('EmployeeList'))
FROM employees e WHERE e.department_id = d.department_id
)) xml
FROM departments d
CREATE OR REPLACE VIEW HR.EMP_DETAILS_VIEW AS SELECT e.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name, e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province, c.country_name, r.region_name FROM employees e, departments d, jobs j, locations l, countries c, regions r WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id AND j.job_id = e.job_id WITH READ ONLY
CREATE OR REPLACE VIEW OE.OC_CORPORATE_CUSTOMERS AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name,
c.cust_address, c.phone_numbers,c.nls_language,c.nls_territory,
c.credit_limit, c.cust_email,
CAST(MULTISET(SELECT o.order_id, o.order_mode,
MAKE_REF(oc_customers,o.customer_id),
o.order_status,
o.order_total,o.sales_rep_id,
CAST(MULTISET(SELECT l.order_id,l.line_item_id,
l.unit_price,l.quantity,
make_ref(oc_product_information,
l.product_id)
FROM order_items l
WHERE o.order_id = l.order_id)
AS order_item_list_typ)
FROM orders o
WHERE c.customer_id = o.customer_id)
CREATE OR REPLACE VIEW OE.OC_CUSTOMERS AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name, c.cust_address,
c.phone_numbers,c.nls_language,c.nls_territory,c.credit_limit,
c.cust_email,
CAST(MULTISET(SELECT o.order_id, o.order_mode,
MAKE_REF(oc_customers,o.customer_id),
o.order_status,
o.order_total,o.sales_rep_id,
CAST(MULTISET(SELECT l.order_id,l.line_item_id,
l.unit_price,l.quantity,
MAKE_REF(oc_product_information,
l.product_id)
FROM order_items l
WHERE o.order_id = l.order_id)
AS order_item_list_typ)
FROM orders o
WHERE c.customer_id = o.customer_id)
CREATE OR REPLACE VIEW OE.OC_INVENTORIES AS
SELECT i.product_id,
warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),
i.quantity_on_hand
FROM inventories i, warehouses w
WHERE i.warehouse_id=w.warehouse_id
CREATE OR REPLACE VIEW OE.OC_ORDERS AS
SELECT o.order_id, o.order_mode,MAKE_REF(oc_customers,o.customer_id),
o.order_status,o.order_total,o.sales_rep_id,
CAST(MULTISET(SELECT l.order_id,l.line_item_id,l.unit_price,l.quantity,
make_ref(oc_product_information,l.product_id)
FROM order_items l
WHERE o.order_id = l.order_id)
AS order_item_list_typ)
FROM orders o
CREATE OR REPLACE VIEW OE.OC_PRODUCT_INFORMATION AS
SELECT p.product_id, p.product_name, p.product_description, p.category_id,
p.weight_class, p.warranty_period, p.supplier_id, p.product_status,
p.list_price, p.min_price, p.catalog_url,
CAST(MULTISET(SELECT i.product_id,i.warehouse,i.quantity_on_hand
FROM oc_inventories i
WHERE p.product_id=i.product_id)
AS inventory_list_typ)
FROM product_information p
CREATE OR REPLACE VIEW OE.ORDERS_VIEW AS SELECT order_id, TO_DATE(TO_CHAR(order_date,'DD-MON-YY HH:MI:SS'),'DD-MON-YY HH:MI:SS') ORDER_DATE, order_mode, customer_id, order_status, order_total, sales_rep_id, promotion_id FROM orders
CREATE OR REPLACE VIEW OE.PRODUCT_PRICES AS SELECT category_id , COUNT(*) as "#_OF_PRODUCTS" , MIN(list_price) as low_price , MAX(list_price) as high_price FROM product_information GROUP BY category_id
CREATE OR REPLACE VIEW OE.PRODUCTS AS
SELECT i.product_id
, d.language_id
, CASE WHEN d.language_id IS NOT NULL
THEN d.translated_name
ELSE TRANSLATE(i.product_name USING NCHAR_CS)
END AS product_name
, i.category_id
, CASE WHEN d.language_id IS NOT NULL
THEN d.translated_description
ELSE TRANSLATE(i.product_description USING NCHAR_CS)
END AS product_description
, i.weight_class
, i.warranty_period
, i.supplier_id
, i.product_status
, i.list_price
, i.min_price
, i.catalog_url
FROM product_information i
, product_descriptions d
WHERE d.product_id (+) = i.product_id
AND d.language_id (+) = sys_context('USERENV','LANG')
CREATE OR REPLACE VIEW SH.PROFITS AS
SELECT
s.channel_id,
s.cust_id,
s.prod_id,
s.promo_id,
s.time_id,
c.unit_cost,
c.unit_price,
s.amount_sold,
s.quantity_sold,
c.unit_cost * s.quantity_sold TOTAL_COST
FROM costs c, sales s
WHERE c.prod_id = s.prod_id
AND c.time_id = s.time_id
AND c.channel_id = s.channel_id
AND c.promo_id = s.promo_id
CREATE OR REPLACE VIEW OE.SYDNEY_INVENTORY AS SELECT p.product_id , p.product_name , i.quantity_on_hand FROM inventories i , warehouses w , products p WHERE p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id AND w.warehouse_name = 'Sydney'
CREATE OR REPLACE VIEW OE.TORONTO_INVENTORY AS SELECT p.product_id , p.product_name , i.quantity_on_hand FROM inventories i , warehouses w , products p WHERE p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id AND w.warehouse_name = 'Toronto'
CREATE OR REPLACE PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205,
'You may only make changes during normal office hours');
END IF;
END secure_dml;
CREATE OR REPLACE FUNCTION get_phone_number_f
(p_in INTEGER, p_phonelist phone_list_typ)
RETURN VARCHAR2 AS
TYPE phone_list IS VARRAY(5) OF VARCHAR2(25);
phone_out varchar2(25) := null;
v_size INTEGER;
BEGIN
IF p_phonelist.FIRST IS NULL OR
p_in > (p_phonelist.LAST + 1) - p_phonelist.FIRST THEN
RETURN phone_out;
ELSE
phone_out := p_phonelist(p_in);
RETURN phone_out;
END IF;
END;
CREATE OR REPLACE TYPE "ADHEADER_TYP" AS OBJECT
( header_name VARCHAR2(256)
, creation_date DATE
, header_text VARCHAR2(1024)
, logo BLOB
);
CREATE OR REPLACE TYPE catalog_typ
UNDER composite_category_typ
(
MEMBER FUNCTION getCatalogName RETURN VARCHAR2
, OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2
);TYPE BODY catalog_typ AS
OVERRIDING MEMBER FUNCTION category_describe RETURN varchar2 IS
BEGIN
RETURN 'catalog_typ';
END;
MEMBER FUNCTION getCatalogName RETURN varchar2 IS
BEGIN
-- Return the category name from the supertype
RETURN self.category_name;
END;
END;
CREATE OR REPLACE TYPE category_typ
AS OBJECT
( category_name VARCHAR2(50)
, category_description VARCHAR2(1000)
, category_id NUMBER(2)
, NOT instantiable
MEMBER FUNCTION category_describe RETURN VARCHAR2
)
NOT INSTANTIABLE NOT FINAL
ALTER TYPE category_typ
ADD ATTRIBUTE (parent_category_id number(2)) CASCADE
CREATE OR REPLACE TYPE composite_category_typ
UNDER category_typ
(
subcategory_ref_list subcategory_ref_list_typ
, OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2
)
NOT FINAL;TYPE BODY composite_category_typ AS
OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2 IS
BEGIN
RETURN 'composite_category_typ';
END;
END;
CREATE OR REPLACE TYPE corporate_customer_typ
UNDER customer_typ
( account_mgr_id NUMBER(6)
);
CREATE OR REPLACE TYPE "CUST_ADDRESS_TYP" AS OBJECT
( street_address VARCHAR2(40)
, postal_code VARCHAR2(10)
, city VARCHAR2(30)
, state_province VARCHAR2(10)
, country_id CHAR(2)
);
CREATE OR REPLACE TYPE customer_typ
AS OBJECT
( customer_id NUMBER(6)
, cust_first_name VARCHAR2(20)
, cust_last_name 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)
, cust_orders order_list_typ
)
NOT FINAL;
CREATE OR REPLACE TYPE inventory_list_typ
AS TABLE OF inventory_typ;
CREATE OR REPLACE TYPE inventory_typ
AS OBJECT
( product_id NUMBER(6)
, warehouse warehouse_typ
, quantity_on_hand NUMBER(8)
) ;
CREATE OR REPLACE TYPE leaf_category_typ
UNDER category_typ
(
product_ref_list product_ref_list_typ
, OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2
);TYPE BODY leaf_category_typ AS
OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2 IS
BEGIN
RETURN 'leaf_category_typ';
END;
END;
CREATE OR REPLACE TYPE "ORDER_EVENT_TYP" AS OBJECT ( order_id NUMBER(12), product_id NUMBER(6), customer_id NUMBER(6), cust_first_name VARCHAR2(20), cust_last_name VARCHAR2(20), order_status NUMBER(2), delivery_date DATE );
CREATE OR REPLACE TYPE order_item_list_typ
AS TABLE OF order_item_typ;
CREATE OR REPLACE TYPE order_item_typ
AS OBJECT
( order_id NUMBER(12)
, line_item_id NUMBER(3)
, unit_price NUMBER(8,2)
, quantity NUMBER(8)
, product_ref REF product_information_typ
) ;
CREATE OR REPLACE TYPE order_list_typ
AS TABLE OF order_typ;
CREATE OR REPLACE TYPE order_typ
AS OBJECT
( order_id NUMBER(12)
, order_mode VARCHAR2(8)
, customer_ref REF customer_typ
, order_status NUMBER(2)
, order_total NUMBER(8,2)
, sales_rep_id NUMBER(6)
, order_item_list order_item_list_typ
) ;
CREATE OR REPLACE TYPE "PHONE_LIST_TYP" AS VARRAY(5) OF VARCHAR2(25);
CREATE OR REPLACE TYPE product_information_typ
AS OBJECT
( product_id 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)
, inventory_list inventory_list_typ
) ;
CREATE OR REPLACE TYPE product_ref_list_typ
AS TABLE OF number(6);
CREATE OR REPLACE TYPE subcategory_ref_list_typ
AS TABLE OF REF category_typ;
CREATE OR REPLACE type "SYS_YOID0000050342$" as object( "SYS_NC00001$" NUMBER(6,0))
CREATE OR REPLACE type "SYS_YOID0000050344$" as object( "SYS_NC00001$" NUMBER(6,0))
CREATE OR REPLACE type "SYS_YOID0000050346$" as object( "SYS_NC00001$" NUMBER(6,0))
CREATE OR REPLACE type "SYS_YOID0000050348$" as object( "SYS_NC00001$" NUMBER(6,0))
CREATE OR REPLACE type "SYS_YOID0000050350$" as object( "SYS_NC00001$" NUMBER(12,0))
CREATE OR REPLACE TYPE "TEXTDOC_TAB" AS TABLE OF textdoc_typ;
CREATE OR REPLACE TYPE "TEXTDOC_TYP" AS OBJECT
( document_typ VARCHAR2(32)
, formatted_doc BLOB
) ;
CREATE OR REPLACE TYPE warehouse_typ
AS OBJECT
( warehouse_id NUMBER(3)
, warehouse_name VARCHAR2(35)
, location_id NUMBER(4)
) ;
CREATE OR REPLACE TYPE "XDBPO_ACTION_COLLECTION" AS VARRAY(4) OF "XDBPO_ACTION_TYPE"
CREATE OR REPLACE TYPE "XDBPO_ACTION_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","Manager" VARCHAR2(10 CHAR),"Date" DATE)FINAL INSTANTIABLE
CREATE OR REPLACE TYPE "XDBPO_ACTIONS_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","ACTION" "XDBPO_ACTION_COLLECTION")NOT FINAL INSTANTIABLE
CREATE OR REPLACE TYPE "XDBPO_LINEITEM_COLLECTION" AS VARRAY(2147483647) OF "XDBPO_LINEITEM_TYPE"
CREATE OR REPLACE TYPE "XDBPO_LINEITEM_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","ITEMNUMBER" NUMBER(38),"Description" VARCHAR2(256 CHAR),"Part" "XDBPO_PART_TYPE")NOT FINAL INSTANTIABLE
CREATE OR REPLACE TYPE "XDBPO_LINEITEMS_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","LINEITEM" "XDBPO_LINEITEM_COLLECTION")NOT FINAL INSTANTIABLE
CREATE OR REPLACE TYPE "XDBPO_PART_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","PARTNO" VARCHAR2(14 CHAR),"Quantity" NUMBER(12,2),"UNITPRICE" NUMBER(8,4))FINAL INSTANTIABLE
CREATE OR REPLACE TYPE "XDBPO_REJECTION_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","Manager" VARCHAR2(10 CHAR),"Date" DATE,"Comments" VARCHAR2(2048 CHAR))NOT FINAL INSTANTIABLE
CREATE OR REPLACE TYPE "XDBPO_SHIPINSTRUCTIONS_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","name" VARCHAR2(20 CHAR),"address" VARCHAR2(256 CHAR),"telephone" VARCHAR2(24 CHAR))NOT FINAL INSTANTIABLE
CREATE OR REPLACE TYPE "XDBPO_TYPE" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","Reference" VARCHAR2(30 CHAR),"ACTIONS" "XDBPO_ACTIONS_TYPE","REJECTION" "XDBPO_REJECTION_TYPE","Requestor" VARCHAR2(128 CHAR),"EmployeeID" NUMBER(38),"DepartmentID" VARCHAR2(4 CHAR),"SHIPPINGINSTRUCTIONS" "XDBPO_SHIPINSTRUCTIONS_TYPE","SpecialInstructions" VARCHAR2(2048 CHAR),"LINEITEMS" "XDBPO_LINEITEMS_TYPE")FINAL INSTANTIABLE
CREATE SEQUENCE IX.AQ$_ORDERS_QUEUETABLE_N INCREMENT BY 1 START WITH 1 MAXVALUE CACHE CYCLE
CREATE SEQUENCE IX.AQ$_STREAMS_QUEUE_TABLE_N INCREMENT BY 1 START WITH 1 MAXVALUE CACHE CYCLE
CREATE SEQUENCE HR.DEPARTMENTS_SEQ INCREMENT BY 10 START WITH 1 MAXVALUE 9990 CACHE CYCLE
CREATE SEQUENCE HR.EMPLOYEES_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE CACHE CYCLE
CREATE SEQUENCE HR.LOCATIONS_SEQ INCREMENT BY 100 START WITH 1 MAXVALUE 9900 CACHE CYCLE
CREATE SEQUENCE OE.ORDERS_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE CACHE CYCLE