Skip Headers
Oracle® Database Application Express Advanced Tutorials
Release 3.0

B28842-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
PDF · Mobi · ePub

A DDLs and Scripts

This appendix contains DDLs (data definition language) and scripts necessary to complete a number of tutorials in Oracle Database Application Express Advanced Tutorials.

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" on page 14-1.

-- 
-- 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" on page 14-1.

--
-- 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.','','')
/