Skip Headers
Oracle® Application Express Advanced Tutorials
Release 3.2

Part Number E11945-02
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 Application Express Advanced Tutorials.

Topics in this section include:

Creating Application Database Objects DDL

The following DDL creates all the required database objects used by the Issue Tracking application. The Issue Tracking application is described in Chapter 14, "How to Design an Issue Tracking Application" and Chapter 15, "How to Build and Deploy an Issue Tracking Application".

--
-- IT_API package spec
--
create or replace package it_api
as
function gen_pk
return number;
end it_api;
/
--
-- IT_PROJECTS
--
-- The IT_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
-- + populates the project id whenever a new record is created
-- + sets the auditing columns
-- + declares table and column comments
--
create table it_projects (
project_id number not null,
project_name varchar2(255) not null,
start_date date not null,
target_end_date date not null,
actual_end_date date,
created_on date not null,
created_by varchar2(255) not null,
modified_on date,
modified_by varchar2(255)
)
/
alter table it_projects
add constraint it_projects_pk
primary key (project_id)
/
alter table it_projects
add constraint it_projects_uk
unique (project_name)
/
create or replace trigger it_projects_biu
before insert or update on it_projects
for each row
begin
if inserting then
if :NEW.PROJECT_ID is null then
:NEW.PROJECT_ID := it_api.gen_pk;
end if;
:NEW.CREATED_ON := sysdate;
:NEW.CREATED_BY := nvl(v('APP_USER'),USER);
end if;
if updating then
:NEW.MODIFIED_ON := sysdate;
:NEW.MODIFIED_BY := nvl(v('APP_USER'),USER);
end if;
end;
/
comment on table it_projects is
'All projects currently underway.'
/
comment on column it_projects.project_id is
'The system generated unique identifier for the project.'
/
comment on column it_projects.project_name is
'The unique name of the project.'
/
comment on column it_projects.start_date is
'The start date of the project.'
/
comment on column it_projects.target_end_date is
'The targeted end date of the project.'
/
comment on column it_projects.actual_end_date is
'The actual end date of the project.'
/
comment on column it_projects.created_on is
'Audit Column: Date the record was created.'
/
comment on column it_projects.created_by is
'Audit Column: The user who created the record.'
/
comment on column it_projects.modified_on is
'Audit Column: Date the record was last modified.'
/
comment on column it_projects.modified_by is
'Audit Column: The user who last modified the record.'
/
--
-- IT_PEOPLE
--
-- The IT_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
-- + populates the person id whenever a new record is created
-- + sets the auditing columns
-- + declares table and column comments
--
create table it_people (
person_id number not null,
person_name varchar2(255) not null,
person_email varchar2(255) not null,
person_role varchar2(30) not null,
username varchar2(255) not null,
assigned_project number,
created_on date not null,
created_by varchar2(255) not null,
modified_on date,
modified_by varchar2(255)
)
/
alter table it_people
add constraint it_people_pk
primary key (person_id)
/
alter table it_people
add constraint it_people_name_uk
unique (person_name)
/
alter table it_people
add constraint it_people_username_uk
unique (username)
/
alter table it_people
add constraint it_people_role_cc
check (person_role in ('CEO','Manager','Lead','Member'))
/
alter table it_people
add constraint it_people_project_fk
foreign key (assigned_project)
references it_projects
/
alter table it_people
add constraint it_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 or replace trigger it_people_biu
before insert or update on it_people
for each row
begin
if inserting then
if :NEW.PERSON_ID is null then
:NEW.PERSON_ID := it_api.gen_pk;
end if;
:NEW.CREATED_ON := sysdate;
:NEW.CREATED_BY := nvl(v('APP_USER'),USER);
end if;
if updating then
:NEW.MODIFIED_ON := sysdate;
:NEW.MODIFIED_BY := nvl(v('APP_USER'),USER);
end if;
end;
/
comment on table it_people is
'All people within the company.'
/
comment on column it_people.person_id is
'The system generated unique identifier for the person.'
/
comment on column it_people.person_name is
'The unique name of the person.'
/
comment on column it_people.person_role is
'The role the person plays within the company.'
/
comment on column it_people.username is
'The username of this person. Used to link login to person details.'
/
comment on column it_people.assigned_project is
'The project that the person is currently assigned to.'
/
comment on column it_people.created_on is
'Audit Column: Date the record was created.'
/
comment on column it_people.created_by is
'Audit Column: The user who created the record.'
/
comment on column it_people.modified_on is
'Audit Column: Date the record was last modified.'
/
comment on column it_people.modified_by is
'Audit Column: The user who last modified the record.'
/
--
-- IT_ISSUES
--
-- The IT_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
-- + populates the issue id whenever a new record is created
-- + sets the auditing columns
-- + assigns the status of 'Open' if no status is provided
-- + sets the status to 'Closed' if an ACTUAL_RESOLUTION_DATE is provided
-- + declares table and column comments
--
create table it_issues (
issue_id number not null,
issue_summary varchar2(255) not null,
issue_description varchar2(4000),
identified_by_person_id number not null,
identified_date date not null,
related_project_id number not null,
assigned_to_person_id number,
status varchar2(30) not null,
priority varchar2(30) not null,
target_resolution_date date,
progress varchar2(4000),
actual_resolution_date date,
resolution_summary varchar2(4000),
created_on date not null,
created_by varchar2(255) not null,
modified_on date,
modified_by varchar2(255)
)
/
alter table it_issues
add constraint it_issues_pk
primary key (issue_id)
/
alter table it_issues
add constraint it_issues_identified_by_fk
foreign key (identified_by_person_id)
references it_people
/
alter table it_issues
add constraint it_issues_assigned_to_fk
foreign key (assigned_to_person_id)
references it_people
/
alter table it_issues
add constraint it_issues_project_fk
foreign key (related_project_id)
references it_projects
/
alter table it_issues
add constraint it_issues_status_cc
check (status in ('Open','On-Hold','Closed'))
/
alter table it_issues
add constraint it_issues_priority_cc
check (priority in ('High','Medium','Low'))
/
create or replace trigger it_issues_biu
before insert or update on it_issues
for each row
begin
if inserting then
if :NEW.ISSUE_ID is null then
:NEW.ISSUE_ID := it_api.gen_pk;
end if;
:NEW.CREATED_ON := sysdate;
:NEW.CREATED_BY := nvl(v('APP_USER'),USER);
if :new.status is null
then :new.status := 'Open';
end if;
end if;
if updating then
:NEW.MODIFIED_ON := sysdate;
:NEW.MODIFIED_BY := nvl(v('APP_USER'),USER);
if :new.actual_resolution_date is not null
then :new.status := 'Closed';
end if;
end if;
end;
/
comment on table it_issues is
'All issues related to the projects being undertaken by the company.'
/
comment on column it_issues.issue_id is
'The system generated unique identifier for the issue.'
/
comment on column it_issues.issue_summary is
'A brief summary of the issue.'
/
comment on column it_issues.issue_description is
'A full description of the issue.'
/
comment on column it_issues.identified_by_person_id is
'The person who identified the issue.'
/
comment on column it_issues.identified_date is
'The date the issue was identified.'
/
comment on column it_issues.related_project_id is
'The project that the issue is related to.'
/
comment on column it_issues.assigned_to_person_id is
'The person that the issue is assigned to.'
/
comment on column it_issues.status is
'The current status of the issue.'
/
comment on column it_issues.priority is
'The priority of the issue. How important it is to get resolved.'
/
comment on column it_issues.target_resolution_date is
'The date on which the issue is planned to be resolved.'
/
comment on column it_issues.actual_resolution_date is
'The date the issue was actually resolved.'
/
comment on column it_issues.progress is
'Any progress notes on the issue resolution.'
/
comment on column it_issues.resolution_summary is
'The description of the resolution of the issue.'
/
comment on column it_issues.created_on is
'Audit Column: Date the record was created.'
/
comment on column it_issues.created_by is
'Audit Column: The user who created the record.'
/
comment on column it_issues.modified_on is
'Audit Column: Date the record was last modified.'
/
comment on column it_issues.modified_by is
'Audit Column: The user who last modified the record.'
/
--
-- IT_API package body
--
create or replace package body it_api
as
-- generates and returns unique number used for primary key values
function gen_pk
return number
is
l_pk number := 0;
begin
for c1 in (
select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') pk
from dual )
loop
l_pk := c1.pk;
exit;
end loop;
return l_pk;
end gen_pk;
end it_api;
/

Creating Issues Script

The following script populates the Issues table for the Issue Tracking application described in Chapter 14, "How to Design an Issue Tracking Application".

create or replace package it_sample_data
as
procedure create_sample_projects;
procedure create_sample_people;
procedure create_sample_issues;
procedure remove_sample_data;
end it_sample_data;
/
create or replace package body it_sample_data
as
procedure create_sample_projects
is
begin
insert into it_projects (project_id, project_name, start_date, target_end_date)
values (1, 'Internal Infrastructure', sysdate-150, sysdate-30);
insert into it_projects (project_id, project_name, start_date, target_end_date)
values (2, 'New Payroll Rollout', sysdate-150, sysdate+15);
insert into it_projects (project_id, project_name, start_date, target_end_date)
values (3, 'Email Integration', sysdate-120, sysdate-60);
insert into it_projects (project_id, project_name, start_date, target_end_date)
values (4, 'Public Website Operational', sysdate-60, sysdate+30);
insert into it_projects (project_id, project_name, start_date, target_end_date)
values (5, 'Employee Satisfaction Survey', sysdate-30, sysdate+60);
commit;
end create_sample_projects;
procedure create_sample_people
is
begin
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (1, 'Joe Cerno', 'joe.cerno@mrvl-bademail.com', 'CEO', 'jcerno', null);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (2, 'Kim Roberts', 'kim.roberts@mrvl-bademail.com', 'Manager', 'kroberts', null);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (3, 'Tom Suess', 'tom.suess@mrvl-bademail.com', 'Manager', 'tsuess', null);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (4, 'Al Bines', 'al.bines@mrvl-bademail.com', 'Lead', 'abines', 1);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (5, 'Carla Downing', 'carla.downing@mrvl-bademail.com', 'Lead', 'cdowning', 2);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (6, 'Evan Fanner', 'evan.fanner@mrvl-bademail.com', 'Lead', 'efanner', 3);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (7, 'George Hurst', 'george.hurst@mrvl-bademail.com', 'Lead', 'ghurst', 4);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (8, 'Irene Jones', 'irene.jones@mrvl-bademail.com', 'Lead', 'ijones', 5);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (9, 'Karen London', 'karen.london@mrvl-bademail.com', 'Member', 'klondon', 1);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (10, 'Mark Nile', 'mark.nile@mrvl-bademail.com', 'Member', 'mnile', 1);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (11, 'Jane Kerry', 'jane.kerry@mrvl-bademail.com', 'Member', 'jkerry', 5);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (12, 'Olive Pope', 'olive.pope@mrvl-bademail.com', 'Member','opope', 2);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (13, 'Russ Sanders', 'russ.sanders@mrvl-bademail.com', 'Member', 'rsanders', 3);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (14, 'Tucker Uberton', 'tucker.uberton@mrvl-bademail.com', 'Member', 'ruberton', 3);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (15, 'Vicky Williams', 'vicky.willaims@mrvl-bademail.com', 'Member', 'vwilliams', 4);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (16, 'Scott Tiger', 'scott.tiger@mrvl-bademail.com', 'Member', 'stiger', 4);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (17, 'Yvonne Zeiring', 'yvonee.zeiring@mrvl-bademail.com', 'Member', 'yzeirling', 4);
commit;
end create_sample_people;
procedure create_sample_issues
is
begin
insert into it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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.');
insert into it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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, '');
insert into it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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,
'','','');
insert into it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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,
'','','');
insert into it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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 it_issues
(issue_id, issue_summary, issue_description,
identified_by_person_id, identified_date,
related_project_id, assigned_to_person_id, 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.','','');
commit;
end create_sample_issues;
procedure remove_sample_data
is
begin
delete from it_issues where issue_id < 29;
delete from it_people where person_id < 18;
delete from it_projects where project_id < 6;
commit;
end remove_sample_data;
end it_sample_data;
/