DROP TABLE col_dt_tab PURGE;
DROP TYPE my_arr;
DROP TYPE my_obj_list;
DROP TYPE my_obj;

-- types
CREATE OR REPLACE TYPE my_obj AS OBJECT (ID INTEGER, name VARCHAR2(50))
/
CREATE OR REPLACE TYPE my_obj_list AS table OF my_obj
/
CREATE OR REPLACE TYPE my_arr AS TABLE OF VARCHAR2(500)
/

-- table (with all sorts of column datatypes)
CREATE TABLE col_dt_tab (
 int_col                       INTEGER CONSTRAINT dt_tab_int_col_nn NOT NULL
,num_col                       NUMBER(12,4)
,char_col                      CHAR(1) DEFAULT 'Y'
,str_btye_col                  VARCHAR2(30 BYTE)
,str_char_col                  VARCHAR2(30 CHAR)
-- have to set master key to use encrypted column
-- ,str_char_col                  VARCHAR2(30 CHAR) ENCRYPT USING 'AES256' SALT
,intl_str_col                  NVARCHAR2(200)
,date_col                      DATE
,ts_col                        TIMESTAMP(6)
,ts_tz_col                     TIMESTAMP WITH TIME ZONE
,ts_ltz_col                    TIMESTAMP WITH LOCAL TIME ZONE
,bfile_col                     BFILE
,blob_col                      BLOB
,clob_col                      CLOB
,nclob_col                     NCLOB
,xml_col                       XMLTYPE
,rowid_col                     ROWID
,urowid_cold                   UROWID
,str_ntab_col                  my_arr 
,obj_ntab_col                  my_obj_list
)
NESTED TABLE str_ntab_col STORE AS my_arr_ntab
NESTED TABLE obj_ntab_col STORE AS my_obj_list_ntab
PCTFREE 1
PCTUSED 99
/

-- global temporary table
DROP TABLE load_gtt PURGE;
CREATE GLOBAL TEMPORARY TABLE load_gtt (
  load_src_id INTEGER
 ,load_txt    VARCHAR2(4000)
)
ON COMMIT PRESERVE ROWS
/

-- sequence
DROP SEQUENCE load_source_sq;
CREATE SEQUENCE load_source_sq 
  NOCACHE
/

-- IOT (w/overflow and secondary index)
DROP TABLE load_source PURGE;
CREATE TABLE load_source(
  load_src_id   INTEGER
 ,load_src_cd   VARCHAR2(5)
 ,load_src_nm   VARCHAR2(30)
 ,load_src_notes   VARCHAR2(4000)
 ,CONSTRAINT load_src_pk PRIMARY KEY (load_src_id)
 ,CONSTRAINT load_src_uk UNIQUE (load_src_cd)
)
ORGANIZATION INDEX
INCLUDING load_src_nm
PCTTHRESHOLD 50
OVERFLOW STORAGE (INITIAL 1M NEXT 1M)
/

-- directory 
DROP DIRECTORY testu_dir;
CREATE OR REPLACE DIRECTORY testu_dir AS '/opt/mssw_logs';
-- external
CREATE TABLE department (
   dept_num   NUMBER(6),
   dept_nm    VARCHAR2(20),
   dept_lcn   VARCHAR2(25) 
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY testu_dir
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  BADFILE 'dept.bad'
  DISCARDFILE 'dept.dis'
  LOGFILE 'dept.log'
  SKIP 20
  FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
  (
   deptno     INTEGER EXTERNAL(6),
   dname      CHAR(20),
   loc        CHAR(25)
  )
 )
 LOCATION ('dept.ctl')
)
REJECT LIMIT UNLIMITED;

-- partitioned (enable row movement), range, hash, list, compress
DROP TABLE sales PURGE;
DROP TABLE associate PURGE;
CREATE TABLE associate
(
   assoc_id  INTEGER CONSTRAINT assoc_id_nn NOT NULL
  ,assoc_nm  VARCHAR2(100) CONSTRAINT assoc_nm_nn NOT NULL
  ,assoc_mgr INTEGER
  ,CONSTRAINT associate_pk PRIMARY KEY (assoc_id)
  ,CONSTRAINT assoc_mgr_fk FOREIGN KEY (assoc_mgr) REFERENCES associate (assoc_id)
)
TABLESPACE users;

CREATE TABLE sales
(
   order_id       INTEGER     CONSTRAINT sales_order_id_nn NOT NULL
  ,sale_dt        DATE        CONSTRAINT sales_dt_nn NOT NULL
  ,sale_yr        VARCHAR2(4) CONSTRAINT sales_yr_nn NOT NULL
  ,sale_qtr       INTEGER     CONSTRAINT sales_qtr_nn NOT NULL
  ,sale_month     INTEGER     CONSTRAINT sales_month_nn NOt NULL
  ,sale_day_of_mo INTEGER     CONSTRAINT sales_day_of_mo_nn NOT NULL
  ,assoc_id       INTEGER     
  ,customer_id    INTEGER     CONSTRAINT sales_customer_id_nn NOT NULL
  ,total_prods    INTEGER
  ,total_paid     NUMBER(10,2)
)
TABLESPACE users
PARTITION BY RANGE (sale_dt)
 (PARTITION SALES_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
  PARTITION SALES_2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
  PARTITION SALES_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')))
/

ALTER TABLE sales
  ADD CONSTRAINT sales_assoc_id_fk FOREIGN KEY (assoc_id) REFERENCES ASSOCIATE (assoc_id)
/

CREATE UNIQUE INDEX sales_uk ON sales (order_id, sale_dt) LOCAL
/

-- list partitioned
DROP TABLE sales2 PURGE;
CREATE TABLE sales2
(
   order_id       INTEGER      CONSTRAINT sales2_order_id_nn NOT NULL
  ,sales_rgn      VARCHAR2(20)  DEFAULT 'HQ' CONSTRAINT sales2_sales_rgn_nn NOT NULL
  ,sale_dt        DATE         CONSTRAINT sales2_dt_nn NOT NULL
  ,sale_yr        VARCHAR2(4)  CONSTRAINT sales2_yr_nn NOT NULL
  ,sale_qtr       INTEGER      CONSTRAINT sales2_qtr_nn NOT NULL
  ,sale_month     INTEGER      CONSTRAINT sales2_month_nn NOt NULL
  ,sale_day_of_mo INTEGER      CONSTRAINT sales2_day_of_mo_nn NOT NULL
  ,assoc_id       INTEGER     
  ,customer_id    INTEGER      CONSTRAINT sales2_customerid_nn NOT NULL
  ,total_prods    INTEGER
  ,total_paid     NUMBER(10,2)
)
TABLESPACE users
PARTITION BY RANGE (sale_dt)
SUBPARTITION BY HASH (order_id)
 (PARTITION sales_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
    SUBPARTITIONS 4,
  PARTITION sales_2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY'))
    SUBPARTITIONS 4,
  PARTITION sales_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY'))
    SUBPARTITIONS 4
 )
/

-- list partitioned
DROP TABLE sales3 PURGE;
CREATE TABLE sales3
(
   order_id       INTEGER      CONSTRAINT sale_order_id_nn NOT NULL
  ,sales_rgn      VARCHAR2(20)  DEFAULT 'HQ' CONSTRAINT salessales_rgn_nn NOT NULL
  ,sale_dt        DATE         CONSTRAINT sale_dt_nn NOT NULL
  ,sale_yr        VARCHAR2(4)  CONSTRAINT sale_yr_nn NOT NULL
  ,sale_qtr       INTEGER      CONSTRAINT sale_qtr_nn NOT NULL
  ,sale_month     INTEGER      CONSTRAINT sale_month_nn NOt NULL
  ,sale_day_of_mo INTEGER      CONSTRAINT sale_day_of_mo_nn NOT NULL
  ,assoc_id       INTEGER     
  ,customer_id    INTEGER      CONSTRAINT sale_customerid_nn NOT NULL
  ,total_prods    INTEGER
  ,total_paid     NUMBER(10,2)
)
TABLESPACE users
PARTITION BY LIST (sales_rgn) (
  PARTITION rgn1 VALUES ('Northwest'),
  PARTITION rgn2 VALUES ('North Central'),
  PARTITION rgn3 VALUES ('South Central'),
  PARTITION rgn4 VALUES ('Southwest'),
  PARTITION rgn5 VALUES ('Northeast'),
  PARTITION rgn6 VALUES ('Southeast')
)
/

-- interval partitioned
/* Testing took place on 10g, so interval paritioning could not be evaluated */

-- view
CREATE OR REPLACE FORCE VIEW load_src_vw
AS
SELECT
  load_src_id   
 ,load_src_cd   
 ,load_src_nm   
FROM load_source;

COMMENT ON TABLE load_src_vw IS 'Load Source View: Test view. Testing comments on view.';

-- materialized view
DROP MATERIALIZED VIEW load_src_note_len;

CREATE MATERIALIZED VIEW load_src_note_len 
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT LENGTH(load_src_notes) note_len, COUNT(*) len_count FROM load_source GROUP BY LENGTH(load_src_notes)
ORDER BY 1 DESC;

-------------------------------------------------------------------------------
-- cluster
-------------------------------------------------------------------------------
DROP CLUSTER states;
CREATE CLUSTER states
(
 state_cd                      VARCHAR2(2) SORT
)
/
CREATE INDEX states_clust_ix ON CLUSTER states;
CREATE TABLE state_mstr (
 state_cd              VARCHAR2(2) CONSTRAINT sm_state_cd_nn NOT NULL
,state_nm              VARCHAR2(30) CONSTRAINT sm_state_nm_nn NOT NULL
)
CLUSTER states (state_cd)
/
insert into state_mstr (state_nm, state_cd) values ('ALABAMA','AL');
insert into state_mstr (state_nm, state_cd) values ('ALASKA','AK');
insert into state_mstr (state_nm, state_cd) values ('AMERICAN SAMOA','AS');
insert into state_mstr (state_nm, state_cd) values ('ARIZONA','AZ');
insert into state_mstr (state_nm, state_cd) values ('ARKANSAS','AR');
insert into state_mstr (state_nm, state_cd) values ('CALIFORNIA','CA');
insert into state_mstr (state_nm, state_cd) values ('COLORADO','CO');
insert into state_mstr (state_nm, state_cd) values ('CONNECTICUT','CT');
insert into state_mstr (state_nm, state_cd) values ('DELAWARE','DE');
insert into state_mstr (state_nm, state_cd) values ('DISTRICT OF COLUMBIA','DC');
insert into state_mstr (state_nm, state_cd) values ('FEDERATED STATES OF MICRONESIA','FM');
insert into state_mstr (state_nm, state_cd) values ('FLORIDA','FL');
insert into state_mstr (state_nm, state_cd) values ('GEORGIA','GA');
insert into state_mstr (state_nm, state_cd) values ('GUAM','GU');
insert into state_mstr (state_nm, state_cd) values ('HAWAII','HI');
insert into state_mstr (state_nm, state_cd) values ('IDAHO','ID');
insert into state_mstr (state_nm, state_cd) values ('ILLINOIS','IL');
insert into state_mstr (state_nm, state_cd) values ('INDIANA','IN');
insert into state_mstr (state_nm, state_cd) values ('IOWA','IA');
insert into state_mstr (state_nm, state_cd) values ('KANSAS','KS');
insert into state_mstr (state_nm, state_cd) values ('KENTUCKY','KY');
insert into state_mstr (state_nm, state_cd) values ('LOUISIANA','LA');
insert into state_mstr (state_nm, state_cd) values ('MAINE','ME');
insert into state_mstr (state_nm, state_cd) values ('MARSHALL ISLANDS','MH');
insert into state_mstr (state_nm, state_cd) values ('MARYLAND','MD');
insert into state_mstr (state_nm, state_cd) values ('MASSACHUSETTS','MA');
insert into state_mstr (state_nm, state_cd) values ('MICHIGAN','MI');
insert into state_mstr (state_nm, state_cd) values ('MINNESOTA','MN');
insert into state_mstr (state_nm, state_cd) values ('MISSISSIPPI','MS');
insert into state_mstr (state_nm, state_cd) values ('MISSOURI','MO');
insert into state_mstr (state_nm, state_cd) values ('MONTANA','MT');
insert into state_mstr (state_nm, state_cd) values ('NEBRASKA','NE');
insert into state_mstr (state_nm, state_cd) values ('NEVADA','NV');
insert into state_mstr (state_nm, state_cd) values ('NEW HAMPSHIRE','NH');
insert into state_mstr (state_nm, state_cd) values ('NEW JERSEY','NJ');
insert into state_mstr (state_nm, state_cd) values ('NEW MEXICO','NM');
insert into state_mstr (state_nm, state_cd) values ('NEW YORK','NY');
insert into state_mstr (state_nm, state_cd) values ('NORTH CAROLINA','NC');
insert into state_mstr (state_nm, state_cd) values ('NORTH DAKOTA','ND');
insert into state_mstr (state_nm, state_cd) values ('NORTHERN MARIANA ISLANDS','MP');
insert into state_mstr (state_nm, state_cd) values ('OHIO','OH');
insert into state_mstr (state_nm, state_cd) values ('OKLAHOMA','OK');
insert into state_mstr (state_nm, state_cd) values ('OREGON','OR');
insert into state_mstr (state_nm, state_cd) values ('PALAU','PW');
insert into state_mstr (state_nm, state_cd) values ('PENNSYLVANIA','PA');
insert into state_mstr (state_nm, state_cd) values ('PUERTO RICO','PR');
insert into state_mstr (state_nm, state_cd) values ('RHODE ISLAND','RI');
insert into state_mstr (state_nm, state_cd) values ('SOUTH CAROLINA','SC');
insert into state_mstr (state_nm, state_cd) values ('SOUTH DAKOTA','SD');
insert into state_mstr (state_nm, state_cd) values ('TENNESSEE','TN');
insert into state_mstr (state_nm, state_cd) values ('TEXAS','TX');
insert into state_mstr (state_nm, state_cd) values ('UTAH','UT');
insert into state_mstr (state_nm, state_cd) values ('VERMONT','VT');
insert into state_mstr (state_nm, state_cd) values ('VIRGIN ISLANDS','VI');
insert into state_mstr (state_nm, state_cd) values ('VIRGINIA','VA');
insert into state_mstr (state_nm, state_cd) values ('WASHINGTON','WA');
insert into state_mstr (state_nm, state_cd) values ('WEST VIRGINIA','WV');
insert into state_mstr (state_nm, state_cd) values ('WISCONSIN','WI');
insert into state_mstr (state_nm, state_cd) values ('WYOMING','WY');
COMMIT;

-------------------------------------------------------------------------------
-- queue
-------------------------------------------------------------------------------
SET SERVEROUTPUT ON
DECLARE
   l_queue_name VARCHAR2(30) := 'TEST_Q';
   l_queue_table_name VARCHAR2(30) := 'TEST_SQT';
   lx_queue_is_not EXCEPTION;
   lx_queue_running EXCEPTION;
   lx_queue_tab_is_not EXCEPTION;
   PRAGMA EXCEPTION_INIT(lx_queue_is_not,-24010);
   PRAGMA EXCEPTION_INIT(lx_queue_running,-24011);
   PRAGMA EXCEPTION_INIT(lx_queue_tab_is_not,-24002);
BEGIN
   BEGIN
      dbms_aqadm.drop_queue(queue_name => l_queue_name);
   EXCEPTION
      WHEN lx_queue_is_not THEN
         dbms_output.put_line('Queue does not yet exist. Check spelling to be sure.');
      WHEN lx_queue_running THEN
         dbms_output.put_line('Stopping '||l_queue_name);
         dbms_aqadm.stop_queue(queue_name => l_queue_name);
         dbms_output.put_line('Dropping '||l_queue_name);
         dbms_aqadm.drop_queue(queue_name => l_queue_name);
   END;

   dbms_output.put_line('Creating '||l_queue_name);
   dbms_aqadm.create_queue(queue_name => l_queue_name,
                          queue_table => l_queue_table_name,
                          max_retries => 4,
                          retry_delay => 3600, -- seconds
                          comment => 'Test Queue: Enqueued manually. Messages remain indefinitely. 
                          Dequeue failures can be fixed for 4 hours, after which they go to exception.');

   dbms_output.put_line('Starting '||l_queue_name);
   dbms_aqadm.start_queue(queue_name=> l_queue_name);

   -- Start the default exception queue as well so we can dequeue from it.
   dbms_output.put_line('Starting AQ$_'||l_queue_table_name||'_E exception Q');
   dbms_aqadm.start_queue(queue_name => 'AQ$_'||l_queue_table_name||'_E', enqueue => FALSE, dequeue => TRUE);

--   dbms_output.put_line('Granting dequeue privs');
--   dbms_aqadm.grant_queue_privilege(privilege    => 'DEQUEUE' -- can be ENQUEUE, DEQUEUE, or ALL
--                                   ,queue_name   => l_queue_name
--                                   ,grantee      => 'TEST_CLIENT'
--                                   ,grant_option => FALSE);
--   dbms_output.put_line('Granting enqueue privs');
--   dbms_aqadm.grant_queue_privilege(privilege    => 'ENQUEUE' -- can be ENQUEUE, DEQUEUE, or ALL
--                                   ,queue_name   => l_queue_name
--                                   ,grantee      => 'TEST_WEBSVC'
--                                   ,grant_option => FALSE);
END;
/

--GRANT SELECT ON test_sqt TO test_full;
--GRANT SELECT ON aq$test_sqtTO test_full;

-------------------------------------------------------------------------------
-- schedule and job
-------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE test_proc(i_str IN VARCHAR2)
AS
BEGIN
   dbms_output.put_line('Parameter Value ['||i_str||']');
END test_proc;
/

--@ignore_error
DECLARE
   lx_job_not_there EXCEPTION;
   PRAGMA EXCEPTION_INIT(lx_job_not_there,-27475);
BEGIN
   dbms_scheduler.drop_job(job_name => 'TEST_SCHED_JOB');
EXCEPTION
   WHEN lx_job_not_there THEN NULL;
END;
/

--@ignore_error
DECLARE
   lx_sched_not_there EXCEPTION;
   PRAGMA EXCEPTION_INIT(lx_sched_not_there,-27476);
BEGIN
   dbms_scheduler.drop_schedule(schedule_name => 'SAT_2200', force => TRUE);
EXCEPTION
   WHEN lx_sched_not_there THEN NULL;
END;
/

-- Create specific schedule for test_sched_job
BEGIN
   dbms_scheduler.create_schedule(schedule_name   => 'SAT_2200'
                                 ,start_date      => SYSDATE
                                 ,repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=22'
                                 ,comments        => 'This schedule will run on Saturdays at 10:00 PM.');
END;
/


BEGIN
   dbms_scheduler.create_job(job_name      => 'TEST_SCHED_JOB'
                            ,job_type      => 'STORED_PROCEDURE'
                            ,job_action    => 'test_proc'
                            ,schedule_name => 'SAT_2200'
                            ,enabled       => TRUE
                            ,auto_drop     => FALSE
                            ,comments      => 'Job to test calling simple proc from within a scheduled job.');
END;
/

--BEGIN
--   dbms_scheduler.set_attribute(NAME      => 'TEST_SCHED_JOB'
--                                   ,attribute => 'RAISE_EVENTS'
--                                   ,VALUE     => sys.dbms_scheduler.job_failed +
--                                                 sys.dbms_scheduler.job_broken +
--                                                 sys.dbms_scheduler.job_stopped +
--                                                 sys.dbms_scheduler.job_disabled);
--END;
--/
