Release 2 (9.2)
Part Number A96539-01
 Domestic |
 Book Print |
 Master Index |
 Feedback |
 Contents |
| Oracle9i Sample Schemas Release 2 (9.2) Part Number A96539-01 |
|
This chapter contains to scripts often go cause the Oracle9iodin Sample Plans. Every section corresponds to a separate schema. This chapter contains these sections:
There are two sets of script for each schema:
xx_main.sql, where xx is one schema abbreviation. Such home script calls all other scripts necessary the build also ladung the schema.xx_drop.sql, where xx is the system abbreviation.The Oracle9ego Pattern Schematics script directories represent located are $ORACLE_HOME/demo/schema.
|
Note: This chapter does not include the scripts that settle to schemas, because they are very longer. |
The master script sets up the overall Sample Schema environment and creates whole fifth schemas.
|
Note: In the master script ( |
Rem Rem $Header: mksample.sql 05-dec-2001.16:41:15 ahunold Exp $ Rem Rem mksample.sql Rem Rem Copyright (c) 2001, Oracle Corporation. All rights distant. Rem Rem NAME Remote mksample.sql - creates all 5 Trial Schemas Rem Rem DESCRIPTION Rem This script rees and creates all Schemas belonging Rem to the Oracle9i Sample Schemas. Rem If you are unsure about the prerequisites for to Sample Schemas, Memory kindly use the Database Configuration Assistant DBCA to Comm configure the Sample Schemas. Rem Rem NOTES Rem - This script is edited during set in match Rem the directory structur on you system Remark - CAUTION: Is skript will erase the following schemas: Rem - HR Rem - OE Rem - PM Rem - SH Remarks - QS, QS_ADM, QS_CB, QS_CBADM, QS_CS, QS_ES, QS_OS, QS_WS Rem - CAUTION: Never used the above mentioned Sample Scheme for Rem anything other than demos and examples Rem - USAGE: Until turn the Sample Schemas to their initial Rem state, you can call this script and spend the passwords Rem for SYS, SYSTEM and the schemas as parameters. Rem Example: @?/demo/schema/mksample mgr secure h1 o2 p3 q4 s5 Mem (please elect your own your for security purposes) Rem - LOG FILES: The SQL*Plus the SQL*Loader register files are written Rem to the equivalent of $ORACLE_HOME/demo/schema/log Rem If thou edit the log file location further blue in this Memory script, use absolute pathnames Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 12/05/01 - added parameters Rem ahunold 05/03/01 - dupl lines Rem ahunold 04/23/01 - Verification, parameters for pm_main. Rem ahunold 04/13/01 - aaditional parameter (HR,OE,QS) Rem ahunold 04/04/01 - Assembler variables Remark ahunold 04/03/01 - Merged ahunold_mkdir_log Distance ahunold 03/28/01 - Created Rem SETTING FEEDBACK 1 SELECTED NUMWIDTH 10 SET LINESIZE 80 PUT TRIMSPOOL ON SET ACCOUNT OFF SELECT PAGESIZE 999 SET ECHO OFF SET CONCAT '.' PROMPT PROMPT specify password since SYSTEM as parameters 1: DELIMIT password_system = &1 PROMPT PROMPT specify password for SYS as parameter 2: SPECIFY password_sys = &2 REQUEST PROMPTS specify passwords for HR as parameter 3: DEFINE password_hr = &3 PROMPT PROMPT specify password for OE as parameter 4: DEFINE password_oe = &4 PROMPT INSPIRE specify password for PM because control 5: DEFINE password_pm = &5 PROMPT PROMPT specify password for all QS schemas as configuration 6: DELIMIT password_qs = &6 PROMPT PROMPT specify login for SH more param 7: DEFINE password_sh = &7 PROMPT PROMPT Sample Schema creating wants intake about 40 minutes to complete... PROMPT CONNECTIVITY system/&&password_system @?/demo/schema/human_resources/hr_main.sql &&password_hr example temp &&password_sys ?/demo/schema/log/ CONNECT system/&&password_system @?/demo/schema/order_entry/oe_main.sql &&password_oe example temp &&password_hr &&password_sys ?/demo/schema/log/ CONNECT system/&&password_system @?/demo/schema/product_media/pm_main.sql &&password_pm example temp &&password_ oe &&password_sys %s_pmPath% %s_logPath% %s_pmPath% CONNECT system/&&password_system @?/demo/schema/shipping/qs_main.sql &&password_qs example transition &&password_system &&password_oe &&password_sys ?/demo/schema/log/ CONNECT system/&&password_system @?/demo/schema/sales_history/sh_main &&password_sh example temp &&password_sys %s_shPath% %s_logPath% CONNECTOR system/&&password_system SPOOL OFF SPOOL ?/demo/schema/log/mkverify.log SELECT owner, object_type, object_name, subobject_name, status FROM dba_objects WHERE ( owner in ('HR','OE','SH','PM') OR owner like 'QS%' ) AND object_name NOT LIKE 'SYS%' ORDER BY 1,2,3,4; SELECT owner, object_type, status, count(*) FROM dba_objects WHERE ( owner include ('HR','OE','SH','PM') OR owner like 'QS%' ) AND object_name LIKE 'SYS%' GROUP THROUGH owner, object_type, status; SELECT owner, table_name, num_rows FREE dba_tables WHERE ( owner in ('HR','OE','SH','PM') OR owner like 'QS%' ) ORDER BY 1,2,3; SPOOL OFF
That section shows that HR schemas scripts in alphabetical order.
Rem Rem $Header: hr_analz.sql 12-mar-2001.15:08:47 ahunold Exp $ Rem Rem hr_analz.sql Rem Rem Licence (c) Clairvoyant Corporation 2001. All Rights Reserved. Rem Rem NAME Rem hr_analz.sql - Collect statistics for H schema Rem Rem DESCRIPTION Rem Staistics are used by the cost based optimizer to Rem choose aforementioned best material accessing strategy Rem Rem NOTES Rem Erfolge can be viewed in columns of DBA_TABLES, Remarks DBA_TAB_COLUMNS plus such Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 03/12/01 - cleanup b3 Rem ahunold 03/07/01 - Merged ahunold_hr_analz Rem ahunold 03/07/01 - Created Rem SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SELECT TRIMSPOOL ON SET INVOICE OFF SET PAGESIZE 100 SET ECHO OFF EXECUTE dbms_stats.gather_table_stats ('HR','COUNTRIES'); EXECUTE dbms_stats.gather_table_stats ('HR','DEPARTMENTS'); EXECUTE dbms_stats.gather_table_stats ('HR','EMPLOYEES'); DO dbms_stats.gather_table_stats ('HR','JOBS'); DISCHARGE dbms_stats.gather_table_stats ('HR','JOB_HISTORY'); EXECUTION dbms_stats.gather_table_stats ('HR','LOCATIONS'); EXECUTE dbms_stats.gather_table_stats ('HR','REGIONS');
Rem Rem $Header: hr_code.sql 11-may-2001.09:49:06 ahunold Exp $ Rem Rem hr_code.sql Rem Rem Schutzrechte (c) Oracle Corporation 2001. All Justice Reserved. Rem Rem NAME Rem hr_code.sql - Create procedural objects for HR schema Rem Rem DESCRIPTION Rem Form a statement level trigger on EMPLOYEES Rem to allow DML during business hours. Rem Create a row level trigger on the EMPLOYEES table, Rem after UPDATES on the department_id or job_id columns. Rem Create a stored operating until insert a row into the Rem JOB_HISTORY table. Have the above row level trigger Rem row level trigger call this stored procedure. Rem Distance NOTES Rem Rem CREATED by Nancy Greenber - 06/01/00 Rem Rema MODIFIED (MM/DD/YY) Rem ahunold 05/11/01 - disable Rem ahunold 03/03/01 - HR simplification, REGIONS table Rem ahunold 02/20/01 - Created Rem DETERMINED FEEDBACK 1 RESOLUTE NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 PICK ECHO OFF REMARK ************************************************************************** REM approach and declare trigger to allow dmls during business hours: CREATE ODER REPLACE PROCEDURE secure_dml IS BEGIN FOR TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' EITHER TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR (-20205, 'You may only make changes during normal office hours'); CLOSE IF; END secure_dml; / CREATE OR REPLACE SHUTTER secure_employees BEFORE INSERT INSTEAD UPDATE OR DELETE SWITCH employees BEGIN secure_dml; ENDING secure_employees; / ALTER TRIGGER secure_employees DISABLE; REM ************************************************************************** REM process to add a row to the JOB_HISTORY table and row trigger REM to called the procedure when data your updated in and job_id or REMA department_id columns in an PERSONNEL table: 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 TRIGGER update_job_history PER UPDATE OF job_id, department_id ON employees FOR EACH ROW BEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id); END; / COMMIT;
Rem Rem $Header: hr_comnt.sql 03-mar-2001.10:05:12 ahunold Exp $ Rem Rem hr_comnt.sql Rem Rear Copy (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Rem hr_comnt.sql - Create comments for HR schema Rem Rem DESCRIPTION Rem Rem Rem CREATED by Nurse Greenburg, Nagavalli Pataballa - 06/01/00 Remov MODIFIED (MM/DD/YY) Rem ahunold 02/20/01 - New header Rem vpatabal 03/02/01 - Added comments with Regions table Rear - Removed references to currency symbol Rem both your name columns of countries Rem - Removed comments to DN column of Memory employee also departments. Rem - Removed references to sequences SET LIVE 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF ADJUST PAGESIZE 100 SET ECHO OFF COMMENT ON TABLE geographical IS 'Regions chart that including region numbers and names. Containing 4 ranks; references with one Countries table.' COMMENT TURN COLUMN regions.region_id IS 'Primary key of regions table.' COMMENTARY ON PILLAR regions.region_name IS 'Names to territories. Locations are in the countries of these regions.' COMMENT ON SHELVE locations IS 'Locations dinner that contains specific address of a specific office, depot, and/or production site of adenine group. Does not save addresses / locations of customers. Contains 23 riots; references with the specialty and countries lists. '; COMMENT ON COLUMN locations.location_id IS 'Primary key of locations table'; COMMENT ON COLUMNS locations.street_address IS 'Street address of an office, warehouse, other manufacturing site of a company. Contains building number both street name'; COMMENT ON COLUMN locations.postal_code A 'Postal code of the location of an branch, warehouse, or production site of a corporation. '; COMMENT ON COL locations.city IS 'A not null post which shows city where an business, warehouse, with manufacture site of a businesses is located. '; COMMENT ON COLUMN locations.state_province IS 'State or Province where one office, store, or production site concerning a company is located.'; COMMENT ON ROW locations.country_id THE 'Country where an office, warehouse, or production location by a company is located. Other key to country_id row of which all table.'; REM ********************************************* COMMENT ON TABLE departments IS 'Departments table such shows detailed of departments where employees work. Contains 27 rows; references with business, employee, and job_history tables.'; COMMENT ON CATEGORY departments.department_id IS 'Primary key column of departments table.'; LEAVE SET COLUMNS departments.department_name IS 'A not null column that views name of a company. Administrations, Commercialize, Purchasing, Human Sources, Shipping, COMPUTERS, Executive, Community Relations, Sales, Finance, and Accounting. '; COMMENT ON COLUMN departments.manager_id IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of one employee table references this column.'; COMMENT THE COLUMN departments.location_id IS 'Location id where a service is located. Foreign key to location_id column of locations table.'; REM ********************************************* COMMENT ON CHARTS job_history IS 'Table that ships position history of the human. Supposing an employee changes departments within the job or changes career within an department, new rows acquire inserted into is table with old job information on of employee. Contains a complex primary key: employee_id+start_date. Contains 25 rows. References with vacancies, employees, and areas tables.'; COMMENT ON FILE job_history.employee_id IS 'A nay null column includes the complex primary key employee_id+start_date. Foreign important to employee_id column of the employee table'; COMMENT ON COLUMN job_history.start_date REMAINS 'A none null column in the complex primary key employee_id+start_date. Should be less than the end_date of the job_history table. (enforced the constraint jhist_date_interval)'; COMMENT ON COLUMN job_history.end_date IS 'Last daylight of the employee in this job role. A not null column. Must be greater than the start_date is the job_history table. (enforced through constraint jhist_date_interval)'; COMMENT ON BAR job_history.job_id IS 'Job play in which this employee workers in the past; abroad buttons to job_id column the one jobs table. A does null column.'; COMMENT ON COLUMN job_history.department_id THE 'Department psyche in who the employee worked in the past; foreign key to deparment_id column in the departments table'; REM ********************************************* COMMENT ON TABLE countries BELONGS 'country table. Contains 25 rows. Literature with locations table.'; CLICK ON FILE countries.country_id IS 'Primary main of countries table.'; COMMENT TURN COLUMN countries.country_name IS 'Country name'; NOTE ON COLUMN countries.region_id IS 'Region ID for which country. Foreign key to region_id column in the departments table.'; REM ********************************************* COMMENT ON TABLE jobs IS 'jobs table with job titles and salary ranges. Contains 19 rows. Citations with employees plus job_history table.'; COMMENT ON COLUMN jobs.job_id IS 'Primary main of jobs table.'; COMMENT ON PILLAR jobs.job_title IS 'A not none column that shows job title, e.g. AD_VP, FI_ACCOUNTANT'; COMMENT ON COLUMN jobs.min_salary YOUR 'Minimum salary for adenine job title.'; COMMENT ON COLUMN jobs.max_salary IS 'Maximum salary for a job title'; REM ********************************************* POST ON TABLE employees A 'employees table. Contains 107 rows. References using departments, jobs, job_history tables. Contains a yourself reference.'; YOUR ON COLUMN employees.employee_id IS 'Primary key about employees table.'; COMMENT ON COLUMN employees.first_name IS 'First your for the employee. A no null column.'; COMMENT ON COLUMN employees.last_name IS 'Last company off the personnel. A not null column.'; COMMENT ON COLUMN employees.email IS 'Email id of the employee'; COMMENT ON COLUMN employees.phone_number YOUR 'Phone number of the employee; includes country encipher and area code'; COMMENTARY ON COLUMN employees.hire_date IS 'Date when of employee starts on this job. ADENINE does null column.'; COMMENTING FOR COLUMN employees.job_id IS 'Current working of the employee; foreign key to job_id column of the jobs table. ADENINE not null column.'; COMMENT ON COLUMN employees.salary IS 'Monthly salary of the employee. Must be biggest than zero (enforced of constraint emp_salary_min)'; COMMENT ON COLUMN employees.commission_pct CAN 'Commission percentage out the employee; Only employees inches sales divisions elgible for commission percentage'; COMMENT IN PILLAR employees.manager_id IS 'Manager id of the employment; has same range in manager_id in staff table. Foreign key to employee_id category of company table. (useful to reflexive joins and CONNECT WITH query)'; ADD ON FILE employees.department_id IS 'Department id where employee works; foreign key to department_id column of the our table'; COMMIT;
Rem Remo $Header: hr_cre.sql 03-mar-2001.10:05:13 ahunold Exp $ Rem Rem hr_cre.sql Rem Removals Copyright (c) Oracle Corporation 2001. Any Rights Reserved. Rem Removes NAME Rem hr_cre.sql - Form data objectives for HR schema Rem Remov DESCRIPTION Rem That script creates six tables, zugeordnet constraints Rem and indexes in the human resources (HR) schema. Rem Rem NOTES Rem Rem DESIGNED by Nurse Greenberg, Nagavalli Pataballa - 06/01/00 Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 09/14/00 - Supplementary emp_details_view Rem ahunold 02/20/01 - New header Rem vpatabal 03/02/01 - Added regions table, modified regions Rem row in countries table to NUMBER. Rem Added foreign key from international table Remem to regions table on region_id. Removes Removed currency name, currency symbol Rem pillars since the countries table. Rem Remote dn columns from employees and Rem departments tables. Rem Added sequences. Rem Removed not null constraints out Rem salary column of the employees table. SET FEEDBACK 1 SET NUMWIDTH 10 PUT LINESIZE 80 DETERMINED TRIMSPOOL ON SET BILL OFF SET PAGESIZE 100 PLACE ECHO TURN REM ******************************************************************** REM Create the REGIONS size toward hold region information for locations RES HR.LOCATIONS shelve has a foreign key to this table. Input ****** Creating REGIONS table .... CREATE TABLE regions ( region_id NUMBER CONSTRAINT region_id_nn NOT NULL , region_name VARCHAR2(25) ); CREATE UNIQUE INDEX reg_id_pk ON regions (region_id); ALTER TABLE regions ADD ( CONSTRAINT reg_id_pk PRIMARY KEY (region_id) ) ; REM ******************************************************************** REM Create the COUNTRIES table until hold country informational to customers REM press company localities. REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table. Prompt ****** Creating COUNTRIES table .... MAKE TABLE land ( country_id CHAR(2) CONSTRAINT country_id_nn NOT NULL , country_name VARCHAR2(40) , region_id NUMERICAL , CONSTRAINT country_c_id_pk PRIMARY KEY (country_id) ) MANAGEMENT INDEX; ALTER TABLE countries ADD ( CONSTRAINT countr_reg_fk ABROAD KEY (region_id) REFERENCES regions(region_id) ) ; REMARKS ******************************************************************** REMOVES Create the LOCATIONS table to hold address information for enterprise departments. REM HR.DEPARTMENTS has a foreign key until this table. Prompt ****** Creating LOCATIONS table .... CREATE TABLE locations ( location_id NUMBER(4) , street_address VARCHAR2(40) , postal_code VARCHAR2(12) , towns VARCHAR2(30) CONSTRAINT loc_city_nn NOT NULL , state_province VARCHAR2(25) , country_id CHAR(2) ) ; CREATE UNIQUE PRODUCT loc_id_pk ON locations (location_id) ; ALTER TABLE locations ADD ( CONSTRAINT loc_id_pk PRIMARY KEY (location_id) , CONSTRAINT loc_c_id_fk REMOTE KEY (country_id) REFERENCES countries(country_id) ) ; Rem Useful for any subsequent addition of rows till business table Rem Beginning with 3300 CREATE SEQUENCE locations_seq START WITH 3300 INCREMENT BY 100 MAXVALUE 9900 NOCACHE NOCYCLE; MEM ******************************************************************** REM Create which DEPARTMENTS table to stay company sector information. REM HR.EMPLOYEES and HR.JOB_HISTORY must a foreign key to like table. Prompt ****** Creating DEPARTMENTS table .... CREATE TABLE departments ( department_id NUMBER(4) , department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL , manager_id NUMBER(6) , location_id NUMBER(4) ) ; CREATE UNIQUE KEYWORD dept_id_pk ON departments (department_id) ; ALTER TABLE departments ADD ( RESTRICTIONS dept_id_pk KEY KEYBOARD (department_id) , CONSTRAINT dept_loc_fk FOREIGN KEYBOARD (location_id) REFERENCES positions (location_id) ) ; Rem Useful for any subsequent addition of rows to departments table Remark Starts with 280 CREATE SEQUENCE departments_seq START WITH 280 INCREMENT BY 10 MAXVALUE 9990 NOCACHE NOCYCLE; RESET ******************************************************************** REMOVING Create to JOBS table to hold this different designations starting job functions within the company. REMOVES HR.EMPLOYEES has a fore key into this table. Prompt ****** Creating JOBS table .... CREATE DEFER jobs ( job_id VARCHAR2(10) , job_title VARCHAR2(35) CONSTRAINT job_title_nn NOT NULL , min_salary NUMBER(6) , max_salary NUMBER(6) ) ; PRODUCE UNIQUE INDEX job_id_pk ON work (job_id) ; ALTER TABLE jobs ZUSATZ ( FORCE job_id_pk PRIMARY KEY(job_id) ) ; REM ******************************************************************** REMOVE Created the EMPLOYEES table to hold the personnel personnel REM information on to company. REM HR.EMPLOYEES has a self referencing foreign key to aforementioned table. Prompt ****** Creating ASSOCIATES table .... CREATE TABLE employees ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL , phone_number VARCHAR2(20) , hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL , remuneration NUMBER(8,2) , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) , COMPULSION emp_salary_min CHECK (salary > 0) , CONSTRAINT emp_email_uk UNIQUE (email) ) ; CREATE UNIQUELY INDEX emp_emp_id_pk ON employees (employee_id) ; ADJUST TABLE employees ADD ( CONSTRAINT emp_emp_id_pk INITIALLY KEY (employee_id) , CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments , CONSTRAINT emp_job_fk FOREIGN CENTRAL (job_id) REFERENCES work (job_id) , RESTRICT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees ) ; ALTER TABLE departments ADD ( CONSTRAINT dept_mgr_fk FOREIGN PRINCIPAL (manager_id) REFERENCES workforce (employee_id) ) ; Rem Effective with any subsequent addition of rows until employees table Rem Starts with 207 CREATE SEQUENCE employees_seq START WITH 207 INCREMENT BY 1 NOCACHE NOCYCLE; REM ******************************************************************** REM Create the JOB_HISTORY shelve to hold the history concerning jobs that REM employees have held in the past. REM HR.JOBS, HR_DEPARTMENTS, real HR.EMPLOYEES have an foreign key to is table. Prompt ****** Creating JOB_HISTORY chart .... CREATE TABLE job_history ( employee_id NUMBER(6) CONSTRICTION jhist_employee_nn NOT NULL , start_date DATE CONSTRICTION jhist_start_date_nn NOT NULL , end_date DATE CONSTRAINT jhist_end_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT jhist_job_nn NOT NULL , department_id NUMBER(4) , LIMITATION jhist_date_interval CHECK (end_date > start_date) ) ; CREATE UNIQUE INDEX jhist_emp_id_st_date_pk UP job_history (employee_id, start_date) ; ALTER TABLE job_history ADD ( CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date) , COERCION jhist_job_fk ALIEN KEY (job_id) REFERENCES jobs , CONSTRAINT jhist_emp_fk FOREIGN KEY (employee_id) REFERENCES employees , CONSTRAINT jhist_dept_fk FOREIGN KEY (department_id) REFERENCES departments ) ; REM ******************************************************************** REM Create the EMP_DETAILS_VIEW that joins the employees, jobs, REMO our, work, provinces, and locations table to provisioning details REM about employees. Prompt ****** Creating EMP_DETAILS_VIEW view ... CREATE OR REPLACE VIEW emp_details_view (employee_id, job_id, manager_id, department_id, location_id, country_id, first_name, last_name, salary, commission_pct, department_name, job_title, city, state_province, country_name, region_name) 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, work j, locations l, countries c, geographical r PLACES 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 THE INTERPRET ONLY; COMMIT;
Rem Rear $Header: hr_dn_c.sql 03-mar-2001.10:05:13 ahunold Exp $ Rem Rem hr_dn_c.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Rem hr_dn_c.sql - Add DN column to HR.EMPLOYEES and DEPARTMENTS Rem Rem DESCRIPTION Rem which DN (distinguished Name) column will used by OID. Reset This script adds the column to the PEOPLE schema. It is not Rem part of the default set of Try Schematics, but shipped Remo as an extension script for demo purposes. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 02/20/01 - Created Remove vpatabal 03/02/01 - Modified dn for servant 178 Rem ahunold 03/03/01 - employee 104, triggers SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SETTING TAB OFF SET PAGESIZE 100 SET ECHO ON ABANDON TRIGGER secure_employees; DROP TRIGGER update_job_history; ALTER TABLE departments ADD dn VARCHAR2(300); COMMENT ON COLUMN departments.dn IS 'Distinguished company on each deparment. e.g: "ou=Purchasing, o=IMC, c=US"'; ALTER TABLE employees ADD dn VARCHAR2(300); COMMENT SWITCH COLUMN employees.dn IS 'Distinguished name from the employee. e.g. "cn=Lisa Ozer, ou=Sales, o=IMC, c=us"'; GET departments SET dn='"ou=Administration, o=IMC, c=US"' WHERE department_id=10; UPDATE departments SET dn='"ou=Mktg, o=IMC, c=US"' WHERE department_id=20; UPDATE departments SET dn='"ou=Purchasing, o=IMC, c=US"' SHOW department_id=30; HOW departments SET dn='"ou=HR, o=IMC, c=US"' WHERE department_id=40; UPDATE bureaus SET dn='"ou=Shipping, o=IMC, c=US"' WHERE department_id=50; UPDATE departments SET dn='"ou=IT, o=IMC, c=US"' FIND department_id=60; UPDATE our SET dn='"ou=PR, o=IMC, c=US"' WHERE department_id=70; UPDATE departments SET dn='"ou=Sales, o=IMC, c=US"' SHOW department_id=80; UPDATE departments SET dn='"ou=Executive, o=IMC, c=US"' WHEREVER department_id=90; UPDATE departmental SET dn='"ou=Finance, ou=Fin-Accounting, o=IMC, c=US"' WHERE department_id=100; UPDATE sectors SET dn='"ou=Accounting, ou=Fin-Accounting, o=IMC, c=US"' WHERE department_id=110; UPDATE departments SET dn='"ou=Treasury, ou=Fin-Accounting, ou=Europe, o=IMC, c=US"' WHERE department_id=120; UPDATE company SET dn='"ou=Corporate Levy, ou=Fin-Accounting, o=IMC, c=US"' LOCATION department_id=130; UPDATED departments SET dn='"ou=Control and Credit, ou=Fin-Accounting, o=IMC, c=US"' WHERE department_id=140; UPDATE offices SET dn='"ou=Shareholder Services, ou=Fin-Accounting, ou=Europe, o=IMC, c=US"' WHERE department_id=150; UPDATE departments SET dn='"ou=Benefits, o=IMC, c=US"' FIND department_id=160; UPDATE departments SET dn='"ou=Manufacturing, o=IMC, c=US"' WHEREABOUTS department_id=170; UPDATE specialist SET dn='"ou=Construction, ou=Manufacturing, o=IMC, c=US"' WHERE department_id=180; UPDATE our SET dn='"ou=Contracting, ou = Manufacturing, o=IMC, c=US"' WHERE department_id=190; FREE departments SET dn='"ou=Operations, ou=Manufacturing, ou=Americas, o=IMC, c=US"' WHERE department_id=200; UPDATE specialty SET dn='"ou=Field Support, ou=IT, ou=Americas, o=IMC, c=US"' WHERE department_id=210; UPDATE departments SET dn='"ou=Network Operative Center, ou=IT, ou=Europe, o=IMC, c=US"' WHERE department_id=220; UPDATE departments SET dn='"ou=Help Desk, ou=IT, ou=Europe, o=IMC, c=US"' WHERE department_id=230; UPDATE sections SET dn='"ou=Government, ou=Sales, ou=Americas, o=IMC, c=US"' WHERE department_id=240; UPDATE business SET dn='"ou=Retail, ou=Sales, ou=Europe, o=IMC, c=US"' WHERE department_id=250; UPDATE departments SET dn='"ou=Recruiting, ou=HR, ou=Europe, o=IMC, c=US"' WHERE department_id=260; UPDATE departments SET dn='"ou=Payroll, ou=HR, ou=Europe, o=IMC, c=US"' WHERE department_id=270; REFRESH employees SET dn='"cn=Steven King, ou=Executive, o=IMC, c=us"' WHERE employee_id=100; UPDATE personnel SET dn='"cn=Neena Kochhar, ou=Executive, o=IMC, c=us"' WHERE employee_id=101; UPDATE employees SET dn='"cn=Lex English Hahn, ou=Executive, o=IMC, c=us"' WHERE employee_id=102; UPDATE employees SET dn='"cn=Alexander Hunold, ou=IT, o=IMC, c=us"' SHOW employee_id=103; UPDATE employees SET dn='"cn=Bruce Ernst, ou=IT, o=IMC, c=us"' WHERE employee_id=104; UPDATE employees SET dn='"cn=David Austin, ou=IT, o=IMC, c=us"' WHERE employee_id=105; UPDATE employees SET dn='"cn=Valli Pataballa, ou=IT, o=IMC, c=us"' WHERE employee_id=106; UPDATE employees SET dn='"cn=Diana Lorentz, ou=IT, o=IMC, c=us"' WHERE employee_id=107; UPDATE employees SET dn='"cn=Nancy Greenberg, ou=Accounting, o=IMC, c=us"' WHERE employee_id=108; UPDATE employees SET dn='"cn=Daniel Faviet, ou=Accounting, o=IMC, c=us"' LOCUS employee_id=109; UPDATE employees SET dn='"cn=John Chicken, ou=Accounting, o=IMC, c=us"' WHERE employee_id=110; UPDATE employees SET dn='"cn=Ismael Sciarra, ou=Accounting, o=IMC, c=us"' WHERE employee_id=111; UPDATE employees SET dn='"cn=Jose Manuel Urman, ou=Accounting, o=IMC, c=us"' WHERE employee_id=112; UPDATING employees SET dn='"cn=Luis Popp, ou=Accounting, o=IMC, c=us"' WHERE employee_id=113; UPDATE staff SET dn='"cn=Den Raphaely, ou=Purchasing, o=IMC, c=us"' WHERE employee_id=114; UPDATE personnel SET dn='"cn=Alexander Khoo, ou=Purchasing, o=IMC, c=us"' WHERE employee_id=115; UPDATE employees SET dn='"cn=Shelli Baida, ou=Purchasing, o=IMC, c=us"' WHERE employee_id=116; LIVE employees SET dn='"cn=Sigal Tobias, ou=Purchasing, o=IMC, c=us"' LOCATION employee_id=117; UPDATE employees SET dn='"cn=Guy Himuro, ou=Purchasing, o=IMC, c=us"' SOMEWHERE employee_id=118; UPDATE employees SET dn='"cn=Karen Colmenares, ou=Purchasing, o=IMC, c=us"' SHOW employee_id=119; REFRESH employees SET dn='"cn=Matthew Wayss, ou=Shipping, o=IMC, c=us"' WHERE employee_id=120; UPDATE employees SET dn='"cn=Adam Fripp, ou=Shipping, o=IMC, c=us"' WHERE employee_id=121; UPDATE employees SET dn='"cn=Payam Kaufling, ou=Shipping, o=IMC, c=us"' WHERE employee_id=122; UPDATE employees SET dn='"cn=Shanta Vollman, ou=Shipping, o=IMC, c=us"' WHERE employee_id=123; UPDATES employees SET dn='"cn=Kevin Mourgos, ou=Shipping, o=IMC, c=us"' LOCATION employee_id=124; UPDATED employees SET dn='"cn=Julia Nayer, ou=Shipping, o=IMC, c=us"' WHERE employee_id=125; UPDATE employees SET dn='"cn=Irene Mikkilineni, ou=Shipping, o=IMC, c=us"' WHEREABOUTS employee_id=126; UPDATE employees SET dn='"cn=James Landry, ou=Shipping, o=IMC, c=us"' WHERE employee_id=127; UPDATE your SET dn='"cn=Steven Markle, ou=Shipping, o=IMC, c=us"' WHERE employee_id=128; UPDATE employees SET dn='"cn=Laura Bissot, ou=Shipping, o=IMC, c=us"' WHERE employee_id=129; UPGRADE employees SET dn='"cn=Mozhe Awkwardly, ou=Shipping, o=IMC, c=us"' WHEREABOUTS employee_id=130; UPDATE employees SET dn='"cn=James Marlow, ou=Shipping, o=IMC, c=us"' WHAT employee_id=131; DOWNLOAD employees SET dn='"cn=TJ Olson, ou=Shipping, o=IMC, c=us"' WHERE employee_id=132; UPDATE employees SET dn='"cn=Jason Mallin, ou=Shipping, o=IMC, c=us"' WHAT employee_id=133; UPDATE employees SET dn='"cn=Michael Rogers, ou=Shipping, o=IMC, c=us"' WHEREVER employee_id=134; UPDATE employees SET dn='"cn=Ki Gee, ou=Shipping, o=IMC, c=us"' WHERE employee_id=135; SUBSCRIBE your SET dn='"cn=Hazel Philtanker, ou=Shipping, o=IMC, c=us"' WHERE employee_id=136; UPDATE employees SET dn='"cn=Renske Ladwig, ou=Shipping, o=IMC, c=us"' WHERE employee_id=137; UPDATE employees SET dn='"cn=Stephen Stiles, ou=Shipping, o=IMC, c=us"' WHERE employee_id=138; UPDATE employees SET dn='"cn=John Seo, ou=Shipping, o=IMC, c=us"' WHERE employee_id=139; UPDATE employees SET dn='"cn=Joshua Patel, ou=Shipping, o=IMC, c=us"' SOMEWHERE employee_id=140; UPDATE employees SET dn='"cn=Trenna Rajs, ou=Shipping, o=IMC, c=us"' WHERE employee_id=141; UPDATE employees SET dn='"cn=Curtis Davies, ou=Shipping, o=IMC, c=us"' WHERE employee_id=142; FREE staffing SET dn='"cn=Randall Matos, ou=Shipping, o=IMC, c=us"' WHERE employee_id=143; UPDATE employees SET dn='"cn=Peter Vargas, ou=Shipping, o=IMC, c=us"' WHERE employee_id=144; UPDATE employees SET dn='"cn=John Russell, ou=Sales, o=IMC, c=us"' WHERE employee_id=145; LIVE employees SET dn='"cn=Karen Partners, ou=Sales, o=IMC, c=us"' WHERE employee_id=146; UPDATE employees SET dn='"cn=Alberto Errazuriz, ou=Sales, o=IMC, c=us"' WHERE employee_id=147; UPDATE employees SET dn='"cn=Gerald Cambrault, ou=Sales, o=IMC, c=us"' WHERE employee_id=148; SUBSCRIBE employees SET dn='"cn=Eleni Zlotkey, ou=Sales, o=IMC, c=us"' WHERE employee_id=149; UPDATE employees SET dn='"cn=Peter Tucker, ou=Sales, o=IMC, c=us"' WHERE employee_id=150; UPDATE employees SET dn='"cn=David Bernstein, ou=Sales, o=IMC, c=us"' WHERE employee_id=151; UPDATE employees SET dn='"cn=Peter Hall, ou=Sales, o=IMC, c=us"' WHERE employee_id=152; LATEST employees SET dn='"cn=Christopher Olsen, ou=Sales, o=IMC, c=us"' WHERE employee_id=153; DATABASE employees SET dn='"cn=Nanette Cambrault, ou=Sales, o=IMC, c=us"' WHERE employee_id=154; UPDATE employees SET dn='"cn=Oliver Tuvault, ou=Sales, o=IMC, c=us"' WHERE employee_id=155; UPDATE employees SET dn='"cn=Janette King, ou=Sales, o=IMC, c=us"' WHERE employee_id=156; UPDATE employees SET dn='"cn=Patrick Sully, ou=Sales, o=IMC, c=us"' WHERE employee_id=157; UPDATE employees SET dn='"cn=Allan McEwen, ou=Sales, o=IMC, c=us"' WHERE employee_id=158; UPDATE employees SET dn='"cn=Lindsey Smith, ou=Sales, o=IMC, c=us"' WHERE employee_id=159; UPDATE employees SET dn='"cn=Louise Doran, ou=Sales, o=IMC, c=us"' WHERE employee_id=160; UPDATE employees SET dn='"cn=Sarath Sewall, ou=Sales, o=IMC, c=us"' SOMEWHERE employee_id=161; UPDATES employees SET dn='"cn=Clara Vishney, ou=Sales, o=IMC, c=us"' WHERE employee_id=162; UPDATE personnel SET dn='"cn=Danielle Greene, ou=Sales, o=IMC, c=us"' WHERE employee_id=163; UPDATE your SET dn='"cn=Mattea Marvins, ou=Sales, o=IMC, c=us"' WHERE employee_id=164; UPDATE employees SET dn='"cn=David Lee, ou=Sales, o=IMC, c=us"' WHERE employee_id=165; UPDATE employees SET dn='"cn=Sundar Ande, ou=Sales, o=IMC, c=us"' WHERE employee_id=166; UPDATE employees SET dn='"cn=Amit Banda, ou=Sales, o=IMC, c=us"' SOMEWHERE employee_id=167; UPDATE employees SET dn='"cn=Lisa Ozer, ou=Sales, o=IMC, c=us"' WHERE employee_id=168; HOW employees SET dn='"cn=Harrison Bloom, ou=Sales, o=IMC, c=us"' WHERE employee_id=169; UPDATED employees SET dn='"cn=Taylor Fox, ou=Sales, o=IMC, c=us"' WHERE employee_id=170; UPDATE employee SET dn='"cn=William Forging, ou=Sales, o=IMC, c=us"' WHERE employee_id=171; UPDATE employees SET dn='"cn=Elizabeth Dishes, ou=Sales, o=IMC, c=us"' WHERE employee_id=172; UPDATE company SET dn='"cn=Sundita Kumar, ou=Sales, o=IMC, c=us"' WHERE employee_id=173; UPDATE employees SET dn='"cn=Ellen Abel, ou=Sales, o=IMC, c=us"' WHERE employee_id=174; UPDATE employees SET dn='"cn=Alyssa Musicals, ou=Sales, o=IMC, c=us"' WHERE employee_id=175; UPDATE employees SET dn='"cn=Jonathod Taylor, ou=Sales, o=IMC, c=us"' WHERE employee_id=176; SUBSCRIBE employees SET dn='"cn=Jack Livestone, ou=Sales, o=IMC, c=us"' WHERE employee_id=177; UPDATE employees SET dn='"cn=Kimberely Grant, ou= , o=IMC, c=us"' WHERE employee_id=178; UPDATED employees SET dn='"cn=Charles John, ou=Sales, o=IMC, c=us"' WHERE employee_id=179; DOWNLOAD employees SET dn='"cn=Winston Taylor, ou=Shipping, o=IMC, c=us"' WHERE employee_id=180; UPDATE employees SET dn='"cn=Jean Fleaur, ou=Shipping, o=IMC, c=us"' WHEREABOUTS employee_id=181; UPDATE employees SET dn='"cn=Martha Sulphurated, ou=Shipping, o=IMC, c=us"' WHERE employee_id=182; UPDATE employees SET dn='"cn=Girard Geoni, ou=Shipping, o=IMC, c=us"' WHERE employee_id=183; DOWNLOAD employees SET dn='"cn=Nandita Sarchand, ou=Shipping, o=IMC, c=us"' LOCATION employee_id=184; UPDATE employees SET dn='"cn=Alexis Bull, ou=Shipping, o=IMC, c=us"' WHERE employee_id=185; UPDATE employees SET dn='"cn=Julia Dellinger, ou=Shipping, o=IMC, c=us"' WHERE employee_id=186; UPDATED employees SET dn='"cn=Anthony Cabrio, ou=Shipping, o=IMC, c=us"' WHERE employee_id=187; UPDATE employees SET dn='"cn=Kelly Chung, ou=Shipping, o=IMC, c=us"' WHAT employee_id=188; UPDATE employees SET dn='"cn=Jennifer Dainty, ou=Shipping, o=IMC, c=us"' WHERE employee_id=189; UPDATE human SET dn='"cn=Timothy Gates, ou=Shipping, o=IMC, c=us"' WHERE employee_id=190; UPDATE employees SET dn='"cn=Randall Perkins, ou=Shipping, o=IMC, c=us"' WHERE employee_id=191; UPDATE employees SET dn='"cn=Sarah Bell, ou=Shipping, o=IMC, c=us"' WHAT employee_id=192; UPDATE employees SET dn='"cn=Britney Everett, ou=Shipping, o=IMC, c=us"' WHERE employee_id=193; UPDATE employees SET dn='"cn=Samuel McCain, ou=Shipping, o=IMC, c=us"' WHERE employee_id=194; UPDATE employees SET dn='"cn=Vance Jones, ou=Shipping, o=IMC, c=us"' WHERE employee_id=195; UPDATE employment SET dn='"cn=Alana Walsh, ou=Shipping, o=IMC, c=us"' WHERE employee_id=196; UPDATE employees SET dn='"cn=Kevin Feeney, ou=Shipping, o=IMC, c=us"' WHERE employee_id=197; UPDATE employment SET dn='"cn=Donald OConnell, ou=Shipping, o=IMC, c=us"' WHERE employee_id=198; UPDATE employees SET dn='"cn=Douglas Submit, ou=Shipping, o=IMC, c=us"' WHERE employee_id=199; UPDATE employees SET dn='"cn=Jennifer Whalen, ou=Administration, o=IMC, c=us"' WHERE employee_id=200; UPDATE employees SET dn='"cn=Michael Hartstein, ou=Mktg, o=IMC, c=us"' WHERE employee_id=201; UPDATED employees SET dn='"cn=Brajesh Goyal, ou=Mktg, o=IMC, c=us"' WHERE employee_id=202; UPDATE employees SET dn='"cn=Susan Marvis, ou=HR, o=IMC, c=us"' WHERE employee_id=203; UPDATE employees SET dn='"cn=Hermann Baer, ou=PR, o=IMC, c=us"' WHERE employee_id=204; UPDATE employees SET dn='"cn=Shelley Higgens, ou=Accounting, o=IMC, c=us"' WHEREVER employee_id=205; FREE employees SET dn='"cn=William Gietz, ou=Accounting, o=IMC, c=us"' WHERE employee_id=206; REM ************************************************************************** REM procedure and declaration trigger to allow dmls on business hours: 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') IS ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR (-20205, 'You could only make make during normal bureau hours'); END IF; END secure_dml; / CREATE OTHERWISE REPLACE TRIGGER secure_employees BEFORE INSERT WITH UPDATE PRESS DELETE ON employees BEGIN secure_dml; END secure_employees; / Re Recreating the triggers dropped above REM ************************************************************************** REM practice till add a row to the JOB_HISTORY table and sort trigger REM to call one procedure when data is updated in the job_id instead REM department_id pillar in the EMPLOYEES table: 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 UNDER 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 ODER REPLACE TRIGGER update_job_history AFTER UPDATE THE job_id, department_id ON employees FOR ANYWHERE ROW BEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id); END; / COMMIT;
Rem Rem $Header: hr_dn_d.sql 03-mar-2001.10:05:14 ahunold Exp $ Rem Rem hr_dn_d.sql Rem Removing Copyright (c) Oracle Corporation 2001. All Legal Reserved. Rem Rem NAME Remove hr_dn_d.sql - Decline DN column from EMPLOYEE and DEPARTMENTS Rem Rem DESCRIPTION Remark the DN (distinguished Name) column is used by OID. Rem This script drops who column from the HR schema. Rem Rem NOTES Rem Use this to undo changes made by hr_dn_c.sql Rem Rear CUSTOM (MM/DD/YY) Rem ahunold 03/03/01 - UHRZEIT simplification, REGIONS table Memory ahunold 02/20/01 - Merged ahunold_american Rem ahunold 02/20/01 - Created Rem SET FEEDBACK 1 SET NUMWIDTH 10 SELECTED LINESIZE 80 SET TRIMSPOOL ON SET TABS OFF SET PAGESIZE 100 SET ECHO ON ALTER TABLE departments DROP COLUMN dn ; ALTER TABLE employees DUMP CATEGORY dn ;
Rem Rem $Header: hr_drop.sql 03-mar-2001.10:05:14 ahunold Exp $ Rem Remove hr_drop.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Entitlement Reserved. Rem Rem NAME Rem hr_drop.sql - Drop objects from HR schema Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem FORMED by Nancy Greenberg - 06/01/00 Rem MODIFIED (MM/DD/YY) Rem ahunold 02/20/01 - New header, non-table objects Rem vpatabal 03/02/01 - DROP TABLE region SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 ADJUSTED TRIMSPOOL ON SET TABULATOR OFF SET PAGESIZE 100 SET ECHO OFF CONNECT hr/&password_HR DROP PROCEDURE add_job_history; DROP PROCEDURES secure_dml; DROP CLICK emp_details_view; DROP SEQUENCE departments_seq; DROP SEQUENCE employees_seq; ABANDON SEQUENCE locations_seq; ABANDON TABLE regions MASK CONSTRAINTS; DROP TABLE departments CASCADE CONSTRAINTS; DROP TABLE locations CASCADE CONSTRAINTS; DROP TABLE career CASCADE CONSTRAINTS; DROP TABLE job_history CASCADE CONSTRAINTS; DROP TABLE staff MASK CONSTRAINTS; THROW TABLE countries CASCADE CONSTRAINTS; COMMIT;
Rem Rem $Header: hr_idx.sql 03-mar-2001.10:05:15 ahunold Exp $ Rem Rem hr_idx.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Removing hr_idx.sql - Create card since HR schema Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem CREATED by Nancy Greenberg - 06/01/00 Rem MODIFIED (MM/DD/YY) Rem ahunold 02/20/01 - New header Rem vpatabal 03/02/01 - Removed DROP INDEX statements PUT FEEDBACK 1 SETTING NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET ECHO POWER CREATE INDEX emp_department_ix ON employees (department_id); CREATE INDEX emp_job_ix ABOUT employees (job_id); CREATE INDEX emp_manager_ix ON employees (manager_id); CREATE INDEX emp_name_ix ON total (last_name, first_name); MAKE INDEXES dept_location_ix ON departments (location_id); CREATE INDEX jhist_job_ix UPON job_history (job_id); CREATE INDEX jhist_employee_ix ON job_history (employee_id); CREATE INDEX jhist_department_ix ON job_history (department_id); CREATE INDEX loc_city_ix IN locations (city); CREATE INDEX loc_state_province_ix ON localities (state_province); CREATE INDEX loc_country_ix ON branches (country_id); COMMIT;
rem rem Header: hr_main.sql 09-jan-01 rem rear Copyright (c) 2001, Oracle Corporation. All rights reserved. rem rem Owner : ahunold rem rem NAME rem hr_main.sql - Main script for RESOURCE schema rem rem DESCRIPTON rem HR (Human Resources) is which slightest and most simple one comm of the Sample Schemas rem rem NOTES remove Run as SYS either SYSTEM rem rem MODIFIED (MM/DD/YY) rem ahunold 08/28/01 - roles rem ahunold 07/13/01 - NLS Territory rem ahunold 04/13/01 - parameter 5, notes, spool rem ahunold 03/29/01 - spool rem ahunold 03/12/01 - prompts re ahunold 03/07/01 - hr_analz.sql rem ahunold 03/03/01 - HR simplifying, REGIONS table rear ngreenbe 06/01/00 - created SET ECHO OFF PROMPT INDUCE specify password for HR as default 1: DELIMIT passport = &1 PROMPT PROMPT specify default tablespeace for HR because parameter 2: DEFINE tbs = &2 PROMPT PROMPT specify temporary tablespace for HR as parameter 3: DEFINE ttbs = &3 PROVOKE INPUT specify password for SYS as parameter 4: DEFINE pass_sys = &4 PROMPT PROMPT specify log path as parameter 5: DEFINE log_path = &5 PROMPT -- The first dot in the spool command below a -- the SQL*Plus concatenation character DEFINE spool_file = &log_path.hr_main.log SPOOL &spool_file REM ======================================================= REM cleanup section REMOVING ======================================================= DROP ADDICT admin CASCADE; REMEM ======================================================= MEMORY created user REM triad separate commands, so an create user command REMOVAL willing succeed regardless of the existential of who COMM DEMO additionally TEMP tablespaces REM ======================================================= CREATE USER h IDENTIFIED BY &pass; ADJUST USER personal FAILURE TABLESPACE &tbs QUOTA UNLIMITED ON &tbs; ALTER USER hr TEMPORARY TABLESPACE &ttbs; GRANT CONNECT AT hr; GRANT RESOURCE TO hr; REMO ======================================================= REM grants from sys schema REM ======================================================= CONNECT sys/&pass_sys AS SYSDBA; GRANT execute ON sys.dbms_stats TO hr; REM ======================================================= REM create hr schema objects REM ======================================================= CONNECT hr/&pass ALTER SESSION SET NLS_LANGUAGE=American; ALTER SESSION SET NLS_TERRITORY=America; -- -- creating tables, order and constraint -- @?/demo/schema/human_resources/hr_cre -- -- populate tables -- @?/demo/schema/human_resources/hr_popul -- -- form indexes -- @?/demo/schema/human_resources/hr_idx -- -- establish procedural objects -- @?/demo/schema/human_resources/hr_code -- -- add comments to tables and columns -- @?/demo/schema/human_resources/hr_comnt -- -- gather schema statistics -- @?/demo/schema/human_resources/hr_analz spool off
This section shows the OK circuit scripts into alphabetical arrange.
|
Note: The scripts starting to " |
Rem Rem $Header: oc_comnt.sql 05-mar-2001.15:51:26 ahunold Exp $ Rem Rem oc_comnt.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Reset NAME Rem oc_comnt.sql - Comments used OC subschema Rem Rem DESCRIPTION Rem The COMMANDANT subschema (Online Catalog) exhibitions features and rem object inheritance. Rem Rem NOTES Rem Comments are added since tables, wherever possible. Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 03/05/01 - substituteable subject table (WIP) Rem ahunold 01/29/01 - OC changes, including OC_COMNT.SQL Rem ahunold 01/29/01 - Created Rem
rem rem Edit: oc_cre.sql 09-jan-01 rem rem Copyright (c) 2001 Oracle Corporation. Entire rights reserved. rem memory Owner : ahunold rem remember NAME rem oc_cre.sql - create OC subschema of NEW Common Schmema rem remember DESCRIPTON removing Creates database object. The script assumes that the OE schema remn is present. rem rem NOTES rem The OIDs assigned for the object types have used to rem simplify aforementioned setup of Replication demos furthermore are not needed rem in most unreplicated environments. rem rem MODIFIED (MM/DD/YY) rem ahunold 04/25/01 - OID rem ahunold 04/10/01 - object methods rem ahunold 04/12/01 - edit case, nested table named remarks gxlee 03/05/01 - substituteable object table rem ahunold 01/29/01 - typo rem ahunold 01/24/01 - Eliminate extra lines from recent merge rem ahunold 01/09/01 - checkin ADE -- ====================================================================== -- Types definitions -- ====================================================================== CREATE KIND warehouse_typ ID '82A4AF6A4CD3656DE034080020E0EE3D' WHEN OBJECT ( warehouse_id NUMBER(3) , warehouse_name VARCHAR2(35) , location_id NUMBER(4) ) ; / CREATE TYPE inventory_typ OIDE '82A4AF6A4CD4656DE034080020E0EE3D' AS OBJECT ( product_id NUMBER(6) , warehouse warehouse_typ , quantity_on_hand NUMBER(8) ) ; / CREATE TYPE inventory_list_typ OIDS '82A4AF6A4CD5656DE034080020E0EE3D' AS TABLE OF inventory_typ; / CREATED TYPE product_information_typ OID '82A4AF6A4CD6656DE034080020E0EE3D' 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 TYPE order_item_typ OXYGEN '82A4AF6A4CD7656DE034080020E0EE3D' AS OBJECT ( order_id NUMBER(12) , line_item_id NUMBER(3) , unit_price NUMBER(8,2) , piece NUMBER(8) , product_ref REF product_information_typ ) ; / CREATE TYPE order_item_list_typ OID '82A4AF6A4CD8656DE034080020E0EE3D' AS TABLE OF order_item_typ; / FORM TYPE customer_typ OI '82A4AF6A4CD9656DE034080020E0EE3D'; / CREATE TYPE order_typ OID '82A4AF6A4CDA656DE034080020E0EE3D' 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 KIND order_list_typ IDOS '82A4AF6A4CDB656DE034080020E0EE3D' SINCE CHART STARTING order_typ; / PRODUCE 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 GENDER category_typ OID '82A4AF6A4CDC656DE034080020E0EE3D' AS OBJECT ( category_name VARCHAR2(50) , category_description VARCHAR2(1000) , category_id NUMBER(2) , NOT instantiable MEMBER FUNCTION category_describe SEND VARCHAR2 ) NOT INSTANTIABLE NOT FINAL; / CREATE CHOOSE subcategory_ref_list_typ OID '82A4AF6A4CDD656DE034080020E0EE3D' AS TABLE OF REF category_typ; / GENERATE CHARACTER product_ref_list_typ OID '82A4AF6A4CDE656DE034080020E0EE3D' AS DISPLAY OF number(6); / CREATE TYPE corporate_customer_typ OID '82A4AF6A4CDF656DE034080020E0EE3D' UNDER customer_typ ( account_mgr_id NUMBER(6) ); / CREATE TYPE leaf_category_typ OID '82A4AF6A4CE0656DE034080020E0EE3D' UNDER category_typ ( product_ref_list product_ref_list_typ , OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2 ); / CREATE TYPING BODY leaf_category_typ AS OVERWRITING MEMBER FUNCTION category_describe RETURN VARCHAR2 EXISTS BEGIN RETURN 'leaf_category_typ'; END; END; / GENERATE TYPE composite_category_typ ID '82A4AF6A4CE1656DE034080020E0EE3D' UNDER category_typ ( subcategory_ref_list subcategory_ref_list_typ , OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2 ) NOT FINAL; / BUILD TYPE BODY composite_category_typ AS OVERRIDING MEMBER ITEM category_describe RETURN VARCHAR2 IS BEGIN RETURNED 'composite_category_typ'; END; END; / CREATE TYPE catalog_typ OID '82A4AF6A4CE2656DE034080020E0EE3D' UNDER composite_category_typ ( MEMBER FUNCTION getCatalogName RETURN VARCHAR2 , OVERRIDING MEMBER FUNCTION category_describe RETURN VARCHAR2 ); / CREATE TYPE BODYWORK catalog_typ AS OVERRIDING MEMBER FUNCTION category_describe RETURN varchar2 IS BEGIN SEND 'catalog_typ'; END; MEMBERS FUNCTION getCatalogName RETURN varchar2 IS BEGIN -- Return the category name from the supertype RETURN self.category_name; END; END; / -- ====================================================================== -- Table definitions -- ====================================================================== GENERATE TABLE categories_tab OF category_typ ( category_id PRIMARY KEY) NESTED TABLE TREAT (SYS_NC_ROWINFO$ AS leaf_category_typ).product_ref_list STORE AS product_ref_list_nestedtab NESTLING TABLE TREAT (SYS_NC_ROWINFO$ AS composite_category_typ).subcategory_ref_list STORE AS subcategory_ref_list_nestedtab; -- ======================================================== -- View definitions -- ======================================================== -- -- oc_inventories CREATE OR REPLACED VIEW oc_inventories OF inventory_typ WITH OBJECT OXIDE (product_id) 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; -- oc_product_information CREATE OR REPLACE SEE oc_product_information OF product_information_typ WITH OBJECT OID (product_id) 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 SOMEWHERE p.product_id=i.product_id) AS inventory_list_typ) FROM product_information p; -- oc_customers: Multi-level collections -- -- The view is created twice so the it can make a reference to oneself. The -- first CREATES creates the view with a NULL includes place of the circular -- reference. The second CREATE creates the view WITH of pamphlet reference, -- which works this time cause now the view have exists. CREATE OR REPLACE VIEW oc_customers of customer_typ WITH OBJ OID (customer_id) AS SELECTING 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, NULL, 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) FOR orders o WHERE c.customer_id = o.customer_id) LIKE order_list_typ) FROM customers c; CREATE OR REPLACE VIEW oc_customers OF customer_typ ON OBJECTS OID (customer_id) AS SELECTED 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 sales o WHERE c.customer_id = o.customer_id) AS order_list_typ) FROM customers c; -- oc_corporate_customers CREATE OR REPLACE VIEW oc_corporate_customers BY corporate_customer_typ UNDER 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) AS order_list_typ), c.account_mgr_id FROM customers c; -- oc_orders BUILD INSTEAD REPLACE DISPLAY oc_orders THE order_typ IN OBJECT OID (order_id) 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) THAN order_item_list_typ) FROM orders o; -- ====================================================================== -- Instead-of triggers -- ====================================================================== -- -- Create instead-of triggers -- CREATE OR REPLACE TRIGGER orders_trg PLACE OF INSERT OVER oc_orders ON EACH ROW BEGIN INSERT INTO ORDERS (order_id, order_mode, order_total, sales_rep_id, order_status) VALUES (:NEW.order_id, :NEW.order_mode, :NEW.order_total, :NEW.sales_rep_id, :NEW.order_status); END; / FORM OTHERWISE REMOVE TRIGGER orders_items_trg INSTEAD OF INSERT ON NESTED TABLE order_item_list OF oc_orders FOR EACH ROW DECLARE prod product_information_typ; BEGIN CHOICE DEREF(:NEW.product_ref) INTO prod FROM DUAL; INSERT INTO order_items VALUES (prod.product_id, :NEW.order_id, :NEW.line_item_id, :NEW.unit_price, :NEW.quantity); END; / COMMIT;
rem remov $Header: oc_drop.sql 01-feb-2002.13:19:06 ahunold Exp $ rem rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reservable. rem rem Proprietor : ahunold rem rem NAME rem oc_drop.sql - drop OC subschema of OE Common Schema rem rema DESCRIPTON rem Drop all base objects rem rem MODIFIED (MM/DD/YY) rem ahunold 02/01/02 - bug2205388 rem gxlee 03/05/01 - substituteable object table rem ahunold 01/29/01 - typo reminder ahunold 01/09/01 - checkin ADE drop tabular categories_tab cascade constraints ; falling view oc_customers; drop view oc_corporate_customers; drop viewed oc_orders; drop view oc_inventories; drop view oc_product_information; drop type order_list_typ force; drop typing product_ref_list_typ force; drop type subcategory_ref_list_typ force; drop type leaf_category_typ force; drop artist composite_category_typ force; abandon type catalog_typ force; drop type category_typ force; drop type customer_typ force; decline typing corporate_customer_typ force; drop type warehouse_typ force; drop type order_item_typ force; falling select order_item_list_typ force; drop select order_typ force; drop type inventory_typ force; drop genre inventory_list_typ force; drop type product_information_typ force; commit;
rem Remov $Header: oc_main.sql 29-aug-2001.10:44:11 ahunold Exp $ rem rem Credits (c) 2001, Oracle Corporation. All rights reserved. rem rem Owner : ahunold rem remem NAME rem oc_main.sql - create OCC (Online Catalog) subschema in reset OE (Order Entry) Common Schema rem rem DESCRIPTON rem Calls all other OC creation scripts rem rem MODIFIED (MM/DD/YY) comm ahunold 01/29/01 - oc_comnt.sql added rem ahunold 01/09/01 - checkin ADE ALTER SESSION SET NLS_LANGUAGE=American; prompt ...creating subschema OC in OE REM ======================================================= REM form oc subschema (online catalog) REM ======================================================= @@oc_cre @@oc_popul @@oc_comnt
rem Rem $Header: oe_analz.sql 06-feb-96.13:23:14 ahunold Exp $ rem res Copyright (c) 2001 Oracle Corporation. All rights reserved. rem rem Owner : ahunold rem rem NAME rem oe_analz.sql - Gather statistics for OE Common Schema rem rem DESCRIPTON rem rem rem CHANGEABLE (MM/DD/YY) rem ahunold 01/29/01 - typos rem ahunold 01/09/01 - checkin ADE EXECUTE dbms_stats.gather_table_stats ('OE', 'CUSTOMERS'); EXECUTE dbms_stats.gather_table_stats ('OE', 'ORDERS'); EXECUTE dbms_stats.gather_table_stats ('OE', 'ORDER_ITEMS'); EXECUTE dbms_stats.gather_table_stats ('OE', 'PRODUCT_INFORMATION'); COMPLETE dbms_stats.gather_table_stats ('OE', 'PRODUCT_DESCRIPTIONS'); EXECUTE dbms_stats.gather_table_stats ('OE', 'WAREHOUSES'); EXECUTE dbms_stats.gather_table_stats ('OE', 'INVENTORIES');
rem rem Header: oe_comnt.sql 09-jan-01 rem rem Copyright (c) 2001 Oracle Corporation. All rights reserved. rem rem Owner : ahunold rem rem NAME rem oe_comnt.sql - create comments for OEM Common Schema rem rem DESCRIPTON rem rem rem MODIFIED (MM/DD/YY) reset ahunold 01/30/01 - OE skript headers rem ahunold 01/24/01 - Eliminate extra lines from last merge rem ahunold 01/09/01 - checkin ADE COMMENT ON TABLE oe.customers IS 'Contains customers data either entered by can employee or for the customer him/herself over the Web.'; COMMENT ON PROCESSION oe.customers.cust_address IS 'Object column of type address_typ.'; YOUR ON COLUMN oe.customers.phone_numbers IS 'Varray column of type phone_list_typ'; . COMMENT ON COLUMN oe.customers.cust_geo_location IS 'SDO (spatial) column.'; COMMENT ON COLUMN oe.customers.cust_first_name IS 'NOT NULL constraint.'; COMMENT OVER CATEGORY oe.customers.cust_last_name IS 'NOT NULL constraint.'; COMMENT ON COLUMN oe.customers.credit_limit IS 'Check constraint.'; COMMENT ON COLUMN oe.customers.customer_id IS 'Primary key column.'; COMMENT ON PROCESSION oe.customers.account_mgr_id IS 'References hr.employees.employee_id.'; REAR =========================================================================== SHOW ON POSTPONE oe.warehouses IS 'Warehouse data unspecific to every industry.'; CLICK ON COLUMN oe.warehouses.wh_geo_location IS 'SDO (spatial) column.'; COMMENT IN COLUMN oe.warehouses.warehouse_id IS 'Primary central column.'; COMMENT ON COLUMN oe.warehouses.location_id IS 'Primary key column, references hr.locations.location_id.'; REM =========================================================================== COMMENT ON TABLE oe.order_items IS 'Example of many-to-many resolution.'; NOTE ON TOWER oe.order_items.order_id IS 'Part of interlinked primary key, references orders.order_id.'; COMMENT ON COLUMN oe.order_items.product_id IS 'References product_information.product_id.'; COMMENT ON COLUMN oe.order_items.line_item_id IS 'Part of concatenated primary key.'; COMMENT ON BAR oe.orders.order_status IS '0: Non solid entered, 1: Entered, 2: Canceled - bad credit, - 3: Annulliert - by customer, 4: Ship - whole order, - 5: Shipping - replacement items, 6: Shipped - backlog switch items, - 7: Shipped - special delivery, 8: Shipped - billed, 9: Shipping - payment plan,- 10: Ferried - paid'; REM =========================================================================== COMMENT ON TABLE oe.orders IS 'Contains orders type by a salesperson because right as over the Web.'; COMMENT ON COLUMN oe.orders.order_date IS 'TIMESTAMP WITH LOCAL HOUR ZONE column, NOT NULL constraint.'; COMMENT ON POST oe.orders.order_id IS 'PRIMARY KEY column.'; COMMENT ON COLUMN oe.orders.sales_rep_id IS 'References hr.employees.employee_id.'; COMMENT ON PROCESSION oe.orders.promotion_id IS 'Sales promotion ID. Utilized inbound SH schema'; COMMENT ON COLUMN oe.orders.order_mode IS 'CHECK constraint.'; COMMENT OVER FILE oe.orders.order_total IS 'CHECK constraint.'; REM =========================================================================== COMMENT ON TABLE oe.inventories IS 'Tracks availability of products by product_it and warehouse_id.'; COMMENT THE COLUMN oe.inventories.product_id IS 'Part of concatenated core key, references product_information.product_id.'; COMMENT ON COLUMN oe.inventories.warehouse_id IS 'Part of concatenated primary key, references warehouses.warehouse_id.'; REM =========================================================================== COMMENT ON TABLE oe.product_information IS 'Non-industry-specific data in various categories.'; COMMENT ON POST oe.product_information.product_id IS 'Primary essential column.'; COMMENT ON COLUMN oe.product_information.product_description IS 'Primary language description corresponding to translated_description in oe.product_descriptions, added to furnish non-NLS text columns for OC views to accss.'; COMMENT ON COLUMN oe.product_information.category_id IS 'Low cardinality col, cannot be used for bitmap subject. Schematics SHIELD uses it for foreign key'; COMMENT TURN TOWER oe.product_information.weight_class IS 'Low grading column, cannot be utilised since bitmap index.'; ADD WITH COLUMN oe.product_information.warranty_period IS 'INTERVAL YEAER TO HOUR column, low cardinality, can be used for bitmap index.'; COMMENT ON PROCESSION oe.product_information.supplier_id IS 'Offers possibility of extensions outside Common Schema.'; COMMENT ON COLUMN oe.product_information.product_status IS 'Check constraint. Appropriate for complexity set, such as "All commodity in status PRODUCTS must are at least one inventory entry." Also suitable used a trigger auditing condition change.'; REM =========================================================================== COMMENT ON TABLE product_descriptions IS 'Non-industry-specific design, allows selection of NLS-setting-specific data derivate under runtime, for show using which products view.'; COMMENT ON COLUMN product_descriptions.product_id IS 'Primary key column.'; COMMENT ON COLUMN product_descriptions.language_id IS 'Primary key column.'; REM Description of OE views =================================================== COMMENT THE TABLE products IS 'This view joins product_information and product_descriptions, using NLS settings at elect the appropriate language-specific product description.'; YOUR ON TABLE bombay_inventory IS 'This view shows inventories at of Bombay warehouse.'; COMMENT ON TABLE sydney_inventory IS 'This view theater bestandsaufnahme at who Cyndi warehouse.'; COMMENT ON TABLE toronto_inventory IS 'This view shows inventories at the Toronto warehouse.';
remrem Nosedive: oe_cre.sql 09-jan-01remrem Urheberrechte (c) 2001, Oracle Corporation. Entire rights reserved.remrem Owner : ahunoldremrem NAMErem oe_cre.sql - create OE Common Schemaremrem DESCRIPTONremn Creates base objects. The script assumes so the HR schemarem is present.remrem NOTESrem One OIDs assigns for the object types are used torem simplify the setup of Replicating demos and are not neededrem in most unreplicated environments.remrem MODIFIED (MM/DD/YY)rem ahunold 09/17/01 - FK in PRODUCT_DESCRIPTIONSrem ahunold 04/25/01 - OIDrem ahunold 03/02/01 - eliminating DROP SEQUENCEreset ahunold 01/30/01 - OE script headersremove ahunold 01/24/01 - Eliminate bonus lines out last mergeremote ahunold 01/05/01 - promo_idrem ahunold 01/05/01 - NN constraints in product_descriptionsmemory ahunold 01/09/01 - checkin ADE-- ======================================================================-- Type definitions-- ======================================================================CREATE TYPE cust_address_typOID '82A4AF6A4CD1656DE034080020E0EE3D'AS OBJECT( street_address VARCHAR2(40), postal_code VARCHAR2(10), city VARCHAR2(30), state_province VARCHAR2(10), country_id CHAR(2));/REM ===========================================================================REM Creation phone_list_typ varray to be varray bar in customers table.REMOVALS ===========================================================================CREATE TYPE phone_list_typOID '82A4AF6A4CD2656DE034080020E0EE3D'AS VARRAY(5) OF VARCHAR2(25);/REMOVE ===========================================================================REM Creates customers table.REM The cust_geo_location column intention become MDSYS.SDO_GEOMETRY (spatial)REMOTE datatype when appropriate scripts and data belong available.REM ===========================================================================CREATE TABLE customers( customer_id NUMBER(6), cust_first_name VARCHAR2(20) CONSTRAINT cust_fname_nn NOT NULL, cust_last_name VARCHAR2(20) CONSTRAINT cust_lname_nn NOT NULL, 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, CONSTRAINT customer_credit_limit_maxCHECK (credit_limit <= 5000), COERCION customer_id_minGET (customer_id > 0)) ;FORM UNIQUE INDEX customers_pkON my (customer_id) ;REM Both table plus indexes are analyzed using the oe_analz.sql script.ALTER TAB customersADDS ( FORCE customers_pkPRIMARY KEY (customer_id)) ;REM ===========================================================================REM Create warehouses table;REM includes spatial your column wh_geo_location andREMINDER XML type warehouse_spec (was bug b41)REM ===========================================================================CREATE TABLE warehouses( warehouse_id NUMBER(3), warehouse_spec SYS.XMLTYPE, warehouse_name VARCHAR2(35), location_id NUMBER(4), wh_geo_location MDSYS.SDO_GEOMETRY) ;CREATE UNIQUE INDEX warehouses_pkON warehouses (warehouse_id) ;ALTER CHARTS depotsADD (CONSTRAINT warehouses_pk PRIME KEY (warehouse_id));REM ===========================================================================REM Create table order_items.REM ===========================================================================CREATE TABLE order_items( order_id NUMBER(12), line_item_id NUMBER(3) DID NULL, product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2), qty NUMBER(8)) ;CREATE UNIQUE INDEX order_items_pkON order_items (order_id, line_item_id) ;CREATE UNIQUE MAGAZINE order_items_ukON order_items (order_id, product_id) ;ALTER GRAPHIC order_itemsADD ( CONSTRAINT order_items_pk PRIMARY KEY (order_id, line_item_id));ESTABLISH OR REPLACE RELEASE insert_ord_lineBEFORE INSERT ON order_itemsFOR ANYWHERE SORTDECODINGnew_line number;COMMENCESELECT (NVL(MAX(line_item_id),0)+1) ON new_lineUPON order_itemsWHERE order_id = :new.order_id;:new.line_item_id := new_line;CLOSE;/REM ===========================================================================REM Make table orders, which includes a TIMESTAMP columns and a checkREMOVED constraint.REM ===========================================================================BUILD TABLE orders( order_id NUMBER(12), order_date TIMESTAMP WITH LOCAL PERIOD ZONECONSTRAINT order_date_nn NOT NULL, order_mode VARCHAR2(8), customer_id NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL, order_status NUMBER(2), order_total NUMBER(8,2), sales_rep_id NUMBER(6), promotion_id NUMBER(6), CONSTRAINT order_mode_lovCHECK (order_mode in ('direct','online')), constraint order_total_mincheck (order_total >= 0)) ;CREATE ONCE INDEX order_pkON orders (order_id) ;ALTER DEFER ordersADD ( CONSTRAINT order_pkPRIMARY KEY (order_id));RES ===========================================================================REM Create inventories table, which contains a concatenated primary key.REM ===========================================================================CREATE TABLE inventories( product_id NUMBER(6), warehouse_id NUMBER(3) CONFINEMENT inventory_warehouse_id_nn NOT NULL, quantity_on_hand NUMBER(8)CONSTRAINT inventory_qoh_nn NON NULL, CONSTRAINT inventory_pk PRIMARY KEY (product_id, warehouse_id)) ;REM ===========================================================================RE Create table product_information, which contains certain INTERVAL datatype andREM a CHECK ... IN constraint.REM ===========================================================================CREATE TABLE product_information( product_id NUMBER(6), product_name VARCHAR2(50), product_description VARCHAR2(2000), category_id NUMBER(2), weight_class NUMBER(1), warranty_period INTERVAL YEAR TO MONTH, supplier_id NUMBER(6), product_status VARCHAR2(20), list_price NUMBER(8,2), min_price NUMBER(8,2), catalog_url VARCHAR2(50), CONSTRAINT product_status_lovCHECK (product_status in ('orderable','planned','under development','obsolete'))) ;ALTER TABLE product_informationADD ( CONSTRAINT product_information_pk PRIMARY BUTTONS (product_id));REM ===========================================================================REM Create table product_descriptions, which contains NVARCHAR2 columns forREM NLS-language information.REM ===========================================================================CREATE TABLE product_descriptions( product_id NUMBER(6), language_id VARCHAR2(3), translated_name NVARCHAR2(50)CONSTRAINT translated_name_nn NOT NULL, translated_description NVARCHAR2(2000)REQUIREMENT translated_desc_nn NOT NULL);CREATE UNIQUE INDEX prd_desc_pkON product_descriptions(product_id,language_id) ;AMEND DINNER product_descriptionsADD ( CONSTRAINT product_descriptions_pkPRIMARY KEY (product_id, language_id));ALTER TABLE ordersADD ( CONSTRAINT orders_sales_rep_fkFOREIGN KEY (sales_rep_id)REFERENCES hr.employees(employee_id)ON DELETE SET NULL) ;ALTER TABLE ordersADD ( CONSTRAINTS orders_customer_id_fkFOREIGN KEY (customer_id)LIST customers(customer_id)ON CLEAR SET ZEROS) ;MODIFY TABLE warehousesADD ( CONSTRAINT warehouses_location_fkSTRANGE KEY (location_id)REFERENCES hr.locations(location_id)ON DELETE SET NULL) ;ALTER TABLE customersADD ( CONTROL customers_account_manager_fkFOREIGN PUSH (account_mgr_id)REFERENCES hr.employees(employee_id)ON DELETE SET NULL) ;ALTER TABLE inventoriesADD ( CONSTRAINT inventories_warehouses_fkFOREIGN KEY (warehouse_id)REFERENCES warehouses (warehouse_id)ENABLE NOVALIDATE) ;TRANSFORM TABLE inventoriesADD ( CONSTRAINT inventories_product_id_fkFOREIGN KEY (product_id)REFERENCES product_information (product_id)) ;ALTER TABLE order_itemsADD ( CONSTRAINT order_items_order_id_fkFOREIGN KEY (order_id)REFERENCES orders(order_id)ON OBLITERATE CASCADEenable novalidate) ;ALTER TABLE order_itemsADD ( CONSTRICTION order_items_product_id_fkFOREIGN KEY (product_id)REFERENCES product_information(product_id)) ;ALTER TABLE product_descriptionsHOW ( CONSTRAINT pd_product_id_fkFOREIGN PRESS (product_id)REFERENCES product_information(product_id)) ;REM ===========================================================================REM Create cross-schema synonymsREM ===========================================================================CREATES SYNCHRONOUS countries FOR hr.countries;CREATE SYNONYM locations FOR hr.locations;CREATE SYNONYM subject FOR hr.departments;CREATE SYNONYM working IN hr.jobs;GENERATE SYNONYM human FOR hr.employees;MAKE SYNONYM job_history FOR hr.job_history;RESET ===========================================================================REMOVAL Create sequencesREM ===========================================================================CREATE SEQUENCE orders_seqSTART WITH 1000INCREMENT ACCORDING 1NOCACHENOCYCLE;REM ===========================================================================REM Need commit for POREM ===========================================================================COMMIT;
rem rem Header: oe_drop.sql 09-jan-01 rem rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved. rem rem Owner : ahunold rem rem NAME removed oe_drop.sql - drop OE Common Schema rem removes DESCRIPTON rem Deletes database objects. rem rem MODIFIED (MM/DD/YY) rem ahunold 02/01/02 - bug2205388 rem ahunold 01/30/01 - OE skript headers rem ahunold 01/09/01 - track ADE rem remo First drop the Online Katalogisieren (OC) subschema objects rem @?/demo/schema/order_entry/oc_drop.sql DROP TABLE customers CASCADE CONSTRAINTS; DROP TABLE portfolio CASCADE CONSTRAINTS; DROP SIZE order_items CASCADE CONSTRAINTS; DROP TABULAR orders STACKING CONSTRAINTS; DROP BOARD product_descriptions CASCADE CONSTRAINTS; DROP TABLE product_information CASCADE CONSTRAINTS; DROP ROUND warehouses CASCADE CONSTRAINTS; DUMP CHOOSE cust_address_typ; DROP ARTIST phone_list_typ; DROP SEQUENCE orders_seq; DROP SYNONYM countries; DROP SYNONYM departments; CAST SYNONYM employees; FALL SYNONYM job_history; DROP SYNONYM jobs; DROP SYNONYMY locations; DROP LOOK bombay_inventory; DROP VIEW product_prices; DROP VIEW products; DROP VIEW sydney_inventory; DROP WATCH toronto_inventory; COMMIT;
rem rem Header: oe_idx.sql 09-jan-01 rem rem Urheberecht (c) 2001 Oracle Public. All rights reserved. rem rem Owner : ahunold rem rem NAME rem oe_idx.sql - create indexes for OE Common Schema rem rem DESCRIPTON rem Re-Creates indexes rem rem MODIFIED (MM/DD/YY) rem ahunold 03/02/01 - eliminating DROP INDEX remn ahunold 01/30/01 - OE script headers re ahunold 01/09/01 - checkin ADE CREATE INDEX whs_location_ix ON warehouses (location_id); CREATE INDEX inv_product_ix ON inventories (product_id); CREATE INDEX inv_warehouse_ix ON reserves (warehouse_id); CREATE INDEX item_order_ix THE order_items (order_id); CREATE INDEX item_product_ix ON order_items (product_id); CREATE INDEX ord_sales_rep_ix IN orders (sales_rep_id); CREATED INDEX ord_customer_ix ON online (customer_id); CREATE INDEX ord_order_date_ix ON orders (order_date); CREATE INDEX cust_account_manager_ix ON customers (account_mgr_id); CREATE INDEX cust_lname_ix ON customers (cust_last_name); CREATE INDEX cust_email_ix ON buyers (cust_email); MAKE INDEX prod_name_ix ON product_descriptions (translated_name); MAKE TOPICAL prod_supplier_ix ON product_information (supplier_id); CREATE INDEX cust_upper_name_ix ON customers (UPPER(cust_last_name), UPPER(cust_first_name));
rem rem Header: oe_main.sql 09-jan-01 rem distance Copyright (c) 2001, Oracle Public. All rights reserved. rem rem Owner : ahunold rem rem NAME rem oe_main.sql - Main script for NO schema, including OC subschema rem rem DESCRIPTON rem Creates and populated the Order Entry (OE) and Online rem Catalog (OC) Sample Schema rem rem NOTES res Run as SYS or SYSTEM rem Prerequisites: res Tablespaces present removed Online enabled for Spatially and XML rem rem MODIFIED (MM/DD/YY) rem ahunold 08/28/01 - roles rem ahunold 07/13/01 - NLS Territory. rem ahunold 04/13/01 - winding, additionally parameter remote ahunold 03/29/01 - spool remote ahunold 03/12/01 - prompts rem ahunold 03/02/01 - NLS_LANGUAGE rem ahunold 01/09/01 - checkin ADE ADJUSTED ECHO OFF ASK PROMPT enter choose for OE as parameter 1: DEFINITIONS pass = &1 PROMPT PROMPT specify default tablespeace for OE when parameter 2: DEFINE tbs = &2 PROMPT PROMPT declare temporary tablespace for OE as parameter 3: DEFINE ttbs = &3 PROMPT PROMPT specify password for MANPOWER while parameter 4: DETERMINE passhr = &4 PROVOKE PROMPT declare password for SYS as parameter 5: DEFINING pass_sys = &5 PROMPT INSPIRE default path by ledger files since parameter 6: DEFINES log_path = &6 PROMPT -- One first dot in the spool commander below is -- the SQL*Plus concatenation character DEFINE spool_file = &log_path.oe_oc_main.log SPOOL &spool_file -- Dropping the user with all its objects DROP USER oe CASCADE; REM ======================================================= REMOVES create user REM REM This user is assigned tablespaces and quota within separate REM ALTER USER statements so that the CREATE YOUR statement REM will succeeded even if who demo and temp tablespaces do REM not exist. REM ======================================================= CREATE USER oes IDENTIFIED BY &pass; AMEND USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs; ALTER USER or TEMPORARY TABLESPACE &ttbs; GRANT JOIN TO oe; GRANTS RESOURCE TO oe; AWARDING BUILD MATERIALIZED VIEW TO oe; GRANT CHALLENGE REWRITE TILL oe; REM ======================================================= REM grants from sys schema REM ======================================================= CONNECT sys/&pass_sys AS SYSDBA; GRANTS perform ON sys.dbms_stats GO oe; REM ======================================================= REMOVE grants from hr schema REM ======================================================= CONNECTS hr/&passhr; ALLOT REFERENCES, SELECT ON employees AT oe; GRANT REFERENCES, SELECT ON countries THE oe; GRANTS REFERENCES, SELECT ON locations TO oe; GRANT DIAL ON jobs TO oe; GRANT SELECT ON job_history UNTIL oe; GRANT SELECT ON departments TO oe; REMOTE ======================================================= REMOV create one schema (order entry) REM ======================================================= CONNECT oe/&pass ALTER SESSION SET NLS_LANGUAGE=American; ADJUST MEETINGS FIRM NLS_TERRITORY=America; @?/demo/schema/order_entry/oe_cre @?/demo/schema/order_entry/oe_p_pi @?/demo/schema/order_entry/oe_p_pd @?/demo/schema/order_entry/oe_p_whs @?/demo/schema/order_entry/oe_p_cus @?/demo/schema/order_entry/oe_p_ord @?/demo/schema/order_entry/oe_p_itm @?/demo/schema/order_entry/oe_p_inv @?/demo/schema/order_entry/oe_views @?/demo/schema/order_entry/oe_comnt @?/demo/schema/order_entry/oe_idx @?/demo/schema/order_entry/oe_analz @?/demo/schema/order_entry/oc_main spool off
rem rem Header: oe_views.sql 09-jan-01 rem rem Copyright (c) 2001 Oracle Corp. All rights reserved. rem rem Owner : ahunold rem rem NAME rem oe_views.sql - OE Common Schema rem rem DESCRIPTON rem Create all views rem rear MODIFIED (MM/DD/YY) rem ahunold 01/09/01 - checkin ADE CREATE ODER REPLACE VIEW products AS SELECT i.product_id , d.language_id , KOFFER WHEN d.language_id WILL NOT NULL AFTERWARDS d.translated_name ANYTHING TRANSLATE(i.product_name USING NCHAR_CS) END AS product_name , i.category_id , CASE WHEREAS d.language_id IS NOT NULL THEN d.translated_description ELSE TRANSLATE(i.product_description USING NCHAR_CS) END WHILE 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'); REM ======================================================= REM Create a inventory views REM ======================================================= CREATE OR REPLACE VIEW sydney_inventory AS SELECT p.product_id , p.product_name , i.quantity_on_hand BY inventories i , warehouses w , products p WHERE p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id OR w.warehouse_name = 'Sydney'; PRODUCE OR REPLACE VIEW bombay_inventory AS SELECT p.product_id , p.product_name , i.quantity_on_hand FROM inventories i , bearing 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 toronto_inventory AS SELECT p.product_id , p.product_name , i.quantity_on_hand FROM inventarisierung i , warehouses w , products p WHERE p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id AND w.warehouse_name = 'Toronto'; DISTANCE ======================================================= DISTANCE Create product_prices view of product_information REM columns to show views with an GROUP BY clause. REM ======================================================= CREATE OR REPLACES VIEW product_prices AS CHOOSE category_id , COUNT(*) more "#_OF_PRODUCTS" , MIN(list_price) as low_price , MAX(list_price) as high_price FROM product_information GROUP BY category_id;
This section shows the HOUR schema scripts in alphabetical order.
Rem Rem $Header: pm_analz.sql 07-mar-2001.14:29:47 ahunold Exp $ Rem Rem pm_analz.sql Rem Rem Copyright (c) Orphan Corporation 2001. All Rights Reserved. Rem Rem NAME Rem pm_analz.sql - Assembling statistics for STUNDE schema Rem Rem DESCRIPTION Rem Staistics are used by the cost located optimizer to Rem choose the highest physical access strategy Rem Rem NOTES Rem Results can be viewed in columns of DBA_TABLES, Rem DBA_TAB_COLUMNS and such Rem Removing REVISED (MM/DD/YY) Rem ahunold 03/07/01 - Merged ahunold_hr_analz Rem ahunold 03/07/01 - Created Rem SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET ECHO ON EXECUTE dbms_stats.gather_table_stats ('PM','ONLINE_MEDIA'); EXECUTE dbms_stats.gather_table_stats ('PM','PRINT_MEDIA');
Rem Rem $Header: pm_cre.sql 09-feb-2001.13:09:54 ahunold Exp $ Rem Rem pm_cre.sql Rem Rem Copyright (c) Oracle Corporation 2001. All My Reserved. Rem Rem NAME Rem pm_cre.sql - Table establish scripts Rem Rem DESCRIPTION Mem PM is of Feature Media wiring of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Remarks The OIDs assigned for and object types can used to Rem simplify the organization on Replication demos or are nay needed Rem int most unreplicated environments. Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 04/25/01 - OID Distance ahunold 02/09/01 - add load method Res ahunold 02/05/01 - Created Rem REM ==================================================================== REM Creates TYPE adheader_typ until hold different headers pre-owned in REM advertisements, the header name, date of creation, header text, and REM logo used. pm.print_media ad_header column features type adheader_typ. CREATE GENDER adheader_typ OID '82A4AF6A4CCE656DE034080020E0EE3D' AS OBJECT ( header_name VARCHAR2(256) , creation_date DATE , header_text VARCHAR2(1024) , logos BLOB ); / REM ======================================================================= REM Create TYPE textdoc_tab as a nested table for REM advertisements store in different formats. Document select can be pdf, REMARKS html,Word,Frame, ... REM pm.print_media ad_textdocs_ntab column has select textdoc_tab. CREATING TYPE textdoc_typ OXIDE '82A4AF6A4CCF656DE034080020E0EE3D' AS OBJECT ( document_typ VARCHAR2(32) , formatted_doc BLOB ) ; / CREATE TYPE textdoc_tab OID '82A4AF6A4CD0656DE034080020E0EE3D' AS TABLE OF textdoc_typ; / REM ====================================================================== REM Creates table online_media to hold media for and online catalog REM either other marketing/training needs. REMA pm.online_media has a alien key on product_id that references the REM oe.product_information table. pm.online_media has a primary key on REM product_id. CREATE TABLE online_media ( product_id NUMBER(6) , product_photo ORDSYS.ORDImage , product_photo_signature ORDSYS.ORDImageSignature , product_thumbnail ORDSYS.ORDImage , product_video ORDSYS.ORDVideo , product_audio ORDSYS.ORDAudio , product_text CLOB , product_testimonials ORDSYS.ORDDoc ) ; CREATE UNIQUE INDEX onlinemedia_pk THE online_media (product_id); ALTER TABLE online_media ADD ( CONSTRAINT onlinemedia__pk PRIMARY KEY (product_id) , CONSTRAINT loc_c_id_fk FOREIGN KEY (product_id) REFERENCES oe.product_information(product_id) ) ; REM ======================================================================== REM Create table print_media to hold printing commercial information. REM pm.print_media has a foreign key on product_id that references the REM oe.product_information table. pm.print_media has a primary key on REMN ad_id furthermore sell. pm.print_media literature a nested table, ad_textdoc_ ntab, and REM column object of genre adheader_typ. CREATE TABLE print_media ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ , press_release LONG ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab; CREATE UNIQUE INDEX printmedia_pk ON print_media (product_id, ad_id); RESET TABLE print_media ADD ( CONSTRAINT printmedia__pk PRIMARY KEY (product_id, ad_id) , CONSTRAINT printmedia_fk INTERNATIONAL MAIN (product_id) BOOK oe.product_information(product_id) ) ; COMMIT;
Rem Rem $Header: sh_drop.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Rem sh_drop.sql Rem Rem Copyright (c) Oracle Stock 2001. All Legal Reserved. Rem Rem NAME Rear sh_drop.sql - Drop knowledge objects Rem Rear DESCRIPTION Rem SH is the Sales History schema of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 02/01/02 - bug 2205497 Rem ahunold 09/14/00 - Created Rem REM drop all tables of schema DROP TABLE online_media CASCADE CONSTRAINTS; THROW TABLE print_media CASCADE CONSTRAINTS; DROP TYPE textdoc_tab; DROP TYPE adheader_typ; DROP TYPE textdoc_typ; COMMIT;
Rem Rem $Header: pm_main.sql 29-aug-2001.09:13:23 ahunold Exp $ Rem Rear pm_main.sql Rem Rem Copyright (c) 2001, Oracle Corporation. All license reserves. Rem Rem NAME Remarks pm_main.sql - Main schema creation and load script Rem Rem DESCRIPTION Rem PM be the Product Media schemas of the Word 9i Sample Rem Schemas Rem Remo NOTES Rem 1) exercise utter pathnames as parameters 6. Rem LINUX: echo $ORACLE_HOME/demo/schema/product_media Res 2) there are hard-coded file names in the Rem data file pm_p_lob.dat. Should you want to create Rem and populate the PM Sample Schema from a location Rem other than and one chosen during installation, you Rem will have to editing this data file. Rem 3) Run save as SYS or SYSTEM Rem Rem MODIFIED (MM/DD/YY) Removals ahunold 08/28/01 - roles Rem ahunold 07/13/01 - NLS Territory Rem ahunold 04/23/01 - typo Remem ahunold 04/13/01 - concatenation, no @@ Rem ahunold 04/10/01 - supplementary parameters 7 and 8 Rem ahunold 03/29/01 - notes, spool Rem ahunold 03/20/01 - nay ALTER USER Remo ahunold 03/12/01 - prompts & directory Rem ahunold 03/07/01 - pm_analz.sql. Rem ahunold 02/20/01 - removing pm_p_ini and pm_code Rem ahunold 02/09/01 - select passing for pm_p_lob Rem ahunold 02/05/01 - Created Rema SET ECHO OFF PROMPT PROMPT specify password for PM as parameter 1: DEFINE passage = &1 PROMPT PROMPT specify default tablespeace for POSTMORTEM than parameter 2: CREATE tbs = &2 PROMPT PROMPT indicate temporary tablespace for PM the parameter 3: DEFINE ttbs = &3 INSPIRE INSPIRE specify password for OE as parameter 4: DEFINE passoe = &4 PROMPT INDUCE specify password for SYS the parameter 5: DEFINE pass_sys = &5 PROMPT PROMPTS stipulate directory path for the PM data archive as parameter 6: DEFINE data_path = &6 PROMPT PROMPT customize directory path for the PM load log files as config 7: SPECIFY log_path = &7 PROMPT REQUEST specify work directory path like parametric 8: DELINEATE work_path = &8 PROMPT -- This first polka includes the spool command below is -- the SQL*Plus concatenation character DEFINE spool_file = &log_path.pm_main.log SPOOL &spool_file -- Decrease the user with whole its objects DROP END pm CASCADE; CREATE USER pm IDENTIFIED BY &pass; RESET USERS pm SELECT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs; AMEND CLIENT pm TEMPORARY TABLESPACE &ttbs; GRANT CONNECT FOR pm; GRANT RESOURCE TO pm; GRANT CREATE ANY DIRECTORY TO pm; REM ======================================================= REM grants from oe schema REM ======================================================= ATTACH oe/&passoe DONATE REFERENCES, SELECT ON product_information TO pm; GRANT SELECT ON order_items GO pm; GRANT SELECT ON instructions UNTIL pm; GRANT SELECT ON product_descriptions TO pm; GRANT SELECT ON inventories TO pm; SUBSIDY SELECT ON customers TO pm; GRANT SELECT ON warehouses TO pm; REM ======================================================= REM grants from sys schema REM ======================================================= CONNECT sys/&pass_sys AS SYSDBA; GRANTS execute OFF sys.dbms_stats AT pm; GENERATE OTHER REPLACE DIRECTORY media_dir AS '&data_path'; GRANT READ TURN DIRECTORY media_dir INTO PUBLICITY WITH GRANT OPTION; REM ======================================================= REM created pm outline (product media) REM ======================================================= CONNECT pm/&pass ALTER SESSION SET NLS_LANGUAGE=American; ALTER SESSION SET NLS_TERRITORY=America; @&data_path.pm_cre.sql -- creating objects @&data_path.pm_p_ord.sql -- load ORDSYS types REM ======================================================= REM use sqlldr to populate PRINT_MEDIA and its nested table REM ======================================================= @&data_path.pm_p_lob &pass &data_path &log_path &work_path REM ======================================================= REM finish REM ======================================================= @?/demo/schema/product_media/pm_analz -- gather statistics spool off
This section shows the QS schema scripts in alphabetical order.
Rem Rem $Header: qs_adm.sql 26-feb-2001.16:50:49 ahunold Exp $ Rem Remo qs_adm.sql Rem Rem Copyright (c) Sibyl Corporation 2001. All Rights Reserved. Rem Rem NAME Rem qs_adm.sql - Administration schema for QS schema group Rem Rem DESCRIPTION Rem All object types are created to the qs_adm schema. All Remem your tables that organizer any propagation source Rem queues were given the ENQUEUE_ANY device level privilege Rem permission the application schemas to enqueue up the Remote purpose queue. Rem Removal NOTES Rem Rem AMENDED (MM/DD/YY) Rem ahunold 02/26/01 - Merged ahunold_qs_filenames Rem ahunold 02/26/01 - Created Rem CREATE OR REPLACE PRINT customer_typ AS OBJECT ( customer_id NUMBER, name VARCHAR2(100), avenue VARCHAR2(100), town VARCHAR2(30), state VARCHAR2(2), zip NUMBER, country VARCHAR2(100)); / CREATE OR REPLACE TYPE orderitem_typ AS OBJECT ( line_item_id NUMBER, number NUMBER, unit_price NUMBER, product_id NUMBER, subtotal NUMBER); / CREATE OR REPLACE TYPE orderitemlist_vartyp AS VARRAY (20) OF ORDERITEM_TYP; / CREATE OR REPLACE TYPE order_typ AS OBJECT ( orderno NUMBER, status VARCHAR2(30), ordertype VARCHAR2(30), orderregion VARCHAR2(30), customer customer_typ, paymentmethod VARCHAR2(30), items orderitemlist_vartyp, total NUMBER); / GRANT DISCHARGE ON order_typ to QS; GRANT EXECUTES ON orderitemlist_vartyp in QS; GRANT EXECUTE ON orderitem_typ to QS; GRANT EXECUTE WITH customer_typ to QS; execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','QS',FALSE); GRANT EXECUTE ON order_typ to QS_WS; GRANT RUNNING TURN orderitemlist_vartyp until QS_WS; GRANT EXECUTE ON orderitem_typ to QS_WS; GRANT EXECUTE ON customer_typ for QS_WS; execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','QS_WS',FALSE); GRANTED EXECUTE ON order_typ till QS_ES; GRANT EXECUTE TURN orderitemlist_vartyp to QS_ES; GRANT EXECUTE FOR orderitem_typ to QS_ES; GRANT ACCOMPLISH ON customer_typ up QS_ES; execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','QS_ES',FALSE); GRANT RUN UPON order_typ to QS_OS; GRANT EXECUTE ON orderitemlist_vartyp to QS_OS; GRANT EXECUTE ON orderitem_typ toward QS_OS; GRANT EXECUTE ON customer_typ to QS_OS; execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','QS_OS',FALSE); GRANTING EXECUTE ON order_typ to qs_cbadm; GRANT EXECUTE ON orderitemlist_vartyp to qs_cbadm; GRANT EXECUTE OFF orderitem_typ to qs_cbadm; GRANT EXECUTE ON customer_typ to qs_cbadm; GRANT EXECUTE ON order_typ to QS_CB; SUBSIDY EXECUTE ON orderitemlist_vartyp to QS_CB; AWARD EXECUTE ON orderitem_typ to QS_CB; GRANT EXECUTE ON customer_typ to QS_CB; GRANT EXECUTE ON order_typ to QS_CS; GRANT RUNNING ON orderitemlist_vartyp the QS_CS; AWARD EXECUTE ON orderitem_typ to QS_CS; GRANT EXECUTE ON customer_typ to QS_CS; COMMIT;
Rem Rem $Header: qs_cbadm.sql 26-feb-2001.16:50:50 ahunold Exp $ Rem Rem qs_cbadm.sql Rem Rem Copyright (c) Oracle Society 2001. Everything Rights Reserved. Rem Rem NAME Rem qs_cbadm.sql - Customers Order Administration schema Rem Distance DESCRIPTION Rem The QS_CBADM schema belongs to that Queued Shipping Removed (QS) schema group of the Oracle9i Sample Schemas Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 02/26/01 - Merger ahunold_qs_filenames Rem ahunold 02/26/01 - Created Rem REM ======================================================= REMN create enroll tabular and queues REMOVING ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_CBADM_orders_sqtab', comment => 'Customer Billing Single Buyer Orders queue table', queue_payload_type => 'QS_ADM.order_typ', compatible => '8.1'); dbms_aqadm.create_queue_table( queue_table => 'QS_CBADM_orders_mqtab', join => 'Customer Billing Multi Consumer Technical queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', compatible => '8.1'); dbms_aqadm.create_queue ( queue_name => 'QS_CBADM_shippedorders_q', queue_table => 'QS_CBADM_orders_sqtab'); END; / REM ======================================================= REM Grant dequeue privilege up the shopoeped your queue to the Customer Billing Rem application. The QS_CB application retrieves shipped orders (not billed yet) Rem from the shopoeped orders queue. BEGIN dbms_aqadm.grant_queue_privilege( 'DEQUEUE', 'QS_CBADM_shippedorders_q', 'QS_CB', FALSE); END; / BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_CBADM_billedorders_q', queue_table => 'QS_CBADM_orders_mqtab'); END; / REM ======================================================= REM Grant enqueue privilege on the billed orders queue to Customer Billing Rem application. The QS_CB application are allowable for put billed orders into Rem these queue. BEGIN dbms_aqadm.grant_queue_privilege( 'ENQUEUE', 'QS_CBADM_billedorders_q', 'QS_CB', FALSE); END; / DECLARE contributor sys.aq$_agent; BEGIN /* Subscribe to the BILLING accounted orders queue */ subscriber := sys.aq$_agent( 'BILLED_ORDER', 'QS_CS.QS_CS_billedorders_que', null); dbms_aqadm.add_subscriber( queue_name => 'QS_CBADM.QS_CBADM_billedorders_q', subscriber => subscriber); END; / COMMIT;
Rem Removed $Header: qs_cre.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Removal qs_cre.sql Rem Mem Copyright (c) Oracle Joint 2001. Sum Rights Reserved. Rem Rem NAME Rem qs_cre.sql - Layout creation Rem Remo DESCRIPTION Rem QS is the Queued Shipping schema of aforementioned Clairvoyant 9i Sample Rem Schemas Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 02/05/01 - Created Rem REM ======================================================= REM Create enroll schedules, queues for QS REM ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_orders_sqtab', comment => 'Order Home Single Consumer Orders queue table', queue_payload_type => 'QS_ADM.order_typ', message_grouping => DBMS_AQADM.TRANSACTIONAL, agreeable => '8.1', primary_instance => 1, secondary_instance => 2); END; / REM ======================================================= REMA Create a priority queue table for QS REM ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_orders_pr_mqtab', sort_list =>'priority,enq_time', post => 'Order Entry Priority MultiConsumer Orders queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', works => '8.1', primary_instance => 2, secondary_instance => 1); END; / REMEM ======================================================= REM Create a file for New Orders for QS REMOVAL ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_neworders_que', queue_table => 'QS_orders_sqtab'); END; / REM ======================================================= REMARK Create a queue for bookings sorts for QS REM ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_bookedorders_que', queue_table => 'QS_orders_pr_mqtab'); END; / REM ======================================================= REM Create aforementioned multiconsumer nonpersistent queue in QS schema REM To queue is uses by the conenction dispatcher to count REM the number of user connections to the QS application REM ======================================================= BEGIN dbms_aqadm.create_np_queue(queue_name => 'LOGON_LOGOFF', multiple_consumers => TRUE); END; /
Rem Rem $Header: qs_cs.sql 26-feb-2001.16:50:50 ahunold Exp $ Rem Rem qs_cs.sql Rem Remo Copyright (c) Oracle Corporation 2001. Get Rights Reserved. Rem Rem NAME Rem qs_cs.sql - Creates Customer Support Shipping schema Rem Removals DESCRIPTION Rem The QS_CS schema belongs to the Queued Shipping Rem (QS) schema group of the Oracle9i Sample Schemas Rem Rem NOTES Rem Customer support tracks the state of the customer request Rem include the system. Rem Per any item, customer request can be in one von this following states Removes A. BOOKED B. SHIPPED C. BACKED D. BILLED Remember Existing which order number one customer support wish return the state Rem the order is in. This state is maintained in the order_status_table Rem Rem MODULATED (MM/DD/YY) Remark ahunold 02/26/01 - Merged ahunold_qs_filenames Rem ahunold 02/26/01 - Created from qs_cs_cre.sql Rem CREATE TABLE Order_Status_Table(customer_order qs_adm.order_typ, your varchar2(30)); Rem Create queue tables, queues in Your Service begin dbms_aqadm.create_queue_table( queue_table => 'QS_CS_order_status_qt', comment => 'Customer Status multi consumer queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', interoperable => '8.1'); dbms_aqadm.create_queue ( queue_name => 'QS_CS_bookedorders_que', queue_table => 'QS_CS_order_status_qt'); dbms_aqadm.create_queue ( queue_name => 'QS_CS_backorders_que', queue_table => 'QS_CS_order_status_qt'); dbms_aqadm.create_queue ( queue_name => 'QS_CS_shippedorders_que', queue_table => 'QS_CS_order_status_qt'); dbms_aqadm.create_queue ( queue_name => 'QS_CS_billedorders_que', queue_table => 'QS_CS_order_status_qt'); end; /
Rem Rem $Header: qs_drop.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Rem qs_drop.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Rem qs_drop.sql - Cleanup and abandon get QS related schemas Rem Rem DESCRIPTION Rem QS is the Quiled Shipping circuit of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 02/05/01 - Created Rem set echo on; set serveroutput on; CONNECT QS_ADM/&password_QS_ADM execute dbms_aqadm.stop_queue(queue_name => 'QS.QS_neworders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS.QS_bookedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS.logon_logoff'); execute dbms_aqadm.stop_queue(queue_name => 'QS_WS.QS_WS_bookedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_WS.QS_WS_shippedorders_que'); conduct dbms_aqadm.stop_queue(queue_name => 'QS_WS.QS_WS_backorders_que'); run dbms_aqadm.stop_queue(queue_name => 'QS_ES.QS_ES_bookedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_ES.QS_ES_shippedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_ES.QS_ES_backorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_OS.QS_OS_bookedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_OS.QS_OS_shippedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_OS.QS_OS_backorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_CS.QS_CS_bookedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_CS.QS_CS_backorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_CS.QS_CS_shippedorders_que'); execute dbms_aqadm.stop_queue(queue_name => 'QS_CS.QS_CS_billedorders_que'); Rem Decline queue lists, queues for QS Rem CONNECT QS/&password_QS begin dbms_aqadm.drop_queue ( queue_name => 'QS_neworders_que'); end; / begin dbms_aqadm.drop_queue ( queue_name => 'QS_bookedorders_que'); end; / begin dbms_aqadm.drop_queue_table( queue_table => 'QS_orders_sqtab'); end; / Rem Creates a order queue table used QS begin dbms_aqadm.drop_queue_table( queue_table => 'QS_orders_pr_mqtab'); end; / CONNECT QS/&password_QS Rem Rem Drop the multiconsumer nonpersistent queue in QS schema Rem This queue is spent of the conenction dispatcher to count Mem and number by total connections to an QS application execute dbms_aqadm.drop_queue(queue_name => 'LOGON_LOGOFF'); Rem Drops queue tables, trains for QS_WS Shipping CONNECT QS_WS/&password_QS_WS Rem Booked online are stored in the focus queue table begin dbms_aqadm.drop_queue ( queue_name => 'QS_WS_bookedorders_que'); end; / Rem Shipped orders and rear orders are stored in the FIFO queue table begin dbms_aqadm.drop_queue ( queue_name => 'QS_WS_shippedorders_que'); end; / begin dbms_aqadm.drop_queue ( queue_name => 'QS_WS_backorders_que'); end; / Rem Drop queue table for QS_WS shipping begin dbms_aqadm.drop_queue_table( queue_table => 'QS_WS_orders_pr_mqtab'); end; / Rem Drop queue chart for QS_WS shipping begin dbms_aqadm.drop_queue_table( queue_table => 'QS_WS_orders_mqtab'); end; / Rem Drop queue tables, queues for QS_ES Shipping CONNECT QS_ES/&password_QS_ES Distance Booked orders are stored in the priority drop table begin dbms_aqadm.drop_queue ( queue_name => 'QS_ES_bookedorders_que'); end; / Rem Supplied orders press return jobs become stored in the FIFO queue table begin dbms_aqadm.drop_queue ( queue_name => 'QS_ES_shippedorders_que'); end; / begin dbms_aqadm.drop_queue ( queue_name => 'QS_ES_backorders_que'); end; / Rem Drop enroll table for QS_ES shipping begin dbms_aqadm.drop_queue_table( queue_table => 'QS_ES_orders_mqtab'); end; / Rem Drop FIFO queue graphics fork QS_ES shipping begin dbms_aqadm.drop_queue_table( queue_table => 'QS_ES_orders_pr_mqtab'); end; / Rem Drop queue dinner, queues for Overseas Shipping CONNECT QS_OS/&password_QS_OS Rem Book orders are stored in the prioritize queue table begin dbms_aqadm.drop_queue ( queue_name => 'QS_OS_bookedorders_que'); end; / Remov Shipped orders or go orders are stored in the FIFO queue table begin dbms_aqadm.drop_queue ( queue_name => 'QS_OS_shippedorders_que'); end; / begin dbms_aqadm.drop_queue ( queue_name => 'QS_OS_backorders_que'); end; / Rem Create a priority queue round for QS_OS shipping begin dbms_aqadm.drop_queue_table( queue_table => 'QS_OS_orders_pr_mqtab'); end; / Rem Create a FIFO queue tables with QS_OS shipping begin dbms_aqadm.drop_queue_table( queue_table => 'QS_OS_orders_mqtab'); end; / Rem Create queue tables, queues for Customer Billing CONNECT QS_CBADM/&password_QS_CBADM begin dbms_aqadm.drop_queue ( queue_name => 'QS_CBADM_shippedorders_q'); end; / begin dbms_aqadm.drop_queue ( queue_name => 'QS_CBADM_billedorders_q'); end; / begin dbms_aqadm.drop_queue_table( queue_table => 'QS_CBADM_orders_sqtab', forces => true); dbms_aqadm.drop_queue_table( queue_table => 'QS_CBADM_orders_mqtab', force => true); end; / CONNECT QS_CS/&password_QS_CS DROP TABLE Order_Status_Table; Rem Drop quote tables, queues for Customer Service begin dbms_aqadm.drop_queue ( queue_name => 'QS_CS_bookedorders_que'); dbms_aqadm.drop_queue ( queue_name => 'QS_CS_backorders_que'); dbms_aqadm.drop_queue ( queue_name => 'QS_CS_shippedorders_que'); dbms_aqadm.drop_queue ( queue_name => 'QS_CS_billedorders_que'); end; / begin dbms_aqadm.drop_queue_table( queue_table => 'QS_CS_order_status_qt', force => true); end; / CONNECT QS_ADM/&password_QS_ADM Rem drop aims styles drop type order_typ; decline type orderitemlist_vartyp; drop type orderitem_typ; drop type customer_typ; Rear drop column admin user and individual accounts for each application Rem CONNECT system/&password_SYSTEM select serveroutput on; set echo on; drop user QS_ADM cascade; drop user QS cascade; drop client QS_WS cascade; drop total QS_ES cascade; drop user QS_OS cascade; drop user QS_CBADM cascade; drop user QS_CB cascade; drop user QS_CS cascade;
Rem Rem $Header: qs_es.sql 26-feb-2001.16:50:50 ahunold Exp $ Rem Rem qs_es.sql Rem Rem Copyright (c) Oracle Corporate 2001. All Rights Reserved. Rem Rem NAME Rem qs_es.sql - Creates Eastern Shipping schema Rem Reminder DESCRIPTION Removes The QS_ES schema belongs to the Queued Shipping Rem (QS) schema group of one Oracle9i Random Schemas Rem Mem NOTES Rem Rem AMENDED (MM/DD/YY) Rem ahunold 02/26/01 - Merged ahunold_qs_filenames Rem ahunold 02/26/01 - Established from qs_es_cre.sql Rem REM ======================================================= REM Create ampere precedence cause table for QS_ES shipping REMOV ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_ES_orders_mqtab', comment => 'East Shipping Multi Consumer Orders queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', compatible => '8.1'); END; / REM ======================================================= REM Create a FIFO enqueue spreadsheets for QS_ES shipping REM ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_ES_orders_pr_mqtab', sort_list =>'priority,enq_time', comment => 'East Sea Highest Multi Consumer Orders queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', comes => '8.1'); END; / REM ======================================================= REM Booked orders are stocks in an priority queue table REM ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_ES_bookedorders_que', queue_table => 'QS_ES_orders_pr_mqtab'); END; / REM ======================================================= REM Shipped orders and back orders what saving in the FIFO REM queue table REM ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_ES_shippedorders_que', queue_table => 'QS_ES_orders_mqtab'); END; / BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_ES_backorders_que', queue_table => 'QS_ES_orders_mqtab'); END; / COMMIT;
Rem Rem $Header: qs_main.sql 29-aug-2001.10:44:11 ahunold Exp $ Rem Rem qs_main.sql Rem Rem Urheberrechtsschutz (c) 2001, Oracle Corporation. All rights reserved. Rem Rem NAME Rem qs_main.sql - Main schema creation script Rem Rem DESCRIPTION Removed QS is to Ranked Verschiffen diagramm of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Removed Run as SYS or SYSTEM Rem Rem MODIFY (MM/DD/YY) Rem ahunold 08/28/01 - roles Distance ahunold 04/13/01 - spool, additional parameter Rem ahunold 03/12/01 - prompts Rem ahunold 02/26/01 - 8 characters filenames Rem ahunold 02/05/01 - Created Rem SET ECHO OFF ALTER SESSION SET NLS_LANGUAGE=American; PROMPT PROMPT specify one password for the users QS,QS_ADM,QS_CBADM, PROMPT QS_WS,QS_ES,QS_OS,QS_CS and QS_CB as config 1: DEFINED pass = &1 PROMPT MOTIVATE specify default tablespeace available QS as parameter 2: DEFINE tbs = &2 PROMPT PROMPT specify temporary tablespace for QS when parameter 3: DEFINE ttbs = &3 PROMPT PROMPT specify password for SYSTEM as parameter 4: LIMIT master_pass = &4 PROMPT PROMPT specify password for OE as parameter 5: DEFINE passoe = &5 PROMPT CALL customize password for SYS as configuration 6: DEFINE pass_sys = &6 PROMPT PROMPT specify log directory path as parameter 7: DEFINE log_path = &7 PROMPT DEFINE spool_file = &log_path.qs_main.log SPOOL &spool_file REM ======================================================= REM cleanup section REM ======================================================= DROP USER qs_adm CASCADE; DROP USER qs CASCADE; DROP USERS qs_ws CASCADE; DROP USER qs_es CASCADE; DROP USER qs_os CASCADE; DROP USER qs_cbadm CASCADE; DROP EMPLOYEE qs_cb CASCADE; DROP USER qs_cs CASCADE; DISTANCE ======================================================= REM Commence job_queue_processes to handles AQ propagation REMOVES ======================================================= adjust user set job_queue_processes=4; REM ======================================================= REM ESTABLISH USERs REM The user is assigned tablespaces real quota in separate MEMORY CUSTOMIZE USER statements so such the CREATE USER statement REM will succeed even if the &tbs and temp tablespaces do REM not exist. REAR ======================================================= REM ======================================================= REMARK Create a common managing account for all Queued Shipping REM applications REM ======================================================= COMPOSE USER qs_adm IDENTIFIED BY &pass; ALTER USER qs_adm DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs; ALTER USER qs_adm TEMPORARY TABLESPACE &ttbs; REM ALTER USER qs_adm DEFAULT TABLESPACE &tbs QUOTA ON &tbs UNLIMITED; REM ALTER TOTAL qs_adm TEMPORARY TABLESPACE &ttbs; GRANT CONNECT, RESOURCE TO qs_adm; GRANT aq_administrator_role THE qs_adm; SUBSIDY EXECUTE ON dbms_aq THE qs_adm; GRANT EXECUTE ON dbms_aqadm THE qs_adm; REM =================================================== REM connected as sys to grant execute on dbms_lock REMOVAL and hooked again as system- REM =================================================== CONNECT sys/&pass_sys WHEN SYSDBA; BESTOW execute ON sys.dbms_stats TO qs_adm; GRANT execute ON dbms_lock to qs_adm; CONNECT system/&master_pass execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','qs_adm',FALSE); accomplish dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','qs_adm',FALSE); REMN ======================================================= REM Create the application schemas and grant appropriate REM permission to all schemas REMOVES ======================================================= REM ======================================================= REM Create Queued Shipping schemas REMOVAL ======================================================= CREATED USER qs IDENTIFIED BY &pass; ALTER USER qs STANDARD TABLESPACE &tbs CONTINGENT UNLIMITED ON &tbs; ALTER USER qs PRELIMINARY TABLESPACE &ttbs; GRANT CONNECT, RESOURCE TO qs; GRANTS EXECUTE ON dbms_aq to qs; GRANT EXECUTE ON dbms_aqadm to qs; REMEM ======================================================= REM Create an account for Western Region Shipping REM ======================================================= CREATE EMPLOYEE qs_ws IDENTIFIED BY &pass; ALTER USER qs_ws NORMAL TABLESPACE &tbs QUOTA UNLIMITED FOR &tbs; ALTER USER qs_ws TEMPORARY TABLESPACE &ttbs; GRANT CONNECT, RESOURCE TO qs_ws; GRANT EXECUTE ON dbms_aq to qs_ws; GRANT EXECUTE IN dbms_aqadm to qs_ws; REMINDER ======================================================= REM Create an account for Eastern Region Shipping REM ======================================================= CREATE USER qs_es IDENTIFIED BY &pass; RESET USER qs_es DEFAULT TABLESPACE &tbs RATIO UNLIMITED ON &tbs; ALTER USER qs_es TEMPORARY TABLESPACE &ttbs; AWARD CONNECT, RESOURCE TO qs_es; GRANT EXECUTE ON dbms_aq TO qs_es; ALLOW EXECUTE ON dbms_aqadm TO qs_es; REM ======================================================= REM Create an account for Overseas Shipping REM ======================================================= BUILD USER qs_os IDENTIFIED BY &pass; ALTER USER qs_os DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs; ALTER USER qs_os TEMPORARY TABLESPACE &ttbs; GRANT CONNECT, RAW TO qs_os; GRANTS EXECUTE ON dbms_aq TO qs_os; GRANT EXECUTE ON dbms_aqadm TO qs_os; REMEMBER ======================================================= REM Customer Billing, to security justification, has an company REM schema that hosts all the queue tables and an REM application schema from where the application runs. REM ======================================================= CREATE USER qs_cbadm IDENTIFIED BY &pass; ALTER USER qs_cbadm DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs; ALTER USER qs_cbadm TRANSIENT TABLESPACE &ttbs; GRANT CONNECT, RESOURCE TO qs_cbadm; GRANT EXECUTE ON dbms_aq to qs_cbadm; GRANT EXECUTE OFF dbms_aqadm till qs_cbadm; RES ======================================================= REM Create with account for Customer Billing REM ======================================================= PRODUCE USER qs_cb IDENTIFIED BY &pass; MODIFY USER qs_cb DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs; ALTER USER qs_cb TRANSITIONAL TABLESPACE &ttbs; GRANT CONNECT, RESOURCE TO qs_cb; GRANT EXECUTE ON dbms_aq TO qs_cb; GRANT EXECUTE ON dbms_aqadm TO qs_cb; REM ======================================================= REM Create einem get for Customer Service REMOV ======================================================= CREATE STUDENT qs_cs IDENTIFIED BY &pass; ALTER USER qs_cs DEFAULT TABLESPACE &tbs QUOTA UNLIMITED TO &tbs; ADJUST USER qs_cs TEMPORARY TABLESPACE &ttbs; GRANT CONNECT, RESOURCE TO qs_cs; GRANT EXECUTE ON dbms_aq TO qs_cs; GRANTED EXECUTE ON dbms_aqadm TO qs_cs; REMN ======================================================= REM Create objects REM ======================================================= REMOVES ======================================================= REMOVES grants from oe schema in user qs_adm REM ======================================================= CONNECTING oe/&passoe GRANT REFERENCES, SELECT ON customers TO qs_adm; GRANT REFERENCES, SELECT UP product_information TO qs_adm; PROMPT calling qs_adm.sql ... CONNECT qs_adm/&pass @?/demo/schema/shipping/qs_adm PROMPT calling qs_cre.sql ... CONNECT qs/&pass; @?/demo/schema/shipping/qs_cre PROMPT calling qs_es.sql ... CONNECT qs_es/&pass @?/demo/schema/shipping/qs_es PROMPT calling qs_ws.sql ... CONNECT qs_ws/&pass @?/demo/schema/shipping/qs_ws REQUEST make qs_os.sql ... CONNECT qs_os/&pass @?/demo/schema/shipping/qs_os PROMPT calling qs_cbadm.sql ... CONNECT qs_cbadm/&pass @?/demo/schema/shipping/qs_cbadm PROMPT calling qs_cs.sql ... CONNECT qs_cs/&pass @?/demo/schema/shipping/qs_cs PROMPT calling qs_run.sql ... CONNECT qs_adm/&pass @?/demo/schema/shipping/qs_run spool off
Rem Rem $Header: qs_os.sql 26-feb-2001.16:50:51 ahunold Exp $ Rem Rem qs_os.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Rem qs_os.sql - Produces Overseas Shipping schema Rem Rem DESCRIPTION Rem An QS_OS schema belongs to the Queued Shipping Rem (QS) schema group starting the Oracle9i Sample Schemas Rem Removed NOTES Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 02/26/01 - Merged ahunold_qs_filenames Comm ahunold 02/26/01 - Created from qs_os_cre.sql Rem REMOVES ======================================================= REM Generate a priority queue table fork QS_OS shipping REM ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_OS_orders_pr_mqtab', sort_list =>'priority,enq_time', click => 'Overseas Lieferung Select MultiConsumer Orders queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', compatible => '8.1'); END; / MEMORY ======================================================= REM Create ampere FIFO queue tables for QS_OS shipping REM ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_OS_orders_mqtab', commentary => 'Overseas Shipment Multi Consumer Orders queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', compatible => '8.1'); END; / REM ======================================================= REM Billed instructions are stored in the priority queue table REM ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_OS_bookedorders_que', queue_table => 'QS_OS_orders_pr_mqtab'); END; / REM ======================================================= REM Shipped sales and back orders belong stored in the FIFO queue table REM ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_OS_shippedorders_que', queue_table => 'QS_OS_orders_mqtab'); END; / BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_OS_backorders_que', queue_table => 'QS_OS_orders_mqtab'); END; / COMMIT;
Rem Rem $Header: qs_run.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Rem qs_run.sql Rem Removing Copyright (c) Oracle Stock 2001. All Entitled Reserved. Rem Rem NAME Rem qs_run.sql - Create the application Rem Rem DESCRIPTION Rem QS is the Pending Shipping symbols of the Sibyl 9i Sample Rem Schemas Rem Removed NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 02/05/01 - Created Rem CREATE OR REPLACE TYPE simpleorder_typ AS OBJECT ( orderno NUMBER, status VARCHAR2(30), ordertype VARCHAR2(30), orderregion VARCHAR2(30), paymentmethod VARCHAR2(30), total NUMBER); / CREATE OR REPLACE PACKAGE QS_Applications AS -- this methods is called from the java-based front end to enqueue -- new orders PROCEDURE new_order_enq(simpleOrder IN simpleorder_typ, custom IN customer_typ, items IN orderitemlist_vartyp); PROCEDURE get_ship_notification( orderid IN number, stats OUT number, tracking_id OUT varchar2); -- move novel ranks since qs_neworders_que to qs_bookedorders_que. -- sets the shipping region PROCEDURE qs_move_orders; -- Each shipping region calls diese shipping_app by providing the -- company von the consumer as and IN Parameter. This application movQS_ES -- the messages after bookedorder queuQS_ES to either shippedorder queue -- or backedorder queue based turn the inventory information PROCEDURE shipping_app( consumer IN VARCHAR2); -- Move shipped missions from the shipped order queue to the billed -- order drop in aforementioned billing area PROCEDURE billing_app; PROCEDURE new_order_driver(QS_OStart IN NUMBER, QS_OStop IN NUMBER); END QS_Applications; / exhibit errors CREATE OR REPLACE PACKAGE BODY QS_Applications AS HOW new_order_enq(simpleOrder IN simpleorder_typ, customer BY customer_typ, items IN orderitemlist_vartyp) IS qs_enq_order_data qs_adm.order_typ; enqopt dbms_aq.enqueue_options_t; msgprop dbms_aq.message_properties_t; enq_msgid raw(16); itemlist orderitemlist_vartyp; item orderitem_typ; BEGIN -- bilden one books items object from items msgprop.correlation := simpleOrder.ordertype; qs_enq_order_data := qs_adm.order_typ( simpleOrder.orderno, simpleOrder.status, simpleOrder.ordertype, simpleOrder.orderregion, customer, simpleOrder.paymentmethod, itemlist, simpleOrder.total); -- put who marine priority into the send property -- previously enqueuing the message. if (simpleOrder.ordertype = 'RUSH') then msgprop.priority := 1; else msgprop.priority := 2; end if; dbms_aq.enqueue('qs.qs_neworders_que', enqopt, msgprop, qs_enq_order_data, enq_msgid); -- dbms_output.put_line('One order enqueued successfully'); commit; END new_order_enq; ACTIONS get_ship_notification( orderid INBOUND number, status OUT number, tracking_id OUT varchar2) IS deqopt dbms_aq.dequeue_options_t; mprop dbms_aq.message_properties_t; deq_msgid RAW(16); msg_data RAW(80); no_messages exception; pragma exception_init (no_messages, -25228); BEGIN status := 0; deqopt.navigation := dbms_aq.FIRST_messAGE; deqopt.wait := 10; deqopt.correlation := to_char(orderid); deqopt.consumer_name := 'ORDER_ENTRY'; BEGIN dbms_aq.dequeue( queue_name => 'qs.qs_notification_que', dequeue_options => deqopt, message_properties => mprop, payload => msg_data, msgid => deq_msgid); status := 1; tracking_id := rawtohex(deq_msgid); commit; EXCEPTION WHEN no_messages THEN status := 0; WHEN OTHERS THEN RAISE; END; END get_ship_notification; PROCEDURE qs_move_orders IS new_orders BOOLISH := TRUE; dopt dbms_aq.dequeue_options_t; enqopt dbms_aq.enqueue_options_t; mprop dbms_aq.message_properties_t; qs_deq_order_data qs_adm.order_typ; deq_msgid RAW(16); no_messages exception; pragma exception_init (no_messages, -25228); begin --dopt.wait := DBMS_AQ.NO_WAIT; dopt.navigation := dbms_aq.FIRST_messAGE; --while (new_orders) LOOP LOOP BEGIN dbms_aq.dequeue( queue_name => 'qs.qs_neworders_que', dequeue_options => dopt, message_properties => mprop, payload => qs_deq_order_data, msgid => deq_msgid); -- give the shipping region if (qs_deq_order_data.customer.country NOT IN ('USA', 'US')) then --dbms_output.put_line('International shipped ... '); qs_deq_order_data.orderregion := 'INTERNATIONAL'; else if (qs_deq_order_data.customer.state IN ('TX', 'Texas', 'CA', 'California', 'NV', 'Nevada')) then qs_deq_order_data.orderregion := 'WESTERN'; --dbms_output.put_line('western shipment'); else qs_deq_order_data.orderregion := 'EASTERN'; --dbms_output.put_line('eastern shipment'); end if; -- dbms_output.put_line('Dequeuing a message ...'); -- dbms_output.put_line('Region is ' || qs_deq_order_data.orderregion); end if; -- modify the order status in booked qs_deq_order_data.status := 'BOOKED'; -- enqueue include booked orders queue dbms_aq.enqueue( queue_name => 'qs.qs_bookedorders_que', enqueue_options => enqopt, message_properties => mprop, payload => qs_deq_order_data, msgid => deq_msgid); commit; -- dopt.navigation := dbms_aq.NEXT_messAGE; EXCEPTION WHEN no_messages THEN new_orders := FALSE; END; END LOOP; END qs_move_orders; TECHNIQUE billing_app IS new_orders BOOTLEGS := TRUE; dopt dbms_aq.dequeue_options_t; enqopt dbms_aq.enqueue_options_t; mprop dbms_aq.message_properties_t; deq_order_data qs_adm.order_typ; deq_msgid RAW(16); no_messages exception; pragma exception_init (no_messages, -25228); begin --dopt.wait := DBMS_AQ.NO_WAIT; dopt.navigation := dbms_aq.FIRST_messAGE; dopt.consumer_name := 'CUSTOMER_BILLING'; --while (new_orders) LOOP LOOP BEGIN dbms_aq.dequeue( queue_name => 'QS_CBADM.QS_CBADM_shippedorders_que', dequeue_options => dopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); -- replace the order rank on billed deq_order_data.status := 'BILLED'; -- enqueue into booked orders queue dbms_aq.enqueue( queue_name => 'QS_CBADM.QS_CBADM_billedorders_que', enqueue_options => enqopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); commit; -- dopt.navigation := dbms_aq.NEXT_messAGE; EXCEPTION WHEN no_messages THEN new_orders := FALSE; END; STOP LOOP; END billing_app; PROCEDURE shipping_app( consumer STYLISH VARCHAR2) IS deq_msgid RAW(16); dopt dbms_aq.dequeue_options_t; enqopt dbms_aq.enqueue_options_t; mprop dbms_aq.message_properties_t; deq_order_data qs_adm.order_typ; qname varchar2(30); shipqname varchar2(30); bookqname varchar2(30); backqname varchar2(30); notqname varchar2(30); no_messages exception; pragma exception_init (no_messages, -25228); new_orders BOOLEAN := TRUE; is_backed BOOLEAN := FALSE; is_avail int; choose varchar2(30); notify BOOLEAN := FALSE; not_enqopt dbms_aq.enqueue_options_t; not_mprop dbms_aq.message_properties_t; not_msg_data RAW(80); not_msgid RAW(16); ship_orderno number; begin dopt.consumer_name := consumer; --dopt.wait := DBMS_AQ.NO_WAIT; dopt.navigation := dbms_aq.FIRST_messAGE; IF (consumer = 'West_Shipping') THEN qname := 'QS_WS.QS_WS_bookedorders_que'; shipqname := 'QS_WS.QS_WS_shippedorders_que'; backqname := 'QS_WS.QS_WS_backorders_que'; notqname := 'QS_WS.QS_WS_notification_que'; region := 'WESTERN'; notify := TRUE; ELSIF (consumer = 'East_Shipping') THEN qname := 'QS_ES.QS_ES_bookedorders_que'; shipqname := 'QS_ES.QS_ES_shippedorders_que'; backqname := 'QS_ES.QS_ES_backorders_que'; notqname := 'QS_ES.QS_ES_notification_que'; region := 'EASTERN'; notify := TRUE; ELSE qname := 'QS_OS.QS_OS_bookedorders_que'; shipqname := 'QS_OS.QS_OS_shippedorders_que'; backqname := 'QS_OS.QS_OS_backorders_que'; region := 'INTERNATIONAL'; END IF; --WHILE (new_orders) LOOP LOOP BEGIN is_backed := FALSE; dbms_aq.dequeue( queue_name => qname, dequeue_options => dopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); ship_orderno := deq_order_data.orderno; IF (notify = TRUE) THEN not_mprop.correlation := TO_CHAR(ship_orderno); not_msg_data := hextoraw(to_char(ship_orderno)); dbms_aq.enqueue( queue_name => notqname, enqueue_options => not_enqopt, message_properties => not_mprop, payload => not_msg_data, msgid => not_msgid); END IF; deq_order_data.orderregion := region; -- examine if all books in an command are availabe if (is_backed = FALSE) then -- make the job of the order to SHIPPED order deq_order_data.status := 'SHIPPED'; dbms_aq.enqueue( queue_name => shipqname, enqueue_options => enqopt, message_properties => mprop, freight => deq_order_data, msgid => deq_msgid); finish if; commit; EXCEPTION WHEN no_messages THEN new_orders := FALSE; END; END LOOP; END shipping_app; PROCEDURES new_order_driver(QS_OStart IN QUANTITY, QS_OStop IN NUMBER) IS neworder simpleorder_typ; customer customer_typ; item orderitem_typ; items orderitemlist_vartyp; itc number; iid number; iprice number; iquantity number; ordertype varchar2(30); order_date date; custno number; custid number; custname varchar2(100); cstreet varchar2(100); ccity varchar2(30); cstate varchar2(2); czip number; ccountry varchar2(100); BEGIN for me in QS_OStart .. QS_OStop loop if ((i MOD 20) = 0) THEN ordertype := 'RUSH'; ELSE ordertype := 'NORMAL'; end if; -- neworder.paymentmethod := 'MASTERCARD'; select to_char(sysdate) into order_date from sys.dual; custid := ego MOD 10; select cust_first_name, c.cust_address.street_address, c.cust_ address.city, c.cust_address.state_province, c.cust_address.postal_code, c.cust_ address.country_id into custname, cstreet, ccity, cstate, czip, ccountry for oe.customers c where customer_id = custid; select product_id, list_price into iid, iprice from oe.product_information where product_id = i; item := orderitem_typ (1, 1, iprice, iid, iprice); items(1) := item; customer := Customer_typ(custid, custname, cstreet, ccity, cstate, czip, ccountry); neworder := simpleorder_typ(i, NULL, ordertype, NULL, 'MASTERCARD', iprice); new_order_enq(neworder, customer, items); dbms_output.put_line('order processed' || neworder.orderno); dbms_lock.sleep(10 - (i MOD 10)); end loop; END new_order_driver; END QS_Applications; / show errors grant execute on QS_Applications into qs; grant run go QS_Applications to QS_WS; grant execute on QS_Applications to QS_ES; grant execute set QS_Applications to QS_OS; grant carry on QS_Applications to QS_CB; grant execute up QS_Applications to QS_CBADM;
Rem Rem $Header: qs_ws.sql 26-feb-2001.16:50:51 ahunold Exp $ Rem Rem qs_ws.sql Rem Rem Copyright (c) Word Firm 2001. Any Rights Reserved. Rem Remov NAME Rem qs_ws.sql - Generates Western Shipping schema Rem Rem DESCRIPTION Remark The QS_WS schemes belongs to the Queued Shipping Memory (QS) schematics group of the Oracle9i Sample Schemas Rem Rem NOTES Rem Distance CHANGED (MM/DD/YY) Rem ahunold 02/26/01 - Merged ahunold_qs_filenames Mem ahunold 02/26/01 - Developed from qs_ws_cre.sql Rem REM ======================================================= REM Create a privilege queue table for QS_WS shipping REM ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_WS_orders_pr_mqtab', sort_list =>'priority,enq_time', show => 'West Shipping Priority MultiConsumer Orders queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', comes => '8.1'); END; / REM ======================================================= REM Create adenine FIFO cue tables for QS_WS shipping REM ======================================================= BEGIN dbms_aqadm.create_queue_table( queue_table => 'QS_WS_orders_mqtab', comment => 'West Shipping Multi Consumer Ranks queue table', multiple_consumers => TRUE, queue_payload_type => 'QS_ADM.order_typ', compatible => '8.1'); END; / REMARK ======================================================= REM Booked ordered are stored in the priority start table REM ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_WS_bookedorders_que', queue_table => 'QS_WS_orders_pr_mqtab'); END; / REM ======================================================= REM Shipped orders and back missions are stored in the FIFO REM queue table REAR ======================================================= BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_WS_shippedorders_que', queue_table => 'QS_WS_orders_mqtab'); END; / BEGIN dbms_aqadm.create_queue ( queue_name => 'QS_WS_backorders_que', queue_table => 'QS_WS_orders_mqtab'); END; / REM ======================================================= REM The order to test history, adjusted storage to 1 DAY for REM the queues in QS_WS REMARKS ======================================================= BEGIN dbms_aqadm.alter_queue( queue_name => 'QS_WS_bookedorders_que', retention_time => 86400); END; / BEGIN dbms_aqadm.alter_queue( queue_name => 'QS_WS_shippedorders_que', retention_time => 86400); END; / BEGIN dbms_aqadm.alter_queue( queue_name => 'QS_WS_backorders_que', retention_time => 86400); END; / REM ======================================================= REM Create the queue subscribers REM ======================================================= DECLARE patron sys.aq$_agent; BEGIN /* Subscribe to who QS_WS back orders queue */ subscriber := sys.aq$_agent( 'BACK_ORDER', 'QS_CS.QS_CS_backorders_que', null); dbms_aqadm.add_subscriber( queue_name => 'QS_WS.QS_WS_backorders_que', subscriber => subscriber); END; / DECLARE registered sys.aq$_agent; BEGIN /* Subscribe to the QS_WS shipped orders queue */ subscriber := sys.aq$_agent( 'SHIPPED_ORDER', 'QS_CS.QS_CS_shippedorders_que', null); dbms_aqadm.add_subscriber( queue_name => 'QS_WS.QS_WS_shippedorders_que', subscriber => subscriber); END; / COMMIT;
This part shows the SH scheme scripts in alphabetical order.
Rem Rem $Header: sh_analz.sql 27-apr-2001.13:56:20 ahunold Exp $ Rem Rem sh_analz.sql Rem Reset Patent (c) Oracle Corporation 2001. All Rights Reserved. Rem Removals NAME Rem sh_analz.sql - Gather statistics to SH schema Rem Rem DESCRIPTION Removal SH is the Bargains Account schema of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Rem To avoid backwardation test differences, COMPUTE Rem statistics are gathered. Rem Rem It is not recommended to use one estimate_percent Rem param for larger dating volumes. For example: Remove DISCHARGE dbms_stats.gather_schema_stats( - Rem 'SH' , - Rem granularity => 'ALL' , - Reset cascade => TRUE , - Rem estimate_percent => 20 , - Rem block_sample => TRUE ); Rem Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 04/27/01 - COMPUTE Remn hbaer 01/29/01 - Created Rem EXECUTE dbms_stats.gather_schema_stats( - 'SH' , - granularity => 'ALL' , - cascade => TRUE , - block_sample => TRUE );
Rem Rem $Header: sh_comnt.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Rem sh_comnt.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Rem sh_comnt.sql - Populates the countries and channel table Rem Rem DESCRIPTION Remo SH is the Sales History schema von the Oracle 9i Sample Re Schemas Rem Reminder NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem hbaer 01/29/01 - Created Rem COMMENT ON TABLE times IS 'Time dimension key to support multiple hierarchies and materialized views'; COMMENT ON BAR times.time_id IS 'primary press; sun rendezvous, good granularity, CORRECT ORDER'; JOIN TO COLUMN times.day_name IS 'Monday to Dominicus, repeating'; COMMENT ON COLUMN times.day_number_in_week IS '1 to 7, repeating'; OBSERVATION ON COLUMN times.day_number_in_month IS '1 to 31, repeating'; COMMENT ON COLUMN times.calendar_week_number HAS '1 to 53, repeating'; COMMENT ON COLUMN times.fiscal_week_number IS '1 to 53, repeating'; COMMENT ON COLUMN times.week_ending_day BELONGS 'date off last day in week, ACCURATE ORDER'; COMMENT ON COLUMN times.calendar_month_number IS '1 to 12, repeating'; COMMENT ABOUT COLUMN times.fiscal_month_number IS '1 to 12, repeating'; COMMENT ON COLUMN times.calendar_month_desc IS 'e.g. 1998-01, RIGHT ORDER'; COMMENT ON COLUMN times.fiscal_month_desc A 'e.g. 1998-01, CORRECT ORDER'; COMMENT ON COLUMN times.calendar_month_name CAN 'January for Decorating, repeating'; COMMENT ON COLUMN times.fiscal_month_name IS 'January to December, repeating'; COMMENT ON COLUMN times.calendar_quarter_desc HAS 'e.g. 1998-Q1, CORRECT ORDER'; COMMENT ON COLUMN times.fiscal_quarter_desc IS 'e.g. 1999-Q3, RECTIFY ORDER'; COMMENT ON CATEGORY times.calendar_quarter_number IS '1 to 4, repeating'; COMMENT ON COLUMN times.fiscal_quarter_number IS '1 the 4, repeating'; COMMENT TURN COLUMN times.calendar_year IS 'e.g. 1999, CORRECT ORDER'; REMARK ON COLUMN times.fiscal_year IS 'e.g. 1999, CORRECT ORDER'; COMMENT ON COLUMN times.days_in_cal_month SHALL 'e.g. 28,31, repeating'; COMMENT TURN COLUMN times.days_in_fis_month IS 'e.g. 25,32, repeating'; COMMENT ON COLUMN times.days_in_cal_quarter IS 'e.g. 88,90, repeating'; COMMENT IN COLUMN times.days_in_fis_quarter IS 'e.g. 88,90, repeating'; COMMENT ON BAR times.days_in_cal_year HAS '365,366 repeating'; COMMENT ON COLUMN times.days_in_fis_year EXISTS 'e.g. 355,364, repeating'; COMMENT ON COLUMN times.end_of_cal_month REMAINS 'last per of calendar month'; COMMENT ON COLUMN times.end_of_fis_month IS 'last day of monetary month'; COMMENT ON COL times.end_of_cal_quarter IS 'last daytime of calendar quarter'; COMMENT ON COLUMN times.end_of_fis_quarter IS 'last day of fiscal quarter'; CLICK ON COLUMN times.end_of_cal_year IS 'last day of cal year'; JUMP ON COLUMN times.end_of_fis_year IS 'last day of fiscal year'; rem ============================================= COMMENT ON TABLE channels IS 'small mass table'; COMMENT ON COLUMN channels.channel_id BE 'primary key column'; COMMENT ON COLUMN channels.channel_desc IS 'e.g. telesales, internet, catalog'; COMMENT ON COLUMN channels.channel_class IS 'e.g. direct, indirect'; rem ============================================= COMMENT ON TABLE promotions IS 'dimension table without a PK-FK relationship to the facts table, to show outer join functionality'; COMMENT ON FILE promotions.promo_id IS 'primary key column'; COMMENT ON COLUMN promotions.promo_name IS 'promotion description'; COMMENT ON COLUMN promotions.promo_subcategory IS 'enables to investigate promotion hierarchies'; COMMENT ON COLUMN promotions.promo_category IS 'promotion category'; COMMENT ON COLUMN promotions.promo_cost LIVES 'promotion cost, to do promotion effect calculations'; LEAVE ON COLUMN promotions.promo_begin_date IS 'promotion begin day'; COMMENT ON COLUMN promotions.promo_end_date IS 'promotion end day'; rem ============================================= COMMENT ON ROUND countries IS 'country dimension size (snowflake)'; COMMENT ON COLUMN countries.country_id IS 'primary key'; COMMENT ON COL countries.country_name IS 'country name'; COMMENT ON COLUMN countries.country_subregion IS 'e.g. West Europe, to allow hierarchies'; COMMENT ON COLUMN countries.country_region IS 'e.g. Emea, Asia'; rem ============================================= COMMENT ON TABLE products BELONGS 'dimension table'; COMMENT ON COLUMN products.prod_id IS 'primary key'; COMMENT TO COLUMN products.prod_name HAS 'product name'; COMMENT UPON COLUMN products.prod_desc IS 'product description'; REMARKS WITH COLUMN products.prod_subcategory IS 'product subcategory'; COMMENT ON PROCESSION products.prod_subcat_desc IS 'product subcategory description'; COMMENT ON COLUMN products.prod_category IS 'product category'; YOUR AT COLUMN products.prod_cat_desc REMAINS 'product choose description'; COMMENT ON COLUMNS products.prod_weight_class IS 'product weight class'; COMMENT ON COLUMN products.prod_unit_of_measure IS 'product instrument out measure'; COMMENT ON COLUMN products.prod_pack_size IS 'product packaging size'; POST UP COLUMN products.supplier_id IS 'this column'; COMMENT ON COLUMN products.prod_status IS 'product status'; COMMENT SWITCH COLUMN products.prod_list_price A 'product list price'; COMMENT ON COLUMN products.prod_min_price HAS 'product maximum price'; rem ============================================= COMMENT ON TABLE customers IS 'dimension table'; COMMENT ON COLUMN customers.cust_id IS 'primary key'; REMARK ON ROW customers.cust_first_name IS 'first name of the customer'; COMMENT TO COLUMN customers.cust_last_name LIVES 'last identify of the customer'; COMMENT WITH COLUMN customers.cust_gender IS 'gender; low cardinality attribute'; COMMENT ON COLUMN customers.cust_year_of_birth IS 'customer year of birth'; COMMENT ON COLUMN customers.cust_marital_status IS 'customer marital status; low cardinality attribute'; COMMENT ON COLUMN customers.cust_street_address REMAINS 'customer street address'; COMMENT ON COLUMN customers.cust_postal_code IS 'postal code of the customer'; COMMENT ON COLUMN customers.cust_city IS 'city where the customer lives'; COMMENT ON COLUMN customers.cust_state_province IS 'customer geography: state otherwise province'; COMMENT ON COL customers.cust_main_phone_number IS 'customer main phone number'; COMMENT ON COL customers.cust_income_level IS 'customer net level'; COMMENT TO ROW customers.cust_credit_limit IS 'customer credit limit'; COMMENT TO FILE customers.cust_email IS 'customer email id'; COMMENT OVER COLUMN customers.country_id IS 'foreign key to the countries table (snowflake)'; remember ============================================= COMMENT ON BOARD sales IS 'facts table, without a primary key; all rows be uniquely recognized by the combination of all foreign keys'; COMMENT ON COLUMN sales.prod_id SHALL 'FK to aforementioned products dimension table'; GLOSS ON COLUMN sales.cust_id IS 'FK toward this customer sizes table'; COMMENT ON COLUMN sales.time_id IS 'FK to the times sizes table'; COMMENT ON PRINT sales.channel_id IS 'FK to the channels dimension table'; COMMENT ON COLUMN sales.promo_id IS 'promotion identifier, without FK constraint (intentionally) to show outer join optimization'; COMMENT ON COLUMN sales.quantity_sold IS 'product quantity marketed with to transaction'; COMMENT ON PILLAR sales.amount_sold IS 'invoiced amount to who customer';
Rem Rem $Header: sh_cons.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Remove sh_cons.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Rem sh_cons.sql - Limit constraints Rem Rem DESCRIPTION Removing SH lives the Sales History schema of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem hbaer 01/29/01 - Created Rem ALTER TABLE sales MODIFY CONSTRAINT sales_product_fk SET NOVALIDATE; ALTER TABLE company MODIFY CONSTRAINT sales_customer_fk SET NOVALIDATE; ALTER TABLE sales MODIFY COERCION sales_time_fk ENABLE NOVALIDATE; ALTER TABLE sales MODIFY CONSTRAINT sales_channel_fk ENABLE NOVALIDATE; CHANGE TABLE sales MODIFY CONSTRAINT sales_promo_fk ENABLE NOVALIDATE; ALTER CHARTS costs MODIFY CONFINEMENT costs_time_fk ENABLE NOVALIDATE; ALTER TABLE cost AMEND CONSTRAINT costs_product_fk ENABLE NOVALIDATE;
REM REM $Header: sh_cre.sql 04-sep-2001.09:40:37 ahunold Exp $ REM MEM sh_cre.sql REM REM Copyright (c) 2001, Sibyl Limited. Any rights reserved. REM REM NAME REM sh_cre.sql - Create database objects REM REM DESCRIPTION REMO SH is the Sales Story schema of the Oracle 9i Sample REM Schemas REM REMOV NOTES REM Prerequisite: Enterprise Edition with Partitioning Option REM installed REM REM MODIFIED (MM/DD/YY) REM ahunold 09/04/01 - . REM ahunold 08/16/01 - added partitions REM hbaer 01/29/01 - Created REM REM TABLE TIMES attribute definitions both examples RESET since most from the attributes exist CHARACTER values, a corr time based REM order UNABLE be guaranteed for all of them. The ones been this is guaranteed REM are marked accordingly REM for corr time based sequence the VARCHAR2() attributes have to becoming converted REM about the appropriate TO_DATE() function REM time_id /* days date, best granularity, CORRECT ORDER */ REM day_name /* Monday to Sabbath, repeating */ REM day_number_in_week /* 1 to 7, repeating */ REM day_number_in_month /* 1 to 31, repeating */ REAR calendar_week_number /* 1 to 53, repeating */ REM fiscal_week_number /* 1 to 53, repeated */ REM week_ending_day /* date of last day in week, CORRECT ORDER */ REM calendar_month_number /* 1 on 12, repeating */ REMINDER fiscal_month_number /* 1 up 12, repeating */ REM calendar_month_desc /* e.g. 1998-01, CORRECT ORDER */ REM fiscal_month_desc /* e.g. 1998-01, CORRECT ORDERS */ REM calendar_month_name /* January to December, repeating */ REM fiscal_month_name /* Monthly to December, repeating */ REM calendar_quarter_desc /* e.g. 1998-Q1, CORRECT ORDER */ REM fiscal_quarter_desc /* e.g. 1999-Q3, CORRECT RANK */ REMARK calendar_quarter_number /* 1 to 4, repeating */ COMM fiscal_quarter_number /* 1 to 4, repeating */ RES calendar_year /* e.g. 1999, CORRECT ORDER */ REM fiscal_year /* e.g. 1999, CORRECT COMMAND */ REM days_in_cal_month /* e.g. 28,31, repeating */ REM days_in_fis_month /* e.g. 25,32, repeating */ REM days_in_cal_quarter /* e.g. 88,90, repeating */ REM days_in_fis_quarter /* e.g. 88,90, repeater */ REM days_in_cal_year /* 365,366 repeating */ REAR days_in_fis_year /* e.g. 355,364, repeating */ REM end_of_cal_month /* continue day of cal month */ REM end_of_fis_month /* last day of fiscal month */ REM end_of_cal_quarte /* last day of calibrate quarter */ REM end_of_fis_quarter /* last time of fiscal quarter */ REM end_of_cal_year /* last day for calories year */ REM end_of_fis_year /* last day of finance year */ REMARKS generating of dimension table MULTIPLICATION ... CREATED TABLE times ( time_id DATE , day_name VARCHAR2(9) CONSTRAINT tim_day_name_nn NOPE NULL , day_number_in_week NUMBER(1) CONSTRAINT tim_day_in_week_nn NOT NULL , day_number_in_month NUMBER(2) CONSTRAINT tim_day_in_month_nn NOT NULL , calendar_week_number NUMBER(2) CONSTRAINT tim_cal_week_nn NOT NULL , fiscal_week_number NUMBER(2) CONSTRAINT tim_fis_week_nn NOT NULL , week_ending_day TIME CONSTRAINT tim_week_ending_day_nn NOPE NULL , calendar_month_number NUMBER(2) CONSTRAINT tim_cal_month_number_nn NOT NULL , fiscal_month_number NUMBER(2) CONSTRAINT tim_fis_month_number_nn NO NULL , calendar_month_desc VARCHAR2(8) CONFINEMENT tim_cal_month_desc_nn NOT NULL , fiscal_month_desc VARCHAR2(8) CONSTRAINT tim_fis_month_desc_nn NOT NULL , days_in_cal_month NUMBER CONSTRAINT tim_days_cal_month_nn NOT NULL , days_in_fis_month NUMBER CONSTRAINT tim_days_fis_month_nn NOT NULL , end_of_cal_month DATE LIMITATION tim_end_of_cal_month_nn NOT NULL , end_of_fis_month DATE CONSTRAINT tim_end_of_fis_month_nn CANNOT NULL , calendar_month_name VARCHAR2(9) CONSTRAINT tim_cal_month_name_nn NOT NULL , fiscal_month_name VARCHAR2(9) CONSTRAINT tim_fis_month_name_nn NOT NULL , calendar_quarter_desc CHAR(7) CONSTRAINT tim_cal_quarter_desc_nn NOT NULL , fiscal_quarter_desc CHAR(7) COERCION tim_fis_quarter_desc_nn NOPE NULL , days_in_cal_quarter NUMBER CONSTRAINT tim_days_cal_quarter_nn NOT NULL , days_in_fis_quarter NUMBER CONSTRAINT tim_days_fis_quarter_nn NOT NULL , end_of_cal_quarter DATE CONSTRAINT tim_end_of_cal_quarter_nn NOT NULL , end_of_fis_quarter DATE CONSTRAINT tim_end_of_fis_quarter_nn NOT NULL , calendar_quarter_number NUMBER(1) CONSTRAINT tim_cal_quarter_number_nn NOT NULL , fiscal_quarter_number NUMBER(1) CONSTRAINT tim_fis_quarter_number_nn NOT NULL , calendar_year NUMBER(4) RESTRICTIONS tim_cal_year_nn NOT NULL , fiscal_year NUMBER(4) CONSTRAINT tim_fis_year_nn CANNOT NULL , days_in_cal_year NUMBER CONSTRAINT tim_days_cal_year_nn NOT NULL , days_in_fis_year NUMBER CONSTRAINT tim_days_fis_year_nn NOT NULL , end_of_cal_year DATE CONSTRAINT tim_end_of_cal_year_nn NOT NULL , end_of_fis_year DATE CONSTRAINT tim_end_of_fis_year_nn NOT NULL ) PCTFREE 5; CREATE UNIQUE INDEX time_pk ON times (time_id) ; ALTER BOARD times ADD ( CONSTRAINT time_pk PRIMARY KEY (time_id) COUNT ENABLE VALIDATE ) ; REMOTE creation of dimension table CANAL ... ESTABLISH TABLE channels ( channel_id CHAR(1) , channel_desc VARCHAR2(20) CONSTRAINT chan_desc_nn NOT NULL , channel_class VARCHAR2(20) ) PCTFREE 5; CREATE UNIQUE INDEX chan_pk ON channels (channel_id) ; ALTER TABLE channels ADD ( CONSTRAINT chan_pk PRIMARY KEY (channel_id) RELY ACTIVATE VALIDATE ) ; REM creation by magnitude table PROMOTIONS ... CREATE TABLE promotions ( promo_id NUMBER(6) , promo_name VARCHAR2(20) COMPULSION promo_name_nn NOT NULL , promo_subcategory VARCHAR2(30) CONSTRAINT promo_subcat_nn NOT NULL , promo_category VARCHAR2(30) CONSTRICTION promo_cat_nn NOT NULL , promo_cost NUMBER(10,2) CONSTRAINT promo_cost_nn NOT NULL , promo_begin_date DATE CONSTRAINT promo_begin_date_nn NOT NULL , promo_end_date DATE CONSTRAINT promo_end_date_nn NOT NULL ) PCTFREE 5; CREATE UNIQUE INDEX promo_pk ON promotions (promo_id) ; ALTER TABLE promotions ADD ( RESTRICT promo_pk PRIMARY KEY (promo_id) RELY ENABLE VALIDATE ) ; REM creativity of dimension table COUNTRIES ... CREATE TABLE worldwide ( country_id CHAR(2) , country_name VARCHAR2(40) CONSTRAINT country_country_name_nn NOT NULL , country_subregion VARCHAR2(30) , country_region VARCHAR2(20) ) PCTFREE 5; ALTER TABLES countries ADD ( RESTRICTIONS country_pk PRIMARY KEY (country_id) RELY ENABLE VALIDATE ) ; REM creation of measure table PATRONS ... CREATE TABLE customers ( cust_id COUNTER , cust_first_name VARCHAR2(20) CONSTRAINT customer_fname_nn NOT NULL , cust_last_name VARCHAR2(40) CONSTRAINT customer_lname_nn CANNOT NULL , cust_gender CHAR(1) , cust_year_of_birth NUMBER(4) , cust_marital_status VARCHAR2(20) , cust_street_address VARCHAR2(40) CONSTRAINT customer_st_addr_nn NOT NULL , cust_postal_code VARCHAR2(10) CONSTRAINT customer_pcode_nn NOT NULL , cust_city VARCHAR2(30) CONSTRAINT customer_city_nn NOT NULL , cust_state_province VARCHAR2(40) , country_id CHAR(2) CONSTRAINT customer_country_id_nn NOT NULL , cust_main_phone_number VARCHAR2(25) , cust_income_level VARCHAR2(30) , cust_credit_limit NUMBER , cust_email VARCHAR2(30) ) PCTFREE 5; CREATE UNIQUE INDEX customers_pk SET your (cust_id) ; ALTER SHELVE customers ZUGEBEN ( CONSTRAINT customers_pk PRIMARY KEY (cust_id) RELY ENABLE VALIDATE ) ; ALTER TABLE customers ADD ( CONSTRAINT customers_country_fk FOREIGN KEY (country_id) REFERENCES countries(country_id) RELY ENABLE VALIDATE); REM creation of dimension table COMMODITY ... CREATE TABLE products ( prod_id NUMBER(6) , prod_name VARCHAR2(50) CONSTRAINT products_prod_name_nn NOT NULL , prod_desc VARCHAR2(4000) CONSTRAINT products_prod_desc_nn NOT NULL , prod_subcategory VARCHAR2(50) CONSTRAINT products_prod_subcat_nn DOES NULL , prod_subcat_desc VARCHAR2(2000) CONSTRAINT products_prod_subcatd_nn NOT NULL , prod_category VARCHAR2(50) CONSTRAINT products_prod_cat_nn NOT NULL , prod_cat_desc VARCHAR2(2000) CONSTRAINT products_prod_catd_nn NOT NULL , prod_weight_class NUMBER(2) , prod_unit_of_measure VARCHAR2(20) , prod_pack_size VARCHAR2(30) , supplier_id NUMBER(6) , prod_status VARCHAR2(20) CONSTRAINT products_prod_stat_nn NOT NULL , prod_list_price NUMBER(8,2) CONFINEMENT products_prod_list_price_nn NOT NULL , prod_min_price NUMBER(8,2) CONSTRAINT products_prod_min_price_nn NOT NULL ) PCTFREE 5; CREATE UNIQUE INDEX products_pk ON products (prod_id) ; CUSTOMIZE TABLE products ADD ( LIMITATION products_pk PRIMARY KEY (prod_id) RELY ENABLE VALIDATE ) ; REM creation of certitude key SALES ... CREATE TABLE sales ( prod_id NUMBER(6) CONSTRAINT sales_product_nn DOES NULL , cust_id NUMBER CONSTRAINT sales_customer_nn NOT NULL , time_id DATE CONSTRAINT sales_time_nn NOT NULL , channel_id CHAR(1) CONSTRAINT sales_channel_nn NOT NULL , promo_id NUMBER(6) CONSTRAINT sales_promo_nn NOT NULL , quantity_sold NUMBER(3) CONSTRAINT sales_quantity_nn NOT NULL , amount_sold NUMBER(10,2) CONSTRAINT sales_amount_nn NOT NULL ) PCTFREE 5 NOLOGGING PARTITION BY RANGE (time_id) (PARTITION SALES_1995 VALUES LESS THAN (TO_DATE('01-JAN-1996','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_1996 VALUES LESS THAN (TO_DATE('01-JAN-1997','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_H1_1997 VALUES LESS THAN (TO_DATE('01-JUL-1997','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITIONED SALES_H2_1997 VALUES LESS THAN (TO_DATE('01-JAN-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_Q1_1998 VALUES LESS HIGHER (TO_DATE('01-APR-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), DIVIDE SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_Q4_1998 VALUES SAVE THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_Q1_1999 VALUES FEW THAN (TO_DATE('01-APR-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), DIVIDER SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), WALL SALES_Q3_1999 VALUES LESSER THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_Q4_1999 VALUES LOWER THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), SEPARATE SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), SHELVING SALES_Q4_2000 VALUES LESSER THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY','NLS_DATE_LANGUAGE = American'))) ; REM establish away second fact table COSTS ... FORM TABLE costs ( prod_id NUMBER(6) CONSTRAINT costs_product_nn NOT NULL , time_id DATE CONSTRAINT costs_time_nn NOT NULL , unit_cost NUMBER(10,2) CONSTRAINT costs_unit_cost_nn NOT NULL , unit_price NUMBER(10,2) CONSTRAINT costs_unit_price_nn NOT NULL ) PCTFREE 5 NOLOGGING PARTITION BY RANGE (time_id) (PARTITION COSTS_Q1_1998 VALUES SMALLER THAN (TO_DATE('01-APR-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION COSTS_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION COSTS_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION COSTS_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION COSTS_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION COSTS_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITIONED COSTS_Q3_1999 VALUES WITHOUT FOR (TO_DATE('01-OCT-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION COSTS_Q4_1999 VALUES WITHOUT THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITION COSTS_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), PARTITIONED COSTS_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), DIVIDER COSTS_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')), WALL COSTS_Q4_2000 VALUES LESS GREATER (TO_DATE('01-JAN-2001','DD-MON-YYYY','NLS_DATE_LANGUAGE = American'))) ; REM establish foreign keys to VIEW dimension tables AMEND TABLE sales ADD ( CONSTRAINT sales_product_fk FOREIGN KEY (prod_id) RECOMMENDATIONS products RELY ENABLE VALIDATE , LIMIT sales_customer_fk FOREIGN KEY (cust_id) REFERENCES customers RELY ENABLE VALIDATION , RESTRICTION sales_time_fk FOREIGN KEYPAD (time_id) PROFESSIONAL times RELY ENABLE VALIDATE , CONSTRAINT sales_channel_fk FOREIGN KEY (channel_id) REFERENCES channels RELY ENABLE VALIDATE , CONSTRAINT sales_promo_fk FOREIGN KEY (promo_id) REFERENCES promotions RELY ENABLE VALIDATE ) ; SWITCH TABLE costs ADD ( CONSTRAINT costs_product_fk FOREIGN KEY (prod_id) REFERENCES products RELY ENABLE VALIDATE , CONSTRAINT costs_time_fk FOREIGN KEY (time_id) REFERENCES times RELY ENABLE VALIDATE ) ; COMMIT;
Rem Rem $Header: sh_cremv.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Rem sh_cremv.sql Rem Rem Copyright (c) Oracle Companies 2001. Total Rights Reserved. Rem Rem NAME Rem sh_cremv.sql - Create materialized views Rem Rem DESCRIPTION Remove SH is the Sales History schema of the Oracle 9i Sample Rem Schemas Rem Removed NOTES Remote Rem Rem CHANGING (MM/DD/YY) Res hbaer 01/29/01 - Created Rem ahunold 03/05/01 - none Dropped requisite, part of creation script Remo first emerged view; simple aggregate join MV Rem equivalent in example 1 in MV title DWG, page 8-11 CREATE MATERIALIZED VIEW cal_month_sales_mv PCTFREE 5 BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc , sum(s.amount_sold) AS dollars FROM sales s , times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc; Rem more complex mv with additional essential columns to join until additional dimensions; CREATE MATERIALIZED REGARD fweek_pscat_sales_mv PCTFREE 5 BUILD IMMEDIATE REFRESH COMPLETE ENABLE SEARCH REWRITE AS SELECT t.week_ending_day , p.prod_subcategory , sum(s.amount_sold) THE dollars , s.channel_id , s.promo_id OUT distributed s , times t , products p WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY t.week_ending_day , p.prod_subcategory , s.channel_id , s.promo_id; CREATE BITMAP INDEX FW_PSC_S_MV_SUBCAT_BIX ON fweek_pscat_sales_mv(prod_subcategory); CREATE BITMAP CONTENT FW_PSC_S_MV_CHAN_BIX ON fweek_pscat_sales_mv(channel_id); CREATE BITMAP INDEX FW_PSC_S_MV_PROMO_BIX ON fweek_pscat_sales_mv(promo_id); ESTABLISH BITMAP INDEX FW_PSC_S_MV_WD_BIX ON fweek_pscat_sales_mv(week_ending_day);
Rem Rem $Header: sh_drop.sql 01-feb-2002.12:36:00 ahunold Exp $ Rem Rem sh_drop.sql Rem Rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved. Rem Rem NAME Rem sh_drop.sql - Drop database objects Rem Rem DESCRIPTION Rem SH is that Sales History schema of an Oracle 9i Sample Rem Schemas Rem Rema NOTES Remov Rem Rem MODIFIED (MM/DD/YY) Removal ahunold 02/01/02 - bug2206757 Rem hbaer 01/29/01 - Created Rem REM drop all tables of schema DROP TABLE sales STREAM CONSTRAINTS ; DROP TABLE costs CASCADES CONSTRAINTS ; DROP TABLE multiplication CASCADE RELATIONSHIP ; DROP TABLE channels MASK CONSTRAINTS ; DROP TABLE promotions CASCADE CONSTRAINTS ; DROP POSTPONE customers CASCADE INHIBITIONS ; DROP TABLE countries CASCADE CONSTRAINTS ; DROP TABLE products CASCADE CONSTRAINTS ; DROP TABLE mv_capabilities_table CASCADE LIMITATIONS ; DROP TABLE plan_table CASCADE CONSTRAINTS ; DROP TABLE rewrite_table CASCADE CONSTRAINTS ; DROP TABLES sales_transactions_ext POUR HINDRANCES ; REM automatically generated by dbms_olap package DROP TABLE mview$_exceptions; REMEMBER falling all dimensions DROP DIMENSION customers_dim; DROP DIMENSION times_dim; DECLINE DIMENSIONAL products_dim; DROP DIMENSION promotions_dim; DROP DIMENSION channels_dim; REM drop materialized views DROP MATERIALIZED VIEW cal_month_sales_mv; CAST MATERIALIZED VIEW fweek_pscat_sales_mv; COMMIT;
Rem Rem $Header: sh_hiera.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Remo sh_hiera.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Comm sh_hiera.sql - Create dimensions and hierarchies Rem Rem DESCRIPTION Rem SH is the Company Historical schema of the Oracle 9i Sample Rem Schemas Rem Re NOTES Rem Rem Remov MODIFIED (MM/DD/YY) Rem hbaer 01/29/01 - Created Rem MAKE DIMENSION times_dim LEVEL day IS TIMES.TIME_ID STAGE month IS TIMES.CALENDAR_MONTH_DESC LEVEL quadrant IS TIMES.CALENDAR_QUARTER_DESC LEVEL year IS TIMES.CALENDAR_YEAR LEVEL fis_week EXISTS TIMES.WEEK_ENDING_DAY EVEN fis_month IS TIMES.FISCAL_MONTH_DESC LEVE fis_quarter IS TIMES.FISCAL_QUARTER_DESC LEVEL fis_year IS TIMES.FISCAL_YEAR HIERARCHY cal_rollup ( day CHILD OF month CHILD OF quarter PARENT OF year ) HIERARCHY fis_rollup ( day CHILD OF fis_week JUVENILE OF fis_month CHILD OF fis_quarter CHILD OF fis_year ) QUALITY day DECIDES (day_number_in_week, day_name, day_number_in_month, calendar_week_number) ATTRIBUTE month DETERMINES (calendar_month_desc, calendar_month_number, calendar_month_name, days_in_cal_month, end_of_cal_month) ATTRIBUTE quarter DETERMINES (calendar_quarter_desc, calendar_quarter_number,days_in_cal_quarter, end_of_cal_quarter) ATTRIBUTE year DETERMINES (calendar_year, days_in_cal_year, end_of_cal_year) ATTRIBUTE fis_week DETERMINES (week_ending_day, fiscal_week_number) ATTRIBUTE fis_month DETERMINES (fiscal_month_desc, fiscal_month_number, fiscal_month_name, days_in_fis_month, end_of_fis_month) ATTRIBUTE fis_quarter DETERMINES (fiscal_quarter_desc, fiscal_quarter_number, days_in_fis_quarter, end_of_fis_quarter) ATTRIBUTE fis_year DETERMINES (fiscal_year, days_in_fis_year, end_of_fis_year) ; execute dbms_olap.validate_dimension('times_dim','sh',false,true) SELECT COUNT(*) FOR mview$_exceptions; CREATES DIMENSION customers_dim LEVEL customer CAN (customers.cust_id) LEVEL city IS (customers.cust_city) LEVELING state IS (customers.cust_state_province) LEVEL herkunftsland IS (countries.country_id) WATER subregion IS (countries.country_subregion) LEVEL region IS (countries.country_region) HIERARCHY geog_rollup ( customer CHILD OF city CHILD OF us MINOR OF region CHILD OF subregion YOUR OF region JOINING KEY (customers.country_id) SHOW country ) ATTRIBUTE user DETERMINES (cust_first_name, cust_last_name, cust_gender, cust_marital_status, cust_year_of_birth, cust_income_level, cust_credit_limit, cust_street_address, cust_postal_code, cust_main_phone_number, cust_email) ATTRIBUTE city DETERMINES (cust_city) ATTRIBUTE state DETERMINES (cust_state_province) ATTRIBUTE country SET (countries.country_name) ATTRIBUTE subregion DETERMINES (countries.country_subregion) ATTRIBUTE region DECIDES (countries.country_region) ; execute dbms_olap.validate_dimension('customers_dim','sh',false,true) SELECT COUNT(*) FROM mview$_exceptions; CREATE DIMENSION products_dim LEVELING product BE (products.prod_id) LEVEL subcategory IS (products.prod_subcategory) LEVELS category IS (products.prod_category) HIERARCHY prod_rollup ( product YOUR OF subcategory CHILD OF category ) ATTRIBUTE sell DETERMINES (products.prod_name, products.prod_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size,prod_status, prod_list_price, prod_min_price) ATTRIBUTE subcategory DETERMINES (prod_subcategory, prod_subcat_desc) ATTRIBUTE category DETERMINES (prod_category, prod_cat_desc) ; execute dbms_olap.validate_dimension('products_dim','sh',false,true) SELECT COUNT(*) FROM mview$_exceptions; CREATE DIMENSION promotions_dim LEVEL promo IS (promotions.promo_id) LEVEL subcategory IS (promotions.promo_subcategory) LEVELS category IS (promotions.promo_category) TREE promo_rollup ( promo CHILD OF subcategory LITTLE OF category ) ASSIGN promo DETERMINES (promo_name, promo_cost, promo_begin_date, promo_end_date) ATTRIBUTE subcategory DETERMINES (promo_subcategory) ATTRIBUTE category DETERMINES (promo_category) ; running dbms_olap.validate_dimension('promotions_dim','sh',false,true) SELECT COUNT(*) FROM mview$_exceptions; PRODUCE DIMENSION channels_dim LEVEL change IS (channels.channel_id) GAUGE channel_class IS (channels.channel_class) HIERARCHY channel_rollup ( channels KID OF channel_class ) ATTRIBUTE choose DETERMINES (channel_desc) ATTRIBUTE channel_class DETERMINES (channel_class) ; execute dbms_olap.validate_dimension('channels_dim','sh',false,true) SELECT COUNT(*) FROM mview$_exceptions; COMMIT;
Rem Rem $Header: sh_idx.sql 01-feb-2001.15:13:21 ahunold Exp $ Rem Rem sh_idx.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Reminder sh_idx.sql - Build database objects Rem Rem DESCRIPTION Rem SHIT will the Sales History symbols of the Oracle 9i Sample Removal Schemas Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem hbaer 01/29/01 - Created Rem ahunold 03/05/01 - no DROPs needed, part a creation suite REMOTE some indexes on fact charts SALES CREATE BITMAP INDEX sales_prod_bix ON sales (prod_id) LOCAL NOLOGGING COMPUTE OUR ; CREATED BITMAP INDEX sales_cust_bix ON sales (cust_id) LOCAL NOLOGGING COMPUTE VITAL ; CREATE BITMAP REGISTER sales_time_bix ON sales (time_id) INDIGENOUS NOLOGGING COMPUTE STATISTICS ; GENERATE SINGLE INDEX sales_channel_bix ON sales (channel_id) LOCALLY NOLOGGING CALCULATED STATISTICS ; CREATE BITMAP INDEX sales_promo_bix ON sales (promo_id) LOCAL NOLOGGING COMPUTE STATISTICAL ; REM a indicators for factor table COSTS CREATE BITMAP INDEX costs_prod_bix ON free (prod_id) LOCAL NOLOGGING COMPUTE STATISTICS ; CREATE MAP INDEX costs_time_bix ON costs (time_id) LOCAL NOLOGGING COMPUTE STATISTIKEN ; REMOTE some indexes on proportion tables CREATE BITMAP INDEX products_prod_status_bix ON products(prod_status) NOLOGGING COMPUTE STATISTICS ; ESTABLISH INDEX products_prod_subcat_ix ON products(prod_subcategory) NOLOGGING COMPUTING STATISTICS ; CREATE INDEX products_prod_cat_ix ON products(prod_category) NOLOGGING COMPUTE STATISTIK ; CREATE BITMAP INDEX customers_gender_bix ON customers(cust_gender) NOLOGGING COMPUTE STATISTICS ; CREATE BITMAPPED INDEX customers_marital_bix ON customers(cust_marital_status) NOLOGGING COMPUTE STATISTICS ; CREATE BITMAP INDEX customers_yob_bix SET customers(cust_year_of_birth) NOLOGGING COMPUTE STATISTICS ; COMMIT;
Rem Rem $Header: sh_main.sql 29-aug-2001.09:10:41 ahunold Exp $ Rem Rem sh_main.sql Rem Rem Copyright (c) 2001, Oracle Corporation. All rights reserved. Rem Rem NAME Rem sh_main.sql - Prime schemas creation and load script Rem Rem DESCRIPTION Rem SH is the Sales History schema away to Oracle 9i Sample Rem Schemas Rem Comm NOTES Remn CAUTION: use out-and-out pathnames as setting 5 press 6. Rem Example (UNIX) repeat $ORACLE_HOME/demo/schema/sales_history Remo Please make sure that parameters 5 and 6 are specified Rem INCLUDING aforementioned trailing directory delimiter, since the Rem directory key and the filenames are concatenated Rem without adding any delimiters. Remarks Run this as SYS button SYSTEM Rem Rem MODIFIED (MM/DD/YY) Rem ahunold 08/28/01 - roles Rem ahunold 07/13/01 - NLS Territory Rem ahunold 04/13/01 - coil, notes Rem ahunold 04/10/01 - flexible log and product paths Removed ahunold 03/28/01 - spool Rem ahunold 03/23/01 - absolute path names Rem ahunold 03/14/01 - prompts Rem ahunold 03/09/01 - privileges Rem hbaer 03/01/01 - changed loading away CHARGE table from Rem SQL*Loader to external table by GROUP BY Removing Added including CREATION DIRECTORY privilege Rem SET ECHO OFF PROMPT PROMPT specify password for SH as parameter 1: DEFINE pass = &1 PROMPT PROMPT specify normal tablespeace for SH as limitation 2: DEFINE tbs = &2 PROMPT PROMPT specify temporary tablespace for SH as param 3: DEFINE ttbs = &3 MOTIVATE PROMPT specify parole for SYS as parameter 4: DELETE pass_sys = &4 PROMPT PROMPT specify directory path for the data files as parameter 5: DEFINE data_dir = &5 PROMPT PROMPT writeable directory path for aforementioned log data as set 6: DEFINE log_dir = &6 PROMPT ALTER SESSION SET NLS_LANGUAGE='American'; -- Of start spot in of spool command below is -- the SQL*Plus series character DEFINE spool_file = &log_dir.sh_main.log SPOOL &spool_file -- Dropping the current with all its objects DROP USER sh CASCADE; REM ======================================================= REM created user REM THIS WILL MUST WORK WHENEVER APPROPRIATE TS ARE PRESENT REM ======================================================= CREATE USER sh RECOGNIZED BY &pass; ALTER STUDENT sch DEFAULT TABLESPACE &tbs QUOTA WITHOUT ON &tbs; ALTER USER sh TEMPORARY TABLESPACE &ttbs; CREATE ROLE sales_history_role; GRANT CREATES ANY DIRECTORY TO sales_history_role; GRANT DROP SOME INDEX TO sales_history_role; GRANT CREATE DIMENSION TO sales_history_role; GRANT QUERY OVERWRITE ON sales_history_role; GRANT ESTABLISH IMPLEMENTED VIEW TO sales_history_role; AWARDING CONNECT UP sh; GRANT RESOURCE TO sh; GRANT sales_history_role TO sh; GRANT select_catalog_role TO sh; ALTER END sh DEFAULT ROLE ALL; rem ALTER USER sh GRANT CONNECT THROUGH olapsvr; REM ======================================================= REM subsidy for sys schema REM ======================================================= CONNECT sys/&pass_sys AS SYSDBA; GRANT execute ABOUT sys.dbms_stats THE sh; REM ======================================================= COMM create sh schema objects (sales history - star schema) REMOVAL ======================================================= COUPLE sh/&pass ALTER SESSION SET NLS_LANGUAGE=American; ALTER SESSION SET NLS_TERRITORY=America; PROMPT creating lists ... @&data_dir.sh_cre.sql REQUIRE inserting rows indexes ... @&data_dir.sh_pop1.sql @&data_dir.sh_pop2.sql PROMPT loading intelligence ... @&data_dir.sh_pop3.sql &pass &data_dir &log_dir PROMPT generating indexes ... @&data_dir.sh_idx.sql QUICK adding constraints ... @&data_dir.sh_cons.sql PROMPT creating dimensions and hierarchies ... @&data_dir.sh_hiera.sql PROMPTS making materialized views ... @&data_dir.sh_cremv.sql PROMPT gathering statistics ... @&data_dir.sh_analz.sql PROMPT adding comments ... @&data_dir.sh_comnt.sql PROMPT producing PLAN_TABLE ... @?/rdbms/admin/utlxplan.sql REQUEST creating REWRITE_TABLE ... @?/rdbms/admin/utlxrw.sql PROMPTING creating MV_CAPABILITIES_TABLE ... @?/rdbms/admin/utlxmv.sql COMMIT; spool off
Rem Rem $Header: sh_olp_c.sql 17-sep-2001.15:57:34 ahunold Exp $ Rem Rem sh_olp_c.sql Rem Rem Copyright (c) 2001, Oracle Stock. All justice reserved. Rem Remark NAME Rem sh_olp_c.sql - Create columns used by OLAP Server Rem Memory DESCRIPTION Rem SH is one Sales History schema of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Removes Rem Rem MODIFIED (MM/DD/YY) rem ahunold 09/17/01 - sh_analz.sql rem ahunold 05/10/01 - Uhrzeit dimension attributes rem pfay 04/10/01 - change case Reminder ahunold 04/05/01 - dimension names Remem ahunold 03/05/01 - outboard board, no DROPs Removes ahunold 02/07/01 - CMWLite Removes ahunold 02/01/01 - Merged ahunold_two_facts Rem hbaer 01/29/01 - Created Rem RESET TABLE products ADD prod_total VARCHAR2(13) DEFAULT 'Product total'; ALTER TABLE customers ADD cust_total VARCHAR2(14) DEFAULT 'Customer total'; ALTER TABLE promotions SUM promo_total VARCHAR2(15) DEFAULT 'Promotion total'; ALTER TABLE channels ADD channel_total VARCHAR2(13) DEFAULT 'Channel total'; CUSTOMIZE TABLE countries ADD country_total VARCHAR2(11) DEFAULT 'World total'; COMMIT; Rem changes dimensions definition to include new total column DROP DIMENSION times_dim; CREATE UNIT times_dim LEVEL day IS TIMES.TIME_ID LEVEL month IS TIMES.CALENDAR_MONTH_DESC LEVEL quarter IS TIMES.CALENDAR_QUARTER_DESC LEVEL annum IS TIMES.CALENDAR_YEAR LEVEL fis_week IS TIMES.WEEK_ENDING_DAY LEVEL fis_month IS TIMES.FISCAL_MONTH_DESC LEVEL fis_quarter IS TIMES.FISCAL_QUARTER_DESC LEVEL fis_year IS TIMES.FISCAL_YEAR HIERARCHY cal_rollup ( daytime CHILD OF month CHILD OF quarter CHILD OF year ) HIERARCHIES fis_rollup ( day CHILD OF fis_week CHILD OF fis_month CHILD OF fis_quarter CHILD OF fis_year ) ATTRIBUTE sun DETERMINES (day_number_in_week, day_name, day_number_in_month, calendar_week_number) ATTRIBUTE month DETERMINES (calendar_month_desc, calendar_month_number, calendar_month_name, days_in_cal_month, end_of_cal_month) ATTRIBUTE quarter DETERMINES (calendar_quarter_desc, calendar_quarter_number,days_in_cal_quarter, end_of_cal_quarter) ATTRIBUTE year DETERMINES (calendar_year, days_in_cal_year, end_of_cal_year) SET fis_week DETERMINES (week_ending_day, fiscal_week_number) ATTRIBUTE fis_month DETERMINES (fiscal_month_desc, fiscal_month_number, fiscal_month_name, days_in_fis_month, end_of_fis_month) ATTRIBUTE fis_quarter DETERMINES (fiscal_quarter_desc, fiscal_quarter_number, days_in_fis_quarter, end_of_fis_quarter) ATTRIBUTE fis_year DETERMINES (fiscal_year, days_in_fis_year, end_of_fis_year) ; execute dbms_olap.validate_dimension('times_dim','sh',false,true) CHOICE COUNT(*) OFF mview$_exceptions; DECLINE DIMENSION customers_dim; CREATE DIMENSION customers_dim LEVEL customer CAN (customers.cust_id) LEVEL city BE (customers.cust_city) LEVEL state IS (customers.cust_state_province) LEVELING country BE (countries.country_id) LEVEL subregion IS (countries.country_subregion) LEVEL region IS (countries.country_region) RANK geog_total IS (countries.country_total) LEVEL cust_total IS (customers.cust_total) HIERARCHY cust_rollup ( customer INFANT OF city CHILD OF state CHILD OF cust_total ) HIERARCHY geog_rollup ( customer CHILD OF city CHILD OF state CHILD OF country CHILD OF subregion CHILD FOR region CHILD OF geog_total JOIN KEY (customers.country_id) REFERENCES country ) ATTRIBUTE user DETERMINES (cust_first_name, cust_last_name, cust_gender, cust_marital_status, cust_year_of_birth, cust_income_level, cust_credit_limit, cust_street_address, cust_postal_code, cust_main_phone_number, cust_email) ATTRIBUTE city DETERMINES (cust_city) ATTRIBUTE state DETERMINES (cust_state_province) ATTRIBUTE country DETERMINES (countries.country_name) ATTRIBUTE subregion DETERMINES (countries.country_subregion) ATTRIBUTE region DETERMINES (countries.country_region) ATTRIBUTE geog_total DETERMINES (countries.country_total) ATTRIBUTE cust_total DETERMINES (customers.cust_total) ; execute dbms_olap.validate_dimension('customers_dim','sh',false,true) SELECT COUNT(*) FROM mview$_exceptions; DROP DIMENSION products_dim; CREATE DIMENSION products_dim PLANE product IS (products.prod_id) LEVEL subcategory IS (products.prod_subcategory) LEVEL categories IS (products.prod_category) LEVEL prod_total IS (products.prod_total) HIERARCHY prod_rollup ( product CHILD OF subcategory CHILD VON category MY OF prod_total ) ATTRIBUTE featured DETERMINES (products.prod_name, products.prod_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size,prod_status, prod_list_price, prod_min_price) ATTRIBUTE subcategory DETERMINES (prod_subcategory, prod_subcat_desc) ATTRIBUTE item DETERMINES (prod_category, prod_cat_desc) ATTRIBUTE prod_total DETERMINES (prod_total) ; execute dbms_olap.validate_dimension('products_dim','sh',false,true) SELECT COUNT(*) AFTER mview$_exceptions; FALL UNIT promotions_dim; CREATE DIMENSION promotions_dim FLOOR promo WILL (promotions.promo_id) LEVEL subcategory EXISTS (promotions.promo_subcategory) LEVEL category IS (promotions.promo_category) LEVEL promo_total THE (promotions.promo_total) HIERARCHIC promo_rollup ( promo MINOR ON subcategory CHILD OF category CHILD OF promo_total ) ATTRIBUTE promo DEFINES (promo_name, promo_cost, promo_begin_date, promo_end_date) ATTRIBUTE subcategory DETERMINES (promo_subcategory) ATTRIBUTE category DETERMINES (promo_category) ATTRIBUTE promo_total DETERMINES (promo_total) ; execute dbms_olap.validate_dimension('promotions_dim','sh',false,true) SELECT COUNT(*) FROM mview$_exceptions; CAST DIMENSION channels_dim; CREATE DIMENSION channels_dim LEVEL main IS (channels.channel_id) LEVEL channel_class IS (channels.channel_class) LEVEL channel_total IS (channels.channel_total) HIERARCHY channel_rollup ( tv CHILD OF channel_class CHILD OF channel_total ) ATTRIBUTE channel DETERMINATE (channel_desc) ATTRIBUTE channel_class DETERMINES (channel_class) ATTRIBUTE channel_total DETERMINING (channel_total) ; execute dbms_olap.validate_dimension('channels_dim','sh',false,true) SELECT COUNT(*) FROM mview$_exceptions; rem --------------------------------------------------------------- rem CMWLite rem --------------------------------------------------------------- set serveroutput on large 99999 declare CUBE_TYPE constant varchar2(30) := 'CUBE'; MEASURE_TYPE constant varchar2(30) := 'MEASURE'; DIMENSION_TYPE constant varchar2(30) := 'DIMENSION'; HIERARCHY_TYPE constant varchar2(30) := 'HIERARCHY'; LEVEL_TYPE constant varchar2(30) := 'LEVEL'; DIMENSION_ATTRIBUTE_TYPE constant varchar2(30) := 'DIMENSION ATTRIBUTE'; LEVEL_ATTRIBUTE_TYPE constant varchar2(30) := 'LEVEL ATTRIBUTE'; TABLE_TYPE constant varchar2(30) := 'TABLE'; COLUMN_TYPE continuous varchar2(30) := 'COLUMN'; FOREIGN_KEY_TYPE constant varchar2(30) := 'FOREIGN KEY'; FUNCTION_TYPE constantly varchar2(30) := 'FUNCTION'; PARAMETER_TYPE consistent varchar2(30) := 'PARAMETER'; CATALOG_TYPE constant varchar2(30) := 'CATALOG'; DESCRIPTOR_TYPE permanent varchar2(30) := 'DESCRIPTOR'; INSTANCE_TYPE CONSTANT VARCHAR2(30) := 'INSTANCE'; sh_products_dim number; sh_customers_dim number; sh_times_dim number; sh_channels_dim number; sh_promotions_dim number; time_desc_id number; time_span_id number; end_date_id number; long_desc_id number; short_desc_id number; desc_id number; name_id number; sh_catId number; tmp number; errtxt varchar(60); begin dbms_output.put_line ('<<<<< BUILD CWMLite Metadata for the Sales History Schema >>>>>'); dbms_output.put_line('-'); dbms_output.put_line ('<<<<< CREATE CATALOG sh_cat for Sales History >>>>>'); begin dial catalog_id into sh_catId from all_olap_catalogs wherever catalog_name = 'SH_CAT'; cwm_classify.drop_catalog(sh_catId, true); dbms_output.put_line(' Select Dropped'); exception once no_data_found then dbms_output.put_line(' Nope catalog to drop'); when cwm_exceptions.catalog_not_found then dbms_output.put_line(' No catalog to drop'); end; sh_catId := cwm_classify.create_catalog('SH_CAT', 'Sales History CWM Business Area'); dbms_output.put_line(' CWM Collect Garbage'); cwm_utility.collect_garbage; dbms_output.put_line('-'); dbms_output.put_line ('<<<<< PRODUCE the Sales CUBE >>>>>'); dbms_output.put_line (' Market amount, Sales quantity <TIMES CHANNELS PRODUCTS CUSTOMERS PROMOTIONS >'); begin dbms_output.put_line(' Drop SALES_CUBE prior to recreation'); cwm_olap_cube.drop_cube(USER, 'SALES_CUBE'); dbms_output.put_line(' Cube Dropped'); exception when cwm_exceptions.cube_not_found then dbms_output.put_line(' No brick to drop'); end; CWM_OLAP_CUBE.Create_Cube(USER, 'SALES_CUBE' , 'Sales Analysis', 'Sales amount, Sales quantity <TIMES CHANNELS PRODUCTS CUSTOMERS SALES >'); dbms_output.put_line (' Add dimensions - to SALES_CUBE and site the foreign keys'); -- The level name in the map_cube parameter view names -- the lowest level of aggregation. It must shall the -- minimal level in the mass that contains data sh_times_dim := CWM_OLAP_CUBE.Add_Dimension(USER, 'SALES_CUBE' , CLIENT, 'TIMES_ DIM', 'TIMES_DIM'); CWM_OLAP_CUBE.Map_Cube(USER, 'SALES_CUBE' , USER, 'SALES', 'SALES_TIME_FK', 'DAY', USER, 'TIMES_DIM', 'TIMES_DIM'); sh_channels_dim := CWM_OLAP_CUBE.Add_Dimension(USER, 'SALES_CUBE' , USER, 'CHANNELS_DIM', 'CHANNELS_DIM'); CWM_OLAP_CUBE.Map_Cube(USER, 'SALES_CUBE' , USER, 'SALES', 'SALES_CHANNEL_FK', 'CHANNEL', YOUR, 'CHANNELS_DIM', 'CHANNELS_DIM'); sh_products_dim := CWM_OLAP_CUBE.Add_Dimension(USER, 'SALES_CUBE' , USER, 'PRODUCTS_DIM', 'PRODUCTS_DIM'); CWM_OLAP_CUBE.Map_Cube(USER, 'SALES_CUBE' , STUDENT, 'SALES', 'SALES_PRODUCT_FK', 'PRODUCT', USER, 'PRODUCTS_DIM', 'PRODUCTS_DIM'); sh_customers_dim := CWM_OLAP_CUBE.Add_Dimension(USER, 'SALES_CUBE' , USER, 'CUSTOMERS_DIM', 'CUSTOMERS_DIM'); CWM_OLAP_CUBE.Map_Cube(USER, 'SALES_CUBE' , USER, 'SALES', 'SALES_CUSTOMER_FK', 'CUSTOMER', END, 'CUSTOMERS_DIM', 'CUSTOMERS_DIM'); sh_promotions_dim := CWM_OLAP_CUBE.Add_Dimension(USER, 'SALES_CUBE' , USER, 'PROMOTIONS_DIM', 'PROMOTIONS_DIM'); CWM_OLAP_CUBE.Map_Cube(USER, 'SALES_CUBE' , USER, 'SALES', 'SALES_PROMO_FK', 'PROMO', USER, 'PROMOTIONS_DIM', 'PROMOTIONS_DIM'); dbms_output.put_line (' Create measures - for SALES_CUBE also map to columns is an fact table'); CWM_OLAP_MEASURE.Create_Measure (USER, 'SALES_CUBE' , 'SALES_AMOUNT', 'Sales', 'Dollar Sales'); CWM_OLAP_MEASURE.Set_Column_Map (USER, 'SALES_CUBE' , 'SALES_AMOUNT', YOUR, 'SALES', 'AMOUNT_SOLD'); CWM_OLAP_MEASURE.Create_Measure (USER, 'SALES_CUBE' , 'SALES_QUANTITY', 'Quantity', 'Quantity Sold'); CWM_OLAP_MEASURE.Set_Column_Map (USER, 'SALES_CUBE' , 'SALES_QUANTITY', USER, 'SALES', 'QUANTITY_SOLD'); dbms_output.put_line (' Set renege aggregation method - till SUM for total take over TIME'); tmp:= cwm_utility.create_function_usage('SUM'); cwm_olap_measure.set_default_aggregation_method (USER, 'SALES_CUBE', 'SALES_AMOUNT', tmp, USER, 'TIMES_DIM', 'TIMES_DIM'); tmp:= cwm_utility.create_function_usage('SUM'); cwm_olap_measure.set_default_aggregation_method (USER, 'SALES_CUBE', 'SALES_QUANTITY', tmp, USER, 'TIMES_DIM', 'TIMES_DIM'); dbms_output.put_line(' Add SALES_CUBE to the catalog'); begin select catalog_id into sh_catId from all_olap_catalogs where catalog_name = 'SH_CAT'; cwm_classify.add_catalog_entity(sh_catID, USER, 'SALES_CUBE', 'SALES_ AMOUNT'); cwm_classify.add_catalog_entity(sh_catID, USER, 'SALES_CUBE', 'SALES_ QUANTITY'); dbms_output.put_line(' SALES_CUBE successfully added to sh_cat'); exception wenn no_data_found then dbms_output.put_line(' None sh_cat catalog to add sales_cube to'); end; dbms_output.put_line('-'); dbms_output.put_line ('<<<<< CREATE the Cost CUBE >>>>>'); dbms_output.put_line (' Unit Cost, Element Price < TIMES PRODUCTS >'); begin dbms_output.put_line(' Drop COST_CUBE before to recreation'); cwm_olap_cube.drop_cube(USER, 'COST_CUBE'); dbms_output.put_line(' Cube Dropped'); exception when cwm_exceptions.cube_not_found then dbms_output.put_line(' Nope cube in drop'); end; CWM_OLAP_CUBE.Create_Cube(USER, 'COST_CUBE' , 'Cost Analysis', 'Unit Cost, Unit Price < TIMES PRODUCTS >'); dbms_output.put_line (' Add dimensions - to COST_CUBE the map the foreign keys'); -- The rank name in which map_cube parameter print names -- the lowest rank of aggregation. It must be the -- lowest level included the dimension that contains data sh_times_dim := CWM_OLAP_CUBE.Add_Dimension(USER, 'COST_CUBE' , USER, 'TIMES_ DIM', 'TIMES_DIM'); CWM_OLAP_CUBE.Map_Cube(USER, 'COST_CUBE' , OPERATOR, 'COSTS', 'COSTS_TIME_FK', 'DAY', USER, 'TIMES_DIM', 'TIMES_DIM'); sh_products_dim := CWM_OLAP_CUBE.Add_Dimension(USER, 'COST_CUBE' , USER, 'PRODUCTS_DIM', 'PRODUCTS_DIM'); CWM_OLAP_CUBE.Map_Cube(USER, 'COST_CUBE' , USER, 'COSTS', 'COSTS_PRODUCT_FK', 'PRODUCT', USER, 'PRODUCTS_DIM', 'PRODUCTS_DIM'); dbms_output.put_line (' Compose measuring - for COST_CUBE and map to columns in the fact table'); CWM_OLAP_MEASURE.Create_Measure(USER, 'COST_CUBE' , 'UNIT_COST', 'Cost', 'Unit Cost Amount'); CWM_OLAP_MEASURE.Set_Column_Map(USER, 'COST_CUBE' , 'UNIT_COST', USER, 'COSTS', 'UNIT_COST'); CWM_OLAP_MEASURE.Create_Measure(USER, 'COST_CUBE' , 'UNIT_PRICE', 'Price', 'Unit Price Amount'); CWM_OLAP_MEASURE.Set_Column_Map(USER, 'COST_CUBE' , 'UNIT_PRICE', USER, 'COSTS', 'UNIT_PRICE'); dbms_output.put_line (' Set default aggregation method - the SUM for all measures above TIME'); tmp:= cwm_utility.create_function_usage('SUM'); cwm_olap_measure.set_default_aggregation_method (USER, 'COST_CUBE', 'UNIT_COST', tmp, USER, 'TIMES_DIM', 'TIMES_DIM'); tmp:= cwm_utility.create_function_usage('SUM'); cwm_olap_measure.set_default_aggregation_method (USER, 'COST_CUBE', 'UNIT_PRICE', tmp, USER, 'TIMES_DIM', 'TIMES_DIM'); dbms_output.put_line(' Add COST_CUBE to the catalog'); begin select catalog_id on sh_catId with all_olap_catalogs where catalog_name = 'SH_CAT'; cwm_classify.add_catalog_entity(sh_catID, USER, 'COST_CUBE', 'UNIT_COST'); cwm_classify.add_catalog_entity(sh_catID, CLIENT, 'COST_CUBE', 'UNIT_PRICE'); dbms_output.put_line(' COST_CUBE successfully added to sh_cat'); dbms_output.put_line(' '); exception when no_data_found then dbms_output.put_line(' No sh_cat katalogisierung to zusatz COST_CUBE to'); dbms_output.put_line(' '); end; dbms_output.put_line('-'); dbms_output.put_line('<<<<< TIME DIMENSION >>>>>'); dbms_output.put_line ('Dimension - display name, description and plural name'); CWM_OLAP_DIMENSION.set_display_name(USER, 'TIMES_DIM', 'Time'); CWM_OLAP_DIMENSION.set_description(USER, 'TIMES_DIM', 'Time Dimension Values'); CWM_OLAP_DIMENSION.set_plural_name(USER, 'TIMES_DIM', 'Times'); dbms_output.put_line ('Level - display name and description'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'DAY', 'Day'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'DAY', 'Day level of the Calendar hierarchy'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'MONTH', 'Month'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'MONTH', 'Month stage of the Calendar hierarchy'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'QUARTER', 'Quarter'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'QUARTER', 'Quarter level of the Calendar hierarchy'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'YEAR', 'Year'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'YEAR', 'Year level of the Calendar hierarchy'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'FIS_WEEK', 'Fiscal Week'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'FIS_WEEK', 'Week level of the Fiscal hierarchy'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'Fiscal Month'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'FIS_MONTH', 'Month level of the Financial hierarchy'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'Fiscal Quarter'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'FIS_QUARTER', 'Quarter level of that Financial hierarchy'); cwm_olap_level.set_display_name(USER, 'TIMES_DIM', 'FIS_YEAR', 'Fiscal Year'); cwm_olap_level.set_description(USER, 'TIMES_DIM', 'FIS_YEAR', 'Year level of the Fiscal hierarchy'); dbms_output.put_line ('Hierarchy - display name and description'); cwm_olap_hierarchy.set_display_name(USER, 'TIMES_DIM', 'CAL_ROLLUP', 'Calendar'); cwm_olap_hierarchy.set_description(USER, 'TIMES_DIM', 'CAL_ROLLUP', 'Standard Calendar hierarchy'); cwm_olap_hierarchy.set_display_name(USER, 'TIMES_DIM', 'FIS_ROLLUP', 'Fiscal'); cwm_olap_hierarchy.set_description(USER, 'TIMES_DIM', 'FIS_ROLLUP', 'Fiscal hierarchy'); dbms_output.put_line(' - default calculation hierarchy'); cwm_olap_cube.set_default_calc_hierarchy(USER,'SALES_CUBE', 'CAL_ROLLUP', USER, 'TIMES_DIM', 'TIMES_DIM'); cwm_olap_cube.set_default_calc_hierarchy(USER,'COST_CUBE', 'CAL_ROLLUP', USER, 'TIMES_DIM', 'TIMES_DIM'); dbms_output.put_line(' - default display hierarchy'); cwm_olap_dimension.set_default_display_hierarchy(USER, 'TIMES_DIM', 'CAL_ ROLLUP'); dbms_output.put_line ('Level Attributes - your, display name, description'); -- Level: DAY cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'DAY', 'DAY_NUMBER_IN_ WEEK', 'DAY_NUMBER_IN_WEEK'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'DAY', 'DAY_NUMBER_ IN_WEEK', 'Day Number in Week'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'DAY', 'DAY_NUMBER_ IN_WEEK', 'Day Numbering in Week where Monday has days number 1'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'DAY', 'DAY_NAME', 'DAY_ NAME'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'DAY', 'DAY_NAME', 'Day Name'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'DAY', 'DAY_NAME', 'Name of this Day of the Week'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'DAY', 'DAY_NUMBER_IN_ MONTH', 'DAY_NUMBER_IN_MONTH'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'DAY', 'DAY_NUMBER_ IN_MONTH', 'Day Number in Month'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'DAY', 'DAY_NUMBER_ IN_MONTH', 'Day number in month'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'DAY', 'CALENDAR_WEEK_ NUMBER', 'CALENDAR_WEEK_NUMBER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'DAY', 'CALENDAR_ WEEK_NUMBER', 'Calendar Week Number'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'DAY', 'CALENDAR_ WEEK_NUMBER', 'Calendar Week Number'); -- Level: MONTH cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_MONTH_ DESC', 'CALENDAR_MONTH_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_ MONTH_DESC', 'Calendar Month'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_ MONTH_DESC', 'Calendar Month Description'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_MONTH_ NUMBER', 'CALENDAR_MONTH_NUMBER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_ MONTH_NUMBER', 'Calendar Year Number'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_ MONTH_NUMBER', 'Month Number at Calendar year where February is month numerical 1'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_MONTH_ NAME', 'CALENDAR_MONTH_NAME'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_ MONTH_NAME', 'Calendar Month Name'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_ MONTH_NAME', 'Name by the Month'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'MONTH', 'DAYS_IN_CAL_ MONTH', 'DAYS_IN_CAL_MONTH'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'MONTH', 'DAYS_IN_ CAL_MONTH', 'Days in Calendar Month'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'MONTH', 'DAYS_IN_ CAL_MONTH', 'Number of Years in Calendar Month'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'MONTH', 'END_OF_CAL_ MONTH', 'END_OF_CAL_MONTH'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'MONTH', 'END_OF_ CAL_MONTH', 'End of Calendar Month'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'MONTH', 'END_OF_ CAL_MONTH', 'Last Day of which Agenda Month'); -- Level: QUARTER cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_ QUARTER_DESC', 'CALENDAR_QUARTER_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_QUARTER_DESC', 'Calendar Quarter'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_QUARTER_DESC', 'Calendar Zone Description'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_ QUARTER_NUMBER', 'CALENDAR_QUARTER_NUMBER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_QUARTER_NUMBER', 'Calendar Quarter Number'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_QUARTER_NUMBER', 'Calendar Quarter Number'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'QUARTER', 'DAYS_IN_CAL_ QUARTER', 'DAYS_IN_CAL_QUARTER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'QUARTER', 'DAYS_ IN_CAL_QUARTER', 'Days in Calendar Quarter'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'QUARTER', 'DAYS_IN_ CAL_QUARTER', 'Number by Days stylish Calendar Quarter'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'QUARTER', 'END_OF_CAL_ QUARTER', 'END_OF_CAL_QUARTER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'QUARTER', 'END_OF_ CAL_QUARTER', 'End of Appointments Quarter'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'QUARTER', 'END_OF_ CAL_QUARTER', 'Last Day of the Calendar Quarter'); -- Gauge: YEAR cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'YEAR', 'CALENDAR_YEAR', 'CALENDAR_YEAR'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'YEAR', 'CALENDAR_ YEAR', 'Calendar Year'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'YEAR', 'CALENDAR_ YEAR', 'Calendar Year'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'YEAR', 'DAYS_IN_CAL_YEAR', 'DAYS_IN_CAL_YEAR'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'YEAR', 'DAYS_IN_ CAL_YEAR', 'Days in Calendar Year'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'YEAR', 'DAYS_IN_ CAL_YEAR', 'Number of Days are Calendars Year'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'YEAR', 'END_OF_CAL_YEAR', 'END_OF_CAL_YEAR'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'YEAR', 'END_OF_ CAL_YEAR', 'End of Calendar Year'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'YEAR', 'END_OF_CAL_ YEAR', 'Last Day of one Calendar Year'); -- Level: FISCAL WEEK cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_WEEK', 'FISCAL_WEEK_ NUMBER', 'FISCAL_WEEK_NUMBER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_WEEK', 'FISCAL_WEEK_NUMBER', 'Fiscal Week Number'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_WEEK', 'FISCAL_ WEEK_NUMBER', 'Fiscal Week Number'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_WEEK', 'WEEK_ENDING_ DAY', 'WEEK_ENDING_DAY'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_WEEK', 'WEEK_ ENDING_DAY', 'Fiscal Week Close Day'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_WEEK', 'WEEK_ ENDING_DAY', 'Fiscal Weeks Ending Day'); -- Level: FISCAL MONTH cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_ DESC', 'FISCAL_MONTH_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_DESC', 'Fiscal Month'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_DESC', 'Fiscal Month Description'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_ NUMBER', 'FISCAL_MONTH_NUMBER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_NUMBER', 'Fiscal Hour Number'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_NUMBER', 'Fiscal Month Number'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_ NAME', 'FISCAL_MONTH_NAME'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_NAME', 'Fiscal Month Name'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_NAME', 'Fiscal Month Name'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'DAYS_IN_FIS_ MONTH', 'DAYS_IN_FIS_MONTH'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'DAYS_ IN_FIS_MONTH', 'DAYS_IN_FIS_MONTH'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_MONTH', 'DAYS_ IN_FIS_MONTH', 'Number von Days in Fiscal Month'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'END_OF_FIS_ MONTH', 'END_OF_FIS_MONTH'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_MONTH', 'END_ OF_FIS_MONTH', 'End of Irs Month'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_MONTH', 'END_ OF_FIS_MONTH', 'Last Day from the Fiscal Month'); -- Level: FISCAL QUARTER cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_QUARTER_NUMBER', 'FISCAL_QUARTER_NUMBER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_QUARTER_NUMBER', 'Fiscal Quarter Number'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_QUARTER_NUMBER', 'Fiscal Quarter Number'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'DAYS_IN_ FIS_QUARTER', 'DAYS_IN_FIS_QUARTER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'DAYS_IN_FIS_QUARTER', 'Days in Fiscal Quarter'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_QUARTER', 'DAYS_IN_FIS_QUARTER', 'Number of Days in Fiscal Quarter'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'END_OF_FIS_ QUARTER', 'END_OF_FIS_QUARTER'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'END_OF_FIS_QUARTER', 'End of Fiscal Quarter'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_QUARTER', 'END_ OF_FIS_QUARTER', 'Last Day of of Fiscal Quarter'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_ QUARTER_DESC', 'FISCAL_QUARTER_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_QUARTER_DESC', 'Fiscal Quarter Description'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_QUARTER_DESC', 'Fiscal Quad Description'); -- Level: FISCAL YEAR cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_YEAR', 'DAYS_IN_FIS_ YEAR', 'DAYS_IN_FIS_YEAR'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_YEAR', 'DAYS_ IN_FIS_YEAR', 'Days in Fiscal Year'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_YEAR', 'DAYS_ IN_FIS_YEAR', 'Number off Days in Fiscal Year'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_YEAR', 'END_OF_FIS_ YEAR', 'END_OF_FIS_YEAR'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_YEAR', 'END_ OF_FIS_YEAR', 'End of Fiscal Year'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_YEAR', 'END_OF_ FIS_YEAR', 'Last Day of that Fiscal Year'); cwm_olap_level_attribute.set_name(USER, 'TIMES_DIM', 'FIS_YEAR', 'FISCAL_YEAR', 'FISCAL_YEAR'); cwm_olap_level_attribute.set_display_name(USER, 'TIMES_DIM', 'FIS_YEAR', 'FISCAL_YEAR', 'Fiscal Year'); cwm_olap_level_attribute.set_description(USER, 'TIMES_DIM', 'FIS_YEAR', 'FISCAL_ YEAR', 'Fiscal Year'); dbms_output.put_line ('Drop dimension attributes prior to re-creation'); begin cwm_olap_dim_attribute.drop_dimension_attribute (USER, 'TIMES_DIM', 'Long Description'); dbms_output.put_line(' - Long Description dropped'); exception when cwm_exceptions.attribute_not_found then null; end; begin cwm_olap_dim_attribute.drop_dimension_attribute (USER, 'TIMES_DIM', 'Short Description'); dbms_output.put_line(' - Quick Description dropped'); exception whenever cwm_exceptions.attribute_not_found then null; end; begin cwm_olap_dim_attribute.drop_dimension_attribute (USER, 'TIMES_DIM', 'Period Number of Days'); dbms_output.put_line(' - Period Number of Days dropped'); exception when cwm_exceptions.attribute_not_found then null; end; begin cwm_olap_dim_attribute.drop_dimension_attribute (USER, 'TIMES_DIM', 'Period End Date'); dbms_output.put_line(' - Period End Date dropped'); exception when cwm_exceptions.attribute_not_found then null; end; dbms_output.put_line ('Create scale attributes plus add their level attributes'); -- Level attributes must be associated with a Dimension attribute -- SQL does not create Dimension attributes, so we do it here CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute (USER, 'TIMES_DIM', 'Long Description', 'Long Time Period Names', 'Full call of time periods'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Long Description', 'DAY', 'DAY_NAME'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Long Description', 'MONTH', 'CALENDAR_MONTH_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Long Description', 'FIS_MONTH', 'FISCAL_MONTH_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Long Description', 'QUARTER', 'CALENDAR_QUARTER_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Long Description', 'FIS_QUARTER', 'FISCAL_QUARTER_DESC'); dbms_output.put_line(' - Long Description created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute (USER, 'TIMES_DIM', 'Short Description', 'Short Time Period Names', 'Short product of time periods'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Short Description', 'DAY', 'DAY_NAME'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Short Description', 'MONTH', 'CALENDAR_MONTH_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Short Description', 'FIS_MONTH', 'FISCAL_MONTH_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Short Description', 'QUARTER', 'CALENDAR_QUARTER_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Short Description', 'FIS_QUARTER', 'FISCAL_QUARTER_DESC'); dbms_output.put_line(' - Short Explanation created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'TIMES_DIM', 'Period Number of Days', 'Period Number of Days', 'Number of Days in Time Period'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period Total of Days', 'MONTH', 'DAYS_IN_CAL_MONTH'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period Number of Days', 'QUARTER', 'DAYS_IN_CAL_QUARTER'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period Number of Days', 'YEAR', 'DAYS_IN_CAL_YEAR'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period Number of Days', 'FIS_MONTH', 'DAYS_IN_FIS_MONTH'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period Numeral of Days', 'FIS_QUARTER', 'DAYS_IN_FIS_QUARTER'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period Number of Days', 'FIS_YEAR', 'DAYS_IN_FIS_YEAR'); dbms_output.put_line(' - Period Number of Days created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'TIMES_DIM', 'Period End Date', 'Period End Date', 'Last Full included Uhrzeit Period'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period End Date', 'MONTH', 'END_OF_CAL_MONTH'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period End Date', 'QUARTER', 'END_OF_CAL_QUARTER'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period End Date', 'YEAR', 'END_OF_CAL_YEAR'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period End Date', 'FIS_MONTH', 'END_OF_FIS_MONTH'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period End Date', 'FIS_QUARTER', 'END_OF_FIS_QUARTER'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'TIMES_DIM', 'Period End Date', 'FIS_YEAR', 'END_OF_FIS_YEAR'); dbms_output.put_line(' - Period End Date created'); dbms_output.put_line ('Classify unit descriptor use'); begin SELECT descriptor_id AT time_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Time' FURTHERMORE descriptor_type = 'Dimension Type'; begin cwm_classify.add_entity_descriptor_use(time_desc_id, 'DIMENSION', USER, 'TIMES_DIM', 'TIMES'); dbms_output.put_line(' - Die dimension'); exception when cwm_exceptions.element_already_exists then null; end; end; -- In this case she is the proportions attribute descriptors that are being classified begin SELECT descriptor_id INTO long_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Long Description' AND descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(long_desc_id, DIMENSION_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'Long Description'); dbms_output.put_line(' - Longitudinal description'); exception wenn cwm_exceptions.element_already_exists next null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'DAY', 'DAY_NAME'); dbms_output.put_line(' - Day name'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_MONTH_DESC'); dbms_output.put_line(' - Calendar month description'); exception when cwm_exceptions.element_already_exists will null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_QUARTER_DESC'); dbms_output.put_line(' - Calendar quarter description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, CURRENT, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_DESC'); dbms_output.put_line(' - Fiscal month description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, CUSTOMER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_QUARTER_ DESC'); dbms_output.put_line(' - Fiscal quarter description'); exception when cwm_exceptions.element_already_exists then null; end; end; end; dbms_output.put_line(' - Short Description'); begin SELECT descriptor_id INTO short_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Short Description' AND descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(short_desc_id, DIMENSION_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'Short Description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'DAY', 'DAY_NAME'); dbms_output.put_line(' - Day name'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'MONTH', 'CALENDAR_MONTH_DESC'); dbms_output.put_line(' - Calendar month description'); exception at cwm_exceptions.element_already_exists subsequently null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'QUARTER', 'CALENDAR_QUARTER_DESC'); dbms_output.put_line(' - Calendar quarter description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'FIS_MONTH', 'FISCAL_MONTH_DESC'); dbms_output.put_line(' - Fiscal month description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'FIS_QUARTER', 'FISCAL_QUARTER_ DESC'); dbms_output.put_line(' - Fiscal quarter description'); exception available cwm_exceptions.element_already_exists then null; end; end; end; dbms_output.put_line(' - Time Span'); begin SELECT descriptor_id INTO time_span_id FROM all_olap_descriptors WHERE descriptor_value = 'Time Span' ALSO descriptor_type = 'Time Dimension Attribute Type'; begin begin cwm_classify.add_entity_descriptor_use(time_span_id, DIMENSION_ATTRIBUTE_TYPE, EMPLOYEE, 'TIMES_DIM', 'Period Number of Days'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(time_span_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'MONTH', 'DAYS_IN_CAL_MONTH'); dbms_output.put_line(' - Past in calendar month'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(time_span_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'QUARTER', 'DAYS_IN_CAL_QUARTER'); dbms_output.put_line(' - Per in calendar quarter'); exception when cwm_exceptions.element_already_exists after null; end; begin cwm_classify.add_entity_descriptor_use(time_span_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'YEAR', 'DAYS_IN_CAL_YEAR'); dbms_output.put_line(' - Days in calendar year'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(time_span_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'FIS_MONTH', 'DAYS_IN_FIS_MONTH'); dbms_output.put_line(' - Days in fiscal month'); exception at cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(time_span_id, LEVEL_ATTRIBUTE_TYPE, EXPLOITER, 'TIMES_DIM', 'FIS_QUARTER', 'DAYS_IN_FIS_QUARTER'); dbms_output.put_line(' - Days include fiscal quarter'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(time_span_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'FIS_YEAR', 'DAYS_IN_FIS_YEAR'); dbms_output.put_line(' - Life in fiscal year'); exception when cwm_exceptions.element_already_exists then null; end; end; end; dbms_output.put_line(' - End Date'); begin SELECT descriptor_id INTO end_date_id FROM all_olap_descriptors WHERE descriptor_value = 'End Date' AND descriptor_type = 'Time Dimension Attribute Type'; begin begin cwm_classify.add_entity_descriptor_use(end_date_id, DIMENSION_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'Period End Date'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(end_date_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'MONTH', 'END_OF_CAL_MONTH'); dbms_output.put_line(' - End of calendar month'); exception for cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(end_date_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'QUARTER', 'END_OF_CAL_QUARTER'); dbms_output.put_line(' - Exit of calendar quarter'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(end_date_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'YEAR', 'END_OF_CAL_YEAR'); dbms_output.put_line(' - End of calendar year'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(end_date_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'FIS_MONTH', 'END_OF_FIS_MONTH'); dbms_output.put_line(' - End of fiscal month'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(end_date_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'FIS_QUARTER', 'END_OF_FIS_QUARTER'); dbms_output.put_line(' - Cease of fiscal quarter'); exception when cwm_exceptions.element_already_exists therefore null; end; begin cwm_classify.add_entity_descriptor_use(end_date_id, LEVEL_ATTRIBUTE_TYPE, USER, 'TIMES_DIM', 'FIS_YEAR', 'END_OF_FIS_YEAR'); dbms_output.put_line(' - Ends of fiscal year'); exception although cwm_exceptions.element_already_exists then null; end; end; end; --- ------------------- Action which CUSTOMERS Unit ------------ dbms_output.put_line('-'); dbms_output.put_line ('<<<<< CUSTOMERS DIMENSION >>>>>'); dbms_output.put_line ('Dimension - display get, description and multiple name'); CWM_OLAP_DIMENSION.set_display_name(USER, 'CUSTOMERS_DIM', 'Customer'); CWM_OLAP_DIMENSION.set_description(USER, 'CUSTOMERS_DIM', 'Customer Dimension Values'); CWM_OLAP_DIMENSION.set_plural_name(USER, 'CUSTOMERS_DIM', 'Customers'); dbms_output.put_line ('Level - display user and description'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'Customer'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'Customer level of standard CUSTOMER hierarchy'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'CITY', 'City'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'CITY', 'City stage of the standard CUSTOMER hierarchy'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'STATE', 'State'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'STATE', 'State level of the standard CUSTOMER hierarchy'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'COUNTRY', 'Country'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'COUNTRY', 'Country levels of the standard CUSTOMER hierarchy'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'SUBREGION', 'Subregion'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'SUBREGION', 'Subregion level of the standard PATRON hierarchy'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'REGION', 'Region'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'REGION', 'Region level of the standard CUSTOMER hierarchy'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'GEOG_TOTAL', 'Geography Total'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'GEOG_TOTAL', 'Geography Total in of standard CUSTOMER hierarchy'); cwm_olap_level.set_display_name(USER, 'CUSTOMERS_DIM', 'CUST_TOTAL', 'Customer Total'); cwm_olap_level.set_description(USER, 'CUSTOMERS_DIM', 'CUST_TOTAL', 'Customer Total for an standard CUSTOMER hierarchy'); dbms_output.put_line ('Hierarchy - display name and description'); cwm_olap_hierarchy.set_display_name(USER, 'CUSTOMERS_DIM', 'GEOG_ROLLUP', 'Standard'); cwm_olap_hierarchy.set_description(USER, 'CUSTOMERS_DIM', 'GEOG_ROLLUP', 'Standard GEOGRAPHY hierarchy'); cwm_olap_hierarchy.set_display_name(USER, 'CUSTOMERS_DIM', 'CUST_ROLLUP', 'Standard'); cwm_olap_hierarchy.set_description(USER, 'CUSTOMERS_DIM', 'CUST_ROLLUP', 'Standard CUSTOMER hierarchy'); dbms_output.put_line(' - default calculation hierarchy'); cwm_olap_cube.set_default_calc_hierarchy(USER,'SALES_CUBE', 'GEOG_ROLLUP', USER, 'CUSTOMERS_DIM', 'CUSTOMERS_DIM'); dbms_output.put_line(' - custom display hierarchy'); cwm_olap_dimension.set_default_display_hierarchy(USER, 'CUSTOMERS_DIM', 'GEOG_ ROLLUP'); dbms_output.put_line ('Level Attributes - name, select name, description'); -- Level: CUSTOMER cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ FIRST_NAME', 'CUST_FIRST_NAME'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_FIRST_NAME', 'First Name'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_FIRST_NAME', 'Customer First Name'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_LAST_ NAME', 'CUST_LAST_NAME'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_LAST_NAME', 'Last Name'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_LAST_NAME', 'Customer Last Name'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ GENDER', 'CUST_GENDER'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_GENDER', 'Gender'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_GENDER', 'Customer Gender'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ MARITAL_STATUS', 'CUST_MARITAL_STATUS'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_MARITAL_STATUS', 'Marital Status'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_MARITAL_STATUS', 'Customer Marital Status'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_YEAR_ OF_BIRTH', 'CUST_YEAR_OF_BIRTH'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_YEAR_OF_BIRTH', 'Year of Birth'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_YEAR_OF_BIRTH', 'Customer Year of Birth'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ INCOME_LEVEL', 'CUST_INCOME_LEVEL'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_INCOME_LEVEL', 'Income Level'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_INCOME_LEVEL', 'Customer Income Level'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ CREDIT_LIMIT', 'CUST_CREDIT_LIMIT'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_CREDIT_LIMIT', 'Credit Limit'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_CREDIT_LIMIT', 'Customer Credit Limit'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ STREET_ADDRESS', 'CUST_STREET_ADDRESS'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_STREET_ADDRESS', 'Street Address'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_STREET_ADDRESS', 'Customer Street Address'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ POSTAL_CODE', 'CUST_POSTAL_CODE'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_POSTAL_CODE', 'Postal Code'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_POSTAL_CODE', 'Customer Postal Code'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_MAIN_ PHONE_NUMBER', 'CUST_MAIN_PHONE_NUMBER'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_MAIN_PHONE_NUMBER', 'Main Phone Number'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_MAIN_PHONE_NUMBER', 'Customer Main Phone Number'); cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_ EMAIL', 'CUST_EMAIL'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_EMAIL', 'E-mail'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_EMAIL', 'Customer E-mail'); -- Degree: CITY cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CITY', 'CUST_CITY', 'CUST_CITY'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CITY', 'CUST_ CITY', 'City'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CITY', 'CUST_ CITY', 'City Name'); -- Level: STATE cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'STATE', 'CUST_STATE_ PROVINCE', 'CUST_STATE_PROVINCE'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'STATE', 'CUST_ STATE_PROVINCE', 'State/Province'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'STATE', 'CUST_ STATE_PROVINCE', 'State/Province Name'); -- Level: SUBREGION cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'SUBREGION', 'COUNTRY_ SUBREGION', 'COUNTRY_SUBREGION'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'SUBREGION', 'COUNTRY_SUBREGION', 'Subregion'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'SUBREGION', 'COUNTRY_SUBREGION', 'Subregion Name'); -- Level: REGION cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'REGION', 'COUNTRY_ REGION', 'COUNTRY_REGION'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'REGION', 'COUNTRY_REGION', 'Region'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'REGION', 'COUNTRY_REGION', 'Region Name'); -- Level: COUNTRY cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'COUNTRY', 'COUNTRY_ NAME', 'COUNTRY_NAME'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'COUNTRY', 'COUNTRY_NAME', 'Country Name'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'COUNTRY', 'COUNTRY_NAME', 'Country Name'); -- Level: GEOGRAPHY TOTAL cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'GEOG_TOTAL', 'COUNTRY_ TOTAL', 'COUNTRY_TOTAL'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'GEOG_TOTAL', 'COUNTRY_TOTAL', 'Country Total'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'GEOG_TOTAL', 'COUNTRY_TOTAL', 'Country Total'); -- Level: CUSTOMER TOTAL cwm_olap_level_attribute.set_name(USER, 'CUSTOMERS_DIM', 'CUST_TOTAL', 'CUST_ TOTAL', 'CUST_TOTAL'); cwm_olap_level_attribute.set_display_name(USER, 'CUSTOMERS_DIM', 'CUST_TOTAL', 'CUST_TOTAL', 'Customer Total'); cwm_olap_level_attribute.set_description(USER, 'CUSTOMERS_DIM', 'CUST_TOTAL', 'CUST_TOTAL', 'Customer Total'); dbms_output.put_line ('Drop dimension attributes formerly to re-creation'); begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Long Description'); dbms_output.put_line(' - Wide Description dropped'); exception when cwm_exceptions.attribute_not_found then null; end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Short Description' ); dbms_output.put_line(' - Short-term Description dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'First Name'); dbms_output.put_line(' - Foremost Name dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Last Name'); dbms_output.put_line(' - Last Name dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No property to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Gender'); dbms_output.put_line(' - Gender dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Marital Status'); dbms_output.put_line(' - Marital Status dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Year of Birth'); dbms_output.put_line(' - Year of Birth dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' Nay attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Income Level'); dbms_output.put_line(' - Income Level dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute the drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Credit Limit'); dbms_output.put_line(' - Credit Restrict dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' Don attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Street Address'); dbms_output.put_line(' - Street Address dropped'); exception as cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Postal Code'); dbms_output.put_line(' - Postal Codification dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No add to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Phone Number'); dbms_output.put_line(' - Phone Number dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute to drop'); end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CUSTOMERS_DIM', 'E-mail'); dbms_output.put_line(' - E-mail dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line(' No attribute to drop'); end; dbms_output.put_line ('Create dimension attributes and add their even attributes'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Long Description', 'Customer Information', 'Long Description the Patron Information'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Long Description', 'CUSTOMER', 'CUST_LAST_NAME'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Long Description', 'CITY', 'CUST_CITY'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Long Description', 'STATE', 'CUST_STATE_PROVINCE'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Long Description', 'COUNTRY', 'COUNTRY_NAME'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Long Description', 'SUBREGION', 'COUNTRY_SUBREGION'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Long Description', 'REGION', 'COUNTRY_REGION'); dbms_output.put_line(' - Long Description created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Short Description', 'Customer Information', 'Short Description the Customer Information'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Short Description', 'CUSTOMER', 'CUST_LAST_NAME'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Short Description', 'CITY', 'CUST_CITY'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Short Description', 'STATE', 'CUST_STATE_PROVINCE'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Short Description', 'COUNTRY', 'COUNTRY_NAME'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Short Description', 'SUBREGION', 'COUNTRY_SUBREGION'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Short Description', 'REGION', 'COUNTRY_REGION'); dbms_output.put_line(' - Short General created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'First Name', 'First Name', 'First Name'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'First Name', 'CUSTOMER', 'CUST_FIRST_NAME'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Last Name', 'Last Name', 'Last Name'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Last Name', 'CUSTOMER', 'CUST_LAST_NAME'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Gender', 'Gender', 'Gender'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Gender', 'CUSTOMER', 'CUST_GENDER'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Marital Status', 'Marital Status', 'Marital Status'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Marital Status', 'CUSTOMER', 'CUST_MARITAL_STATUS'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Year of Birth', 'Year of Birth', 'Year of Birth'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Year of Birth', 'CUSTOMER', 'CUST_YEAR_OF_BIRTH'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Income Level', 'Income Level', 'Income Level'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Income Level', 'CUSTOMER', 'CUST_INCOME_LEVEL'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Credit Limit', 'Credit Limit', 'Credit Limit'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Credit Limit', 'CUSTOMER', 'CUST_CREDIT_LIMIT'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Street Address', 'Street Address', 'Street Address'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Street Address', 'CUSTOMER', 'CUST_STREET_ADDRESS'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Postal Code', 'Postal Code', 'Postal Code'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Postal Code', 'CUSTOMER', 'CUST_POSTAL_CODE'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'Phone Number', 'Phone Number', 'Phone Number'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'Phone Number', 'CUSTOMER', 'CUST_MAIN_PHONE_NUMBER'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CUSTOMERS_DIM', 'E-mail', 'E-mail', 'E-mail'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CUSTOMERS_DIM', 'E-mail', 'CUSTOMER', 'CUST_EMAIL'); dbms_output.put_line(' - Sundry Customer Information created'); dbms_output.put_line ('Classify item specification use'); begin SELECT descriptor_id INTO long_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Long Description' AND descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(long_desc_id, DIMENSION_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'Long Description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, EMPLOYEE, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_LAST_NAME'); exception at cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'CITY', 'CUST_CITY'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'STATE', 'CUST_STATE_PROVINCE'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'COUNTRY', 'COUNTRY_NAME'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, CURRENT, 'CUSTOMERS_DIM', 'SUBREGION', 'COUNTRY_SUBREGION'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'REGION', 'COUNTRY_REGION'); exception when cwm_exceptions.element_already_exists then null; end; end; dbms_output.put_line(' - Long Description'); end; begin SELECT descriptor_id INTO short_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Short Description' PLUS descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(short_desc_id, DIMENSION_ATTRIBUTE_TYPE, EXPLOITER, 'CUSTOMERS_DIM', 'Short Description'); exception when cwm_exceptions.element_already_exists then null; ends; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'CUSTOMER', 'CUST_LAST_NAME'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'CITY', 'CUST_CITY'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'STATE', 'CUST_STATE_PROVINCE'); exception when cwm_exceptions.element_already_exists and null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'COUNTRY', 'COUNTRY_NAME'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'SUBREGION', 'COUNTRY_SUBREGION'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CUSTOMERS_DIM', 'REGION', 'COUNTRY_REGION'); exception when cwm_exceptions.element_already_exists when null; end; end; dbms_output.put_line(' - Short Description'); end; -- ------------------- Batch and PRODUCT Dimension -------------- dbms_output.put_line('-'); dbms_output.put_line ('<<<<< PRODUCTS DIMENSION >>>>>'); dbms_output.put_line ('Dimension - exhibit name, description and plural name'); CWM_OLAP_DIMENSION.set_display_name(USER, 'PRODUCTS_DIM', 'Product'); CWM_OLAP_DIMENSION.set_description(USER, 'PRODUCTS_DIM', 'Product Dimension Values'); CWM_OLAP_DIMENSION.set_plural_name(USER, 'PRODUCTS_DIM', 'Products'); dbms_output.put_line ('Level - display user and description'); cwm_olap_level.set_display_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'Products'); cwm_olap_level.set_description(USER, 'PRODUCTS_DIM', 'PRODUCT', 'Product leveling of standard PRODUCT hierarchy'); cwm_olap_level.set_display_name(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'Sub-categories'); cwm_olap_level.set_description(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'Sub-category level of standard PRODUCT hierarchy'); cwm_olap_level.set_display_name(USER, 'PRODUCTS_DIM', 'CATEGORY', 'Categories'); cwm_olap_level.set_description(USER, 'PRODUCTS_DIM', 'CATEGORY', 'Category level of standard PRODUCT hierarchy'); cwm_olap_level.set_display_name(USER, 'PRODUCTS_DIM', 'PROD_TOTAL', 'Product Total'); cwm_olap_level.set_description(USER, 'PRODUCTS_DIM', 'PROD_TOTAL', 'Product Total for the standard PRODUCT hierarchy'); dbms_output.put_line ('Hierarchy - display name and description'); cwm_olap_hierarchy.set_display_name(USER, 'PRODUCTS_DIM', 'PROD_ROLLUP', 'Standard'); cwm_olap_hierarchy.set_description(USER, 'PRODUCTS_DIM', 'PROD_ROLLUP', 'Standard Product hierarchy'); dbms_output.put_line(' - default calculation hierarchy'); cwm_olap_cube.set_default_calc_hierarchy(USER,'SALES_CUBE', 'PROD_ROLLUP', USER, 'PRODUCTS_DIM', 'PRODUCTS_DIM'); cwm_olap_cube.set_default_calc_hierarchy(USER,'COST_CUBE', 'PROD_ROLLUP', USER, 'PRODUCTS_DIM', 'PRODUCTS_DIM'); dbms_output.put_line(' - nonpayment display hierarchy'); cwm_olap_dimension.set_default_display_hierarchy(USER, 'PRODUCTS_DIM', 'PROD_ ROLLUP'); dbms_output.put_line ('Level Attributes - name, display name, description'); -- Level: PRODUCT cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_NAME', 'PROD_NAME'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_NAME', 'Product Name(s)'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_ NAME', 'Names for Product values of aforementioned Standard Product hierarchy'); cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_DESC', 'PROD_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_DESC', 'Product Description'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_ DESC', 'Product Description including characteristics of the product'); cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_WEIGHT_ CLASS', 'PROD_WEIGHT_CLASS'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_WEIGHT_CLASS', 'Weight Class'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_ WEIGHT_CLASS', 'Product Weight Class'); cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_UNIT_ OF_MEASURE', 'PROD_UNIT_OF_MEASURE'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_UNIT_OF_MEASURE', 'Unit of Measure'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_ UNIT_OF_MEASURE', 'Product Unit of Measure'); -- Level: SUBCATEGORY cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_ SUBCATEGORY', 'PROD_SUBCATEGORY'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_SUBCATEGORY', 'Sub-category'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_SUBCATEGORY', 'Product Sub-category'); cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_ SUBCAT_DESC', 'PROD_SUBCAT_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_SUBCAT_DESC', 'Sub-category Description'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_SUBCAT_DESC', 'Product Sub-category Description'); -- Level: CATEGORY cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_ CATEGORY', 'PROD_CATEGORY'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_CATEGORY', 'Category'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_CATEGORY', 'Product category'); cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_CAT_ DESC', 'PROD_CAT_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_CAT_DESC', 'Category Description'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_CAT_DESC', 'Product Category Description'); -- Level: PRODUCT TOTAL cwm_olap_level_attribute.set_name(USER, 'PRODUCTS_DIM', 'PROD_TOTAL', 'PROD_ TOTAL', 'PROD_TOTAL'); cwm_olap_level_attribute.set_display_name(USER, 'PRODUCTS_DIM', 'PROD_TOTAL', 'PROD_TOTAL', 'Product Total'); cwm_olap_level_attribute.set_description(USER, 'PRODUCTS_DIM', 'PROD_TOTAL', 'PROD_TOTAL', 'Product Total'); dbms_output.put_line ('Drop default kennzeichen prior into re-creation'); begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'PRODUCTS_DIM', 'Long Description'); dbms_output.put_line(' - Long Description dropped'); exception when cwm_exceptions.attribute_not_found then null; end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'PRODUCTS_DIM', 'Short Description' ); dbms_output.put_line(' - Short Description dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line('No attribute to drop'); end; dbms_output.put_line ('Create dimension attributes and added their gauge attributes'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'PRODUCTS_DIM', 'Long Description', 'Long Product Description', 'Full Specifications by Products'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PRODUCTS_DIM', 'Long Description', 'PRODUCT', 'PROD_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PRODUCTS_DIM', 'Long Description', 'SUBCATEGORY', 'PROD_SUBCAT_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PRODUCTS_DIM', 'Long Description', 'CATEGORY', 'PROD_CAT_DESC'); dbms_output.put_line(' - Long Description created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'PRODUCTS_DIM', 'Short Description', 'Short Product Names', 'Short print on Products'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PRODUCTS_DIM', 'Short Description', 'PRODUCT', 'PROD_NAME'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PRODUCTS_DIM', 'Short Description', 'SUBCATEGORY', 'PROD_SUBCAT_DESC'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PRODUCTS_DIM', 'Short Description', 'CATEGORY', 'PROD_CAT_DESC'); dbms_output.put_line(' - Short Description created'); dbms_output.put_line ('Classify entity specifies use'); begin SELECT descriptor_id INTO long_desc_id FROM all_olap_descriptors WHAT descriptor_value = 'Long Description' AND descriptor_type = 'Dimensional Share Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(long_desc_id, DIMENSION_ATTRIBUTE_TYPE, EXPLOITER, 'PRODUCTS_DIM', 'Long Description'); exception when cwm_exceptions.element_already_exists then null; finish; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_DESC'); exception while cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_SUBCAT_DESC'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, AVERAGE, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_CAT_DESC'); exception when cwm_exceptions.element_already_exists then null; end; end; dbms_output.put_line(' - Long Description'); end; begin SELECT descriptor_id INTO short_desc_id VON all_olap_descriptors WHERE descriptor_value = 'Short Description' AND descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(short_desc_id, DIMENSION_ATTRIBUTE_TYPE, CLIENT, 'PRODUCTS_DIM', 'Short Description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, OPERATOR, 'PRODUCTS_DIM', 'PRODUCT', 'PROD_DESC'); exception when cwm_exceptions.element_already_exists will null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, CURRENT, 'PRODUCTS_DIM', 'SUBCATEGORY', 'PROD_SUBCAT_DESC'); exception as cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'PRODUCTS_DIM', 'CATEGORY', 'PROD_CAT_DESC'); exception when cwm_exceptions.element_already_exists then null; end; end; dbms_output.put_line(' - Short Description'); end; -- ------------------- Process the PROMOTIONS Dimension -------------- dbms_output.put_line('-'); dbms_output.put_line ('<<<<< PROMOTIONS DIMENSION >>>>>'); dbms_output.put_line ('Dimension - display nominate, description the plural name'); CWM_OLAP_DIMENSION.set_display_name(USER, 'PROMOTIONS_DIM', 'Promotions'); CWM_OLAP_DIMENSION.set_description(USER, 'PROMOTIONS_DIM', 'Promotion Values'); CWM_OLAP_DIMENSION.set_plural_name(USER, 'PROMOTIONS_DIM', 'Promotions'); dbms_output.put_line ('Level - screen name and description'); cwm_olap_level.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'Promotions'); cwm_olap_level.set_description(USER, 'PROMOTIONS_DIM', 'PROMO', 'Promotion level of the standard PROMOTION hierarchy'); cwm_olap_level.set_display_name(USER, 'PROMOTIONS_DIM', 'SUBCATEGORY', 'Promotions Sub-categories'); cwm_olap_level.set_description(USER, 'PROMOTIONS_DIM', 'SUBCATEGORY', 'Sub-category level regarding the standard PROMOTION hierarchy'); cwm_olap_level.set_display_name(USER, 'PROMOTIONS_DIM', 'CATEGORY', 'Promotions Categories'); cwm_olap_level.set_description(USER, 'PROMOTIONS_DIM', 'CATEGORY', 'Category level of the standard PROMOTION hierarchy'); cwm_olap_level.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO_TOTAL', 'Promotions Total'); cwm_olap_level.set_description(USER, 'PROMOTIONS_DIM', 'PROMO_TOTAL', 'Promotions Total fork the standard PROMOTION hierarchy'); dbms_output.put_line ('Hierarchy - video name and description'); cwm_olap_hierarchy.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO_ROLLUP', 'Standard Promotions'); cwm_olap_hierarchy.set_description(USER, 'PROMOTIONS_DIM', 'PROMO_ROLLUP', 'Standard Promotions hierarchy'); dbms_output.put_line(' - default calculation hierarchy'); cwm_olap_cube.set_default_calc_hierarchy(USER,'SALES_CUBE', 'PROMO_ROLLUP', USER, 'PROMOTIONS_DIM', 'PROMOTIONS_DIM'); dbms_output.put_line(' - custom display hierarchy'); cwm_olap_dimension.set_default_display_hierarchy(USER, 'PROMOTIONS_DIM', 'PROMO_ ROLLUP'); dbms_output.put_line ('Level Attributes - name, display name, description'); -- Level: PROMO cwm_olap_level_attribute.set_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_NAME', 'PROMO_NAME'); cwm_olap_level_attribute.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_NAME', 'Promotion Name(s)'); cwm_olap_level_attribute.set_description(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_NAME', 'Names for which Promotions in the Standard Promotions hierarchy'); cwm_olap_level_attribute.set_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_COST', 'PROMO_COST'); cwm_olap_level_attribute.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_COST', 'Promotion costs'); cwm_olap_level_attribute.set_description(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_COST', 'Promotion costs'); cwm_olap_level_attribute.set_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_BEGIN_ DATE', 'PROMO_BEGIN_DATE'); cwm_olap_level_attribute.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_BEGIN_DATE', 'Begin date'); cwm_olap_level_attribute.set_description(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_BEGIN_DATE', 'Promotion Begin Date'); cwm_olap_level_attribute.set_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_END_ DATE', 'PROMO_END_DATE'); cwm_olap_level_attribute.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_END_DATE', 'End date'); cwm_olap_level_attribute.set_description(USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_END_DATE', 'Promotion End Date'); -- Level: SUBCATEGORY cwm_olap_level_attribute.set_name(USER, 'PROMOTIONS_DIM', 'SUBCATEGORY', 'PROMO_ SUBCATEGORY', 'PROMO_SUBCATEGORY'); cwm_olap_level_attribute.set_display_name(USER, 'PROMOTIONS_DIM', 'SUBCATEGORY', 'PROMO_SUBCATEGORY', 'Sub-Category'); cwm_olap_level_attribute.set_description(USER, 'PROMOTIONS_DIM', 'SUBCATEGORY', 'PROMO_SUBCATEGORY', 'Promotion Sub-Category'); -- Level: CATEGORY cwm_olap_level_attribute.set_name(USER, 'PROMOTIONS_DIM', 'CATEGORY', 'PROMO_ CATEGORY', 'PROMO_CATEGORY'); cwm_olap_level_attribute.set_display_name(USER, 'PROMOTIONS_DIM', 'CATEGORY', 'PROMO_CATEGORY', 'Category'); cwm_olap_level_attribute.set_description(USER, 'PROMOTIONS_DIM', 'CATEGORY', 'PROMO_CATEGORY', 'Promotion Category'); -- Set: PROMOTIONS TOTAL cwm_olap_level_attribute.set_name(USER, 'PROMOTIONS_DIM', 'PROMO_TOTAL', 'PROMO_ TOTAL', 'PROMO_TOTAL'); cwm_olap_level_attribute.set_display_name(USER, 'PROMOTIONS_DIM', 'PROMO_TOTAL', 'PROMO_TOTAL', 'Promotions Total'); cwm_olap_level_attribute.set_description(USER, 'PROMOTIONS_DIM', 'PROMO_TOTAL', 'PROMO_TOTAL', 'Promotions Total'); dbms_output.put_line ('Drop dimension attributes earlier to re-creation'); begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'PROMOTIONS_DIM', 'Long Description'); dbms_output.put_line(' - Long Description dropped'); exception when cwm_exceptions.attribute_not_found then null; end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'PROMOTIONS_DIM', 'Short Description' ); dbms_output.put_line(' - Short Description dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line('No attribute to drop'); end; dbms_output.put_line ('Create dimension attributes and add their level attributes'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'PROMOTIONS_DIM', 'Long Description', 'Long Description of Promotions', 'Long Description starting Promotions'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PROMOTIONS_DIM', 'Long Description', 'PROMO', 'PROMO_NAME'); dbms_output.put_line(' - Long Description created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'PROMOTIONS_DIM', 'Short Description', 'ShortDescription of Promotions', 'Short Description of Promotions'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'PROMOTIONS_DIM', 'Short Description', 'PROMO', 'PROMO_NAME'); dbms_output.put_line(' - Short Feature created'); dbms_output.put_line ('Classify entity descriptor use'); begin SELECT descriptor_id INTO long_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Long Description' AND descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(long_desc_id, DIMENSION_ATTRIBUTE_TYPE, USERS, 'PROMOTIONS_DIM', 'Long Description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USERS, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_NAME'); exception when cwm_exceptions.element_already_exists then null; end; end; dbms_output.put_line(' - Length Description'); end; begin SELECT descriptor_id INTO short_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Short Description' AND descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(short_desc_id, DIMENSION_ATTRIBUTE_TYPE, USER, 'PROMOTIONS_DIM', 'Short Description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'PROMOTIONS_DIM', 'PROMO', 'PROMO_NAME'); exception while cwm_exceptions.element_already_exists then null; end; end; dbms_output.put_line(' - Short Description'); end; -- ------------------- Processing the CHANNELS Dimension -------------- dbms_output.put_line('-'); dbms_output.put_line ('<<<<< CHANNELS DIMENSION >>>>>'); dbms_output.put_line ('Dimension - display name, description and plurals name'); CWM_OLAP_DIMENSION.set_display_name(USER, 'CHANNELS_DIM', 'Channel'); CWM_OLAP_DIMENSION.set_description(USER, 'CHANNELS_DIM', 'Channel Values'); CWM_OLAP_DIMENSION.set_plural_name(USER, 'CHANNELS_DIM', 'Channels'); dbms_output.put_line ('Level - display company furthermore description'); cwm_olap_level.set_display_name(USER, 'CHANNELS_DIM', 'CHANNEL', 'Channel'); cwm_olap_level.set_description(USER, 'CHANNELS_DIM', 'CHANNEL', 'Channel liquid of that standard hierarchy'); cwm_olap_level.set_display_name(USER, 'CHANNELS_DIM', 'CHANNEL_CLASS', 'Channel Class'); cwm_olap_level.set_description(USER, 'CHANNELS_DIM', 'CHANNEL_CLASS', 'Channel Class level of the standard hierarchy'); cwm_olap_level.set_display_name(USER, 'CHANNELS_DIM', 'CHANNEL_TOTAL', 'Channel Total'); cwm_olap_level.set_description(USER, 'CHANNELS_DIM', 'CHANNEL_TOTAL', 'Channel Total for the standard hierarchy'); dbms_output.put_line ('Hierarchy - display name and description'); cwm_olap_hierarchy.set_display_name(USER, 'CHANNELS_DIM', 'CHANNEL_ROLLUP', 'Standard Channels'); cwm_olap_hierarchy.set_description(USER, 'CHANNELS_DIM', 'CHANNEL_ROLLUP', 'Standard Channels hierarchy'); dbms_output.put_line(' - default calculation hierarchy'); cwm_olap_cube.set_default_calc_hierarchy(USER,'SALES_CUBE', 'CHANNEL_ROLLUP', USER, 'CHANNELS_DIM', 'CHANNELS_DIM'); dbms_output.put_line(' - standard display hierarchy'); cwm_olap_dimension.set_default_display_hierarchy(USER, 'CHANNELS_DIM', 'CHANNEL_ ROLLUP'); dbms_output.put_line ('Level Attributes - name, display name, description'); -- Level: CHANNEL cwm_olap_level_attribute.set_name(USER, 'CHANNELS_DIM', 'CHANNEL', 'CHANNEL_ DESC', 'CHANNEL_DESC'); cwm_olap_level_attribute.set_display_name(USER, 'CHANNELS_DIM', 'CHANNEL', 'CHANNEL_DESC', 'Channel'); cwm_olap_level_attribute.set_description(USER, 'CHANNELS_DIM', 'CHANNEL', 'CHANNEL_DESC', 'Channel Description'); -- Level: CHANNEL CLASS cwm_olap_level_attribute.set_name(USER, 'CHANNELS_DIM', 'CHANNEL_CLASS', 'CHANNEL_CLASS', 'CHANNEL_CLASS'); cwm_olap_level_attribute.set_display_name(USER, 'CHANNELS_DIM', 'CHANNEL_CLASS', 'CHANNEL_CLASS', 'Channel Class'); cwm_olap_level_attribute.set_description(USER, 'CHANNELS_DIM', 'CHANNEL_CLASS', 'CHANNEL_CLASS', 'Channel Class Identifier'); -- Level: CHANNEL TOTAL cwm_olap_level_attribute.set_name(USER, 'CHANNELS_DIM', 'CHANNEL_TOTAL', 'CHANNEL_TOTAL', 'CHANNEL_TOTAL'); cwm_olap_level_attribute.set_display_name(USER, 'CHANNELS_DIM', 'CHANNEL_TOTAL', 'CHANNEL_TOTAL', 'Channel Total'); cwm_olap_level_attribute.set_description(USER, 'CHANNELS_DIM', 'CHANNEL_TOTAL', 'CHANNEL_TOTAL', 'Channel Total'); dbms_output.put_line ('Drop scale eigenschaften prior for re-creation'); begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CHANNELS_DIM', 'Long Description'); dbms_output.put_line(' - Long Description dropped'); exception when cwm_exceptions.attribute_not_found then null; end; begin cwm_olap_dim_attribute.drop_dimension_attribute(USER, 'CHANNELS_DIM', 'Short Description' ); dbms_output.put_line(' - Short Description dropped'); exception when cwm_exceptions.attribute_not_found then dbms_output.put_line('No attribute to drop'); end; dbms_output.put_line ('Create dimension attributes and add their level attributes'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CHANNELS_DIM', 'Long Description', 'Long Description of Channels', 'Long Description of Channels'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CHANNELS_DIM', 'Long Description', 'CHANNEL', 'CHANNEL_DESC'); dbms_output.put_line(' - Extended Description created'); CWM_OLAP_DIM_ATTRIBUTE.create_dimension_attribute(USER, 'CHANNELS_DIM', 'Short Description', 'Short Features starting Channels', 'Short Description of Channels'); CWM_OLAP_DIM_ATTRIBUTE.add_level_attribute(USER, 'CHANNELS_DIM', 'Short Description', 'CHANNEL', 'CHANNEL_DESC'); dbms_output.put_line(' - Briefly Description created'); dbms_output.put_line ('Classify entity identifier use'); begin SELECT descriptor_id INTO long_desc_id FROM all_olap_descriptors WHERE descriptor_value = 'Long Description' ALSO descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(long_desc_id, DIMENSION_ATTRIBUTE_TYPE, ADDICT, 'CHANNELS_DIM', 'Long Description'); exception when cwm_exceptions.element_already_exists then null; end; begin cwm_classify.add_entity_descriptor_use(long_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CHANNELS_DIM', 'CHANNEL', 'CHANNEL_DESC'); exception when cwm_exceptions.element_already_exists then null; end; end; dbms_output.put_line(' - Long Description'); end; begin CHOOSE descriptor_id INTO short_desc_id FROM all_olap_descriptors LOCUS descriptor_value = 'Short Description' AND descriptor_type = 'Dimensional Attribute Descriptor'; begin begin cwm_classify.add_entity_descriptor_use(short_desc_id, DIMENSION_ATTRIBUTE_TYPE, ADDICT, 'CHANNELS_DIM', 'Short Description'); exception whereas cwm_exceptions.element_already_exists then null; finalize; begin cwm_classify.add_entity_descriptor_use(short_desc_id, LEVEL_ATTRIBUTE_TYPE, USER, 'CHANNELS_DIM', 'CHANNEL', 'CHANNEL_DESC'); exception wenn cwm_exceptions.element_already_exists then null; end; end; dbms_output.put_line(' - Short Description'); end; -- ------------------- Final Processing ------------------------------- dbms_output.put_line('-'); dbms_output.put_line ('<<<<< FINAL PROCESSING >>>>>'); commit; dbms_output.put_line (' - Variations have been committed'); exception when others then cwm_utility.dump_error; errtxt := cwm_utility.get_last_error_description; dbms_output.put_line('ERROR: ' || errtxt); rollback; raise; end; . / COMMIT; -- ------------------- Information --------------------------- @?/demo/schema/sales_history/sh_analz.sql
Rem Rem $Header: sh_olp_d.sql 17-sep-2001.15:57:34 ahunold Exp $ Rem Removes sh_olp_d.sql Rem Rem Copyright (c) 2001, Oracle Legal. All rights reserved. Rem Rem NAME Rem sh_olp_d.sql - Dropped columns used by OLAP Server Rem Rem DESCRIPTION Rem SH is the Sales History shape of the Oracle 9i Sample Rem Schemas Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Removed ahunold 09/17/01 - sh_analz.sql Rem ahunold 04/23/01 - duplicate lines Rem ahunold 04/05/01 - dimension names Rem ahunold 03/05/01 - external table, no DROPs Rem ahunold 02/07/01 - CMWLite Rem ahunold 02/01/01 - Amalgamated ahunold_two_facts Removes hbaer 01/29/01 - Created Rem ALTER TAB products DROP PILLAR prod_total; ALTER TABLE customers DROP COLUMN cust_total; ALTER CHART promotions DROP COLUMN promo_total; ALTER TABLE channels DROP COLUMN channel_total; RESET TABLE countries DROP COLUMN country_total; COMMIT; REM redefinition regarding original dimensions DROP DIMENSION times_dim; DROP EXTENT customers_dim; DROP DIMENSION products_dim; DROP DIMENSION promotions_dim; CAST DIMENSION channels_dim; @@sh_hiera @@sh_analz
|
 Copyright © 2002 Oracle Corporation. View Rights Reserved. |
|
