Skip Headers
Oracle® Database 2 Day + Application Express Developer's Guide
Release 2.2

Part Number B28839-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
View PDF

A DDLs and Scripts

This appendix contains DDLs (data definition language) and scripts necessary to complete a number of tutorials in Oracle Database 2 Day + Application Express Developer's Guide.

Topics in this section include:

Creating Application Database Objects DDL

The following DDL creates all the required database objects for the issue tracking application in "How to Build and Deploy an Issue Tracking Application".

-- 
-- This DDL creates all the database objects used by the
-- Issue Management Application featured in 
--
-- HT_PROJECTS
--
-- The HT_PROJECTS DDL:
--    + creates the projects table with the necessary columns, 
--      including a new column for a system generated primary key
--    + declares the new primary key
--    + implements the real primary key, project name, as a unique key
--    + implements a sequence to generate project id
--    + assigns the sequence to populate the project id 
--      whenever a new record is created
--    + declares table and column comments
--






CREATE TABLE ht_projects (
   project_id              INTEGER           NOT NULL,
   project_name            VARCHAR2(100)     NOT NULL,
   start_date              DATE              NOT NULL,
   target_end_date         DATE              NOT NULL,
   actual_end_date         DATE

)
/
ALTER table ht_projects
   ADD CONSTRAINT ht_projects_pk
   PRIMARY KEY (project_id)
/
ALTER TABLE ht_projects
   ADD CONSTRAINT ht_projects_uk
   UNIQUE (project_name)
/
CREATE SEQUENCE ht_projects_seq
   INCREMENT BY 1
   START WITH 40
/
CREATE OR REPLACE TRIGGER bi_ht_projects
     BEFORE INSERT ON ht_projects
     FOR EACH ROW
  BEGIN
     IF :new.project_id is null
        THEN SELECT ht_projects_seq.nextval
               INTO :new.project_id
               FROM DUAL;
     END IF;
  END;
/

COMMENT ON table ht_projects IS 
   'All projects currently underway.'
/
COMMENT ON column ht_projects.project_id IS 
   'The system generated unique identifier for the project.'
/
COMMENT ON column ht_projects.project_name IS 
   'The unique name of the project.'
/
COMMENT ON column ht_projects.start_date IS 
   'The start date of the project.'
/
COMMENT ON column ht_projects.target_end_date IS 
   'The targeted end date of the project.'
/
COMMENT ON column ht_projects.actual_end_date IS 
   'The actual end date of the project.'
/

--
-- HT_PEOPLE
--
-- The HT_PEOPLE DDL:
--    + creates the people table with the necessary columns, 
--      including a new column for a system generated primary key
--    + declares the new primary key
--    + implements the real primary key, person name, as a unique key
--    + implements a check constraint to validate the roles that people 
--      can be assigned

--    + implements a foreign key to validate that people are assigned to 
--      valid projects
--    + implements a check constraint to enforce that all project leads 
--      and team members are assigned to projects
--    + implements a sequence to generate person id
--    + assigns the sequence to populate the person id whenever a 
--      new record is created
--    + declares table and column comments
--

CREATE TABLE ht_people (
   person_id              INTEGER           NOT NULL,
   person_name            VARCHAR2(100)     NOT NULL,
   person_email           VARCHAR2(100)     NOT NULL,
   person_role            VARCHAR2(7)       NOT NULL,
   assigned_project       INTEGER
)
/
ALTER TABLE ht_people
   ADD CONSTRAINT ht_people_pk
   PRIMARY KEY (person_id)
/
ALTER TABLE ht_people
   ADD CONSTRAINT ht_people_uk
   UNIQUE (person_name)
/
ALTER TABLE ht_people
   ADD CONSTRAINT ht_people_role_cc
   CHECK (person_role in ('CEO','Manager','Lead','Member'))
/
ALTER TABLE ht_people
   ADD CONSTRAINT ht_people_project_fk
   FOREIGN KEY (assigned_project)
   REFERENCES ht_projects
/
ALTER TABLE ht_people
   ADD CONSTRAINT ht_people_assignment_cc
   CHECK ( (person_role in ('Lead','Member') and assigned_project is not null)
   or (person_role in ('CEO','Manager') and assigned_project is null) )
/
CREATE SEQUENCE ht_people_seq
   INCREMENT BY 1
   START WITH 40
/
CREATE OR REPLACE TRIGGER bi_ht_people
      BEFORE INSERT on ht_people
      FOR EACH ROW
   BEGIN
      IF :new.person_id IS NULL
          THEN SELECT ht_people_seq.nextval
                 INTO :new.person_id
                 FROM DUAL;
      END IF;

   END;
/

COMMENT ON table ht_people IS 
   'All people within the company.'
/
COMMENT ON column ht_people.person_id IS 
   'The system generated unique identifier for the person.'
/
COMMENT ON column ht_people.person_name IS 
   'The unique name of the person.'
/
COMMENT ON column ht_people.person_role IS 
   'The role the person plays within the company.'
/
COMMENT ON column ht_people.assigned_project IS 
   'The project that the person is currently assigned to.'
/

--
-- HT_ISSUES
--
-- The HT_ISSUES DDL:
--    + creates the table with the necessary columns, including a new column 
--      for a system generated primary key
--    + declares the new primary key
--    + implements a foreign key to validate that the issue is identified by a
--      valid person
--    + implements a foreign key to validate that the issue is assigned to a 
--      valid person
--    + implements a foreign key to validate that the issue is associated with 
--      a valid project
--    + implements a check constraint to validate the status that is assigned 
--      to the issue
--    + implements a check constraint to validate the priority that is assigned 
--      to the issue
--    + implements a sequence to generate issue id
--    + assigns the sequence to populate the issue id and the creation date 
--      whenever a new record is created, records the user creating the
--      row and also assigns status of 'Open' if no status is provided
--    + records the current date and the user whenever an issue is edited and
--      sets the status to 'Closed' if an ACTUAL_RESOLUTION_DATE is 
--      provided
--    + declares table and column comments
--

create table ht_issues (
   issue_id                INTEGER           not null,
   issue_summary           VARCHAR2(200)     not null,
   issue_description       VARCHAR2(2000),
   identified_by           INTEGER NOT NULL,
   identified_date         DATE              not null,
   related_project         INTEGER           not null,
   assigned_to             INTEGER,
   status                  VARCHAR2(8)       not null,
   priority                VARCHAR2(6),
   target_resolution_date  DATE,
   progress                VARCHAR2(2000),
   actual_resolution_date  DATE,
   resolution_summary      VARCHAR2(2000),
   created_date            DATE              not null,
   created_by              VARCHAR2(60)      not null,
   last_modified_date      DATE,
   last_modified_by        VARCHAR2(60)
)
/
ALTER TABLE ht_issues
   ADD CONSTRAINT ht_issues_pk
   PRIMARY KEY (issue_id)
/
ALTER TABLE ht_issues
   ADD CONSTRAINT ht_issues_identified_by_fk
   FOREIGN KEY (identified_by)
   REFERENCES ht_people
/
ALTER TABLE ht_issues
   ADD CONSTRAINT ht_issues_assigned_to_fk
   FOREIGN KEY (assigned_to)
   REFERENCES ht_people
/
ALTER TABLE ht_issues
   ADD CONSTRAINT ht_issues_project_fk
   FOREIGN KEY (related_project)
   REFERENCES ht_projects
/
ALTER TABLE ht_issues
   ADD CONSTRAINT ht_issues_status_cc
   CHECK (status in ('Open','On-Hold','Closed'))
/
ALTER TABLE ht_issues
   ADD CONSTRAINT ht_issues_priority_cc
   CHECK (priority in ('High','Medium','Low'))

/

CREATE SEQUENCE ht_issues_seq
   INCREMENT BY 1
   START WITH 40
/
CREATE OR REPLACE TRIGGER bi_ht_issues
      BEFORE INSERT on ht_issues
      FOR EACH ROW
  BEGIN
      IF :new.issue_id IS NULL
         THEN SELECT ht_issues_seq.nextval
                INTO :new.issue_id
                FROM DUAL;
      END IF;
      IF :new.status IS NULL
         THEN :new.status := 'Open';
      END IF;
      :new.created_date := sysdate;
      :new.created_by := nvl(wwv_flow.g_user,user);
  END;
/
CREATE OR REPLACE TRIGGER bu_ht_issues
      BEFORE UPDATE ON ht_issues
      FOR EACH ROW
  BEGIN
      IF :new.actual_resolution_date IS NOT NULL
         THEN :new.status := 'Closed';
      END IF;

      :new.last_modified_date := sysdate;
      :new.last_modified_by := nvl(wwv_flow.g_user,user);
  END;
/

COMMENT ON table ht_issues IS 
   'All issues related to the projects being undertaken by the company.'
/
COMMENT ON column ht_issues.issue_id IS 
   'The system generated unique identifier for the issue.'
/
COMMENT ON column ht_issues.issue_summary IS 
   'A brief summary of the issue.'
/
COMMENT ON column ht_issues.issue_description IS 
   'A full description of the issue.'
/
COMMENT ON column ht_issues.identified_by IS 
   'The person who identified the issue.'
/
COMMENT ON column ht_issues.identified_date IS 
   'The date the issue was identified.'
/
COMMENT ON column ht_issues.related_project IS 
   'The project that the issue is related to.'
/
COMMENT ON column ht_issues.assigned_to IS 
   'The person that the issue is assigned to.'
/
COMMENT ON column ht_issues.status IS 
   'The current status of the issue.'
/
COMMENT ON column ht_issues.priority IS 
   'The priority of the issue. How important it is to get resolved.'
/
COMMENT ON column ht_issues.target_resolution_date IS 
   'The date on which the issue is planned to be resolved.'
/
COMMENT ON column ht_issues.actual_resolution_date IS 
   'The date the issue was actually resolved.'
/
COMMENT ON column ht_issues.progress IS 
   'Any progress notes on the issue resolution.'
/
COMMENT ON column ht_issues.resolution_summary IS 
   'The description of the resolution of the issue.'
/
COMMENT ON column ht_issues.created_date IS 
   'Audit Column: Date the record was created.'
/
COMMENT ON column ht_issues.created_by IS 
   'Audit Column: The user who created the record.'
/
COMMENT ON column ht_issues.last_modified_date IS 
   'Audit Column: Date the record was last modified.'
/
COMMENT ON column ht_issues.last_modified_by IS 
   'Audit Column: The user who last modified the record.'
/

Creating Issues Script

The following script populates Issues table for the issue tracking application in "How to Build and Deploy an Issue Tracking Application".

--
-- Email Integration Issues
--
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
     (1, 'Midwest call center servers have no failover due to Conn Creek plant  fire','', 
     6, sysdate-80,
     3, 6, 'Closed', 'Medium', sysdate-73,
     'Making steady progress.', sysdate-73, '')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
       identified_by, identified_date,
       related_project, assigned_to, status, priority, target_resolution_date,
       progress, actual_resolution_date, resolution_summary)
  VALUES
      (2, 'Timezone ambiguity in some EMEA regions is delaying bulk forwarding to mirror sites','', 
      6, sysdate-100,
      3, 14, 'Open', 'Low', sysdate-80,
      '','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
       identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (3, 'Some vendor proposals lack selective archiving and region-keyed retrieval sections','', 
      6, sysdate-110,
      3, 13, 'Closed', 'Medium', sysdate-90,
      '', sysdate-95, '')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (4, 'Client software licenses expire for Bangalore call center before cutover','', 
      1, sysdate-70,
      3, 6, 'Closed', 'High', sysdate-60,
      '',sysdate-66,'Worked with HW, applied patch set.')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (5, 'Holiday coverage for DC1 and DC3 not allowed under union contract, per acting steward at branch 745','', 
      1, sysdate-100,
      3, 13, 'Closed', 'High', sysdate-90,
      '',sysdate-95, 'Worked with HW, applied patch set.')
/
--
-- Employee Satisfaction Survey Issues
--
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (6, 'Review rollout schedule with HR VPs/Directors','', 
      8, sysdate-30,
      5, null, 'Closed', 'Medium', sysdate-15,
      '',sysdate-20,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (7, 'Distribute translated categories and questions for non-English regions to regional team leads','', 
      8, sysdate-2,
      5, 8, 'Open', 'Medium', sysdate+10,
      'currently beta testing new look and feel','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (8, 'Provide survey FAQs to online newsletter group','', 
      1, sysdate-10,
      5, 11, 'Open', 'Medium', sysdate+20,
      '','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (9, 'Need better definition of terms like work group, department, and organization for categories F, H, and M-W','', 
      1, sysdate-8,
      5, null, 'Open', 'Low', sysdate+15,
      '','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (10, 'Legal has asked for better definitions on healthcare categories for Canadian provincial regs compliance','', 
      1, sysdate-10,
      5, 11, 'Closed', 'Medium', sysdate+20,
      '',sysdate-1,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (11, 'Action plan review dates conflict with effectivity of organizational consolidations for Great Lakes region','', 
      1, sysdate-9,
      5, 11, 'Open', 'Medium', sysdate+45,
      '','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (12, 'Survey administration consulting firm requires indemnification release letter from HR SVP','', 
      1, sysdate-30,
      5, 11, 'Closed', 'Low', sysdate-15,
      '', sysdate-17, '')
/
--
-- Internal Infrastructure Issues
--
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
 VALUES
      (13, 'Facilities, Safety health-check reports must be signed off before capital asset justification can be approved','', 
      4, sysdate-145,
      1, 4, 'Closed', 'Medium', sysdate-100,
      '',sysdate-110,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (14, 'Cooling and Power requirements exceed 90% headroom limit -- variance from Corporate requested','', 
      4, sysdate-45,
      1, 9, 'Closed', 'High', sysdate-30,
      '',sysdate-35,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (15, 'Local regulations prevent Federal contracts compliance on section 3567.106B','', 
      4, sysdate-90,
      1, 10, 'Closed', 'High', sysdate-82,
      '',sysdate-85,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (16, 'Emergency Response plan failed county inspector''s review at buildings 2 and 5','', 
      4, sysdate-35,
      1, null, 'Open', 'High', sysdate-5,
      '','','')
/
--
-- New Payroll Rollout Issues
--
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (17, 'Training for call center 1st and 2nd lines must be staggered across shifts','', 
      5, sysdate-8,
      2, 5, 'Closed', 'Medium', sysdate+10,
      '',sysdate-1,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (18, 'Semi-monthly ISIS feed exceeds bandwidth of Mississauga backup site','', 
      5, sysdate-100,
      2, 12, 'On-Hold', 'Medium', sysdate-30,
      'pending info from supplier','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (19, 'Expat exception reports must be hand-reconciled until auto-post             phaseout complete','', 
      5, sysdate-17,
      2, 12, 'Closed', 'High', sysdate+4,
      '',sysdate-4,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (20, 'Multi-region batch trial run schedule and staffing plan due to directors by end of phase review','', 
      5, sysdate,
      2, null, 'Open', 'High', sysdate+15,
      '','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (21, 'Auditors'' signoff requires full CSB compliance report','', 
      5, sysdate-21,
      2, 5, 'Open', 'High', sysdate-7,
      '','','')
/
--
-- Public Website Operational Issues
--
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (22, 'Review security architecture plan with consultant','', 
      1, sysdate-60,
      4, 7, 'Closed', 'High', sysdate-45,
      '',sysdate-40,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (23, 'Evaluate vendor load balancing proposals against capital budget','', 
      7, sysdate-50,
      4, 7, 'Closed', 'High', sysdate-45,
      '',sysdate-43,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (24, 'Some preferred domain names are unavailable in registry','', 
      7, sysdate-55,
      4, 15, 'Closed', 'Medium', sysdate-45,
      '',sysdate-50,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (25, 'Establish grid management capacity-expansion policies with ASP','', 
      7, sysdate-20,
      4, 16, 'Open', 'Medium', sysdate-5,
      '','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (26, 'Access through proxy servers blocks some usage tracking tools','', 
      7, sysdate-10,
      4, 15, 'Closed', 'High', sysdate-5,
      '',sysdate-1,'')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (27, 'Phase I stress testing cannot use production network','', 
      7, sysdate-11,
      4, 17, 'Open', 'High', sysdate,
      '','','')
/
INSERT INTO ht_issues
      (issue_id, issue_summary, issue_description, 
      identified_by, identified_date,
      related_project, assigned_to, status, priority, target_resolution_date,
      progress, actual_resolution_date, resolution_summary)
  VALUES
      (28, 'DoD clients must have secure port and must be blocked from others','', 
      7, sysdate-20,
      4, 17, 'On-Hold', 'High', sysdate,
      'Waiting on Security Consultant, this may drag on.','','')
/