/*
|| Script: CreateFBDATAbles.sql
||
|| Purpose: Creates database objects for demonstration of Oracle 11g 
|| Flashback Data Archive (FBDA) techniques
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| Flashback Data Archive, and they should be carefully proofread before
|| executing it against any existing Oracle database to avoid potential damage!
*/

-----
-- Create a new table (HR.APPLICANTS)
-----
DROP TABLE hr.applicants CASCADE CONSTRAINTS PURGE;
CREATE TABLE hr.applicants
(
  applicant_id     NUMBER(5)    NOT NULL,
  last_name        VARCHAR2(24) NOT NULL,
  first_name       VARCHAR2(24) NOT NULL,
  middle_initial   VARCHAR2(1),
  gender           VARCHAR2(1),
  application_date DATE         NOT NULL,
  job_desired      VARCHAR2(10) NOT NULL,
  salary_desired   NUMBER(10,2) NOT NULL,
  added_on         DATE         DEFAULT SYSDATE NOT NULL,
  added_by         VARCHAR2(12) NOT NULL,
  changed_on       DATE DEFAULT SYSDATE NOT NULL,
  changed_by       VARCHAR2(12) NOT NULL
)
TABLESPACE example;

-- Comments
COMMENT ON TABLE hr.applicants
    IS 'Controls domain of Applicants, i.e. persons who have applied for an employment opportunity';
COMMENT ON COLUMN hr.applicants.applicant_id
    IS 'Unique identifier for an Applicant';
COMMENT ON COLUMN hr.applicants.last_name
    IS 'Applicant Last Name';
COMMENT ON COLUMN hr.applicants.first_name
    IS 'Applicant First Name';
COMMENT ON COLUMN hr.applicants.middle_initial
    IS 'Applicant Middle Initial';
COMMENT ON COLUMN hr.applicants.gender
    IS 'Applicant Gender';
COMMENT ON COLUMN hr.applicants.application_date
    IS 'Application Date';
COMMENT ON COLUMN hr.applicants.job_desired
    IS 'Job Applied For';
COMMENT ON COLUMN hr.applicants.salary_desired
    IS 'Desired Salary';
COMMENT ON COLUMN hr.applicants.added_on
    IS 'Added On';
COMMENT ON COLUMN hr.applicants.added_by
    IS 'Added By';
COMMENT ON COLUMN hr.applicants.changed_on
    IS 'Last Updated On';
COMMENT ON COLUMN hr.applicants.changed_by
    IS 'Last Updated By';

-- Create indexes and constraints
CREATE UNIQUE INDEX hr.applicants_pk_idx
    ON hr.applicants(applicant_id)
    TABLESPACE example;

ALTER TABLE hr.applicants
    ADD CONSTRAINT applicants_pk
    PRIMARY KEY (applicant_id);

CREATE INDEX hr.applicants_last_name_idx
    ON hr.applicants (last_name)
    TABLESPACE example;

-- Create/Recreate check constraints
ALTER TABLE hr.applicants
    ADD CONSTRAINT applicant_gender_ck
    CHECK ((gender IN('M', 'F') or gender IS NULL));

-- Create sequence
DROP SEQUENCE hr.seq_applicants;
CREATE SEQUENCE hr.seq_applicants
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 3
    NOORDER;

-- Create INSERT/UPDATE row-level trigger
CREATE OR REPLACE TRIGGER hr.tr_briu_applicants
    BEFORE INSERT OR UPDATE ON hr.applicants
    FOR EACH ROW
DECLARE
    entry_id NUMBER := 0;
BEGIN

    IF INSERTING THEN
        BEGIN

            SELECT
                hr.seq_applicants.NEXTVAL
                INTO entry_id
                FROM DUAL;

            :new.applicant_id := entry_id;
            :new.added_on := SYSDATE;
            :new.added_by := DBMS_STANDARD.LOGIN_USER;
            :new.changed_on := SYSDATE;
            :new.changed_by := DBMS_STANDARD.LOGIN_USER;

        END;

    ELSIF UPDATING THEN
        BEGIN
            :new.changed_on := SYSDATE;
            :new.changed_by := DBMS_STANDARD.LOGIN_USER;
        END;

    END IF;

END TR_BRIU_APPLICANTS;
/


-----
-- Create a first set of applicants
-----
INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Aniston', 'Seth', 'J', 'M', TO_DATE('01-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 88017.94);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Niven', 'Ray', NULL, 'M', TO_DATE('01-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 82553.39);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Brown', 'Jackson', 'R', 'M', TO_DATE('02-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 70113.04);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Murdock', 'Charlton', 'H', 'M', TO_DATE('02-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 70389.16);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Bedelia', 'Colin', 'F', 'M', TO_DATE('02-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 38720.86);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Chandler', 'Gino', NULL, 'M', TO_DATE('02-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 55511.77);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Lerner', 'Hex', 'X', 'M', TO_DATE('03-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 80587.46);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Robinson', 'Mekhi', 'A', 'M', TO_DATE('03-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 49516.37);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Chestnut', 'Denis', 'V', 'M', TO_DATE('04-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 73042.53);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Costa', 'Doug', 'A', 'M', TO_DATE('04-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 65403.50);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Bello', 'Lucy', NULL, 'F', TO_DATE('05-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 78432.05);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Playboys', 'Edward', 'H', 'M', TO_DATE('05-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 54464.91);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Spader', 'Leonardo', 'J', 'M', TO_DATE('05-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 49207.14);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Jovovich', 'Edwin', 'Y', 'M', TO_DATE('05-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 56825.48);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Graham', 'Ray', 'A', 'M', TO_DATE('08-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 69169.14);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Barkin', 'Suzanne', 'P', 'F', TO_DATE('08-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 49641.49);

COMMIT;

-----
-- Create a second set of applicants. Because the first set has been COMMITted already,
-- these entries will have a different SCN than the first set.
-----

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Shandling', 'Mac', 'G', 'M', TO_DATE('09-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 98871.03);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Sizemore', 'Casey', 'T', 'M', TO_DATE('10-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 73455.15);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Pressly', 'Bob', 'E', 'M', TO_DATE('11-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 63675.02);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Sandler', 'Joanna', 'A', 'F', TO_DATE('11-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 56205.25);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Callow', 'Ramsey', 'S', 'M', TO_DATE('11-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 90966.42);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Skerritt', 'Rade', 'T', 'F', TO_DATE('12-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 44394.27);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('McBride', 'Earl', 'M', 'M', TO_DATE('14-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 58023.76);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Shepherd', 'Charles', 'A', 'M', TO_DATE('15-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 67411.52);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Nicholas', 'Mint', 'C', 'F', TO_DATE('15-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 63045.15);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Bassett', 'Jennifer', 'A', 'F', TO_DATE('15-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 86512.69);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Tobolowsky', 'Ronny', 'C', 'M', TO_DATE('16-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 77830.91);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Finney', 'Marisa', NULL, 'F', TO_DATE('17-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 92955.58);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Maxwell', 'Emily', 'C', 'F', TO_DATE('17-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 72122.43);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('MacLachlan', 'Walter', 'K', 'M', TO_DATE('17-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 97292.06);

COMMIT;


-----
-- Create a third set of applicants for one final set of examples
-----
INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Conners', 'Jose', 'S', 'M', TO_DATE('18-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 49579.74);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('McFerrin', 'Jonatha', 'R', 'F', TO_DATE('19-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 87397.11);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Webb', 'Night', 'J', 'F', TO_DATE('22-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 85049.10);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Dzundza', 'Tramaine', 'O', 'M', TO_DATE('23-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 35239.10);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('McCormack', 'Ethan', 'L', 'M', TO_DATE('23-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 49729.40);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Withers', 'Andie', 'W', 'F', TO_DATE('25-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 50804.93);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Goodman', 'Sonny', NULL, 'M', TO_DATE('26-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 97469.88);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Goodman', 'Fiona', 'I', 'F', TO_DATE('26-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 35213.20);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Watson', 'Melanie', 'J', 'F', TO_DATE('26-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 76803.79);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Chaplin', 'Bridgette', 'C', 'F', TO_DATE('26-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 67701.57);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Patton', 'Ted', 'N', 'M', TO_DATE('27-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 43295.03);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Winwood', 'Chloe', 'S', 'F', TO_DATE('28-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 57301.55);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('King', 'Clint', NULL, 'M', TO_DATE('28-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 50291.11);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Carrington', 'Joan', 'B', 'F', TO_DATE('28-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 91919.56);

INSERT INTO hr.applicants (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
VALUES('Tyson', 'Hex', 'M', 'M', TO_DATE('29-10-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 56582.30);

COMMIT;