A DDLおよびスクリプト

この付録では、『Oracle Application Expressアドバンスト・チュートリアル』の多数のチュートリアルを完了するために必要なDDL (データ定義言語)およびスクリプトについて説明します。

この項の内容は次のとおりです。

• アプリケーション・データベース・オブジェクトのDDLの作成

• 問題スクリプトの作成

アプリケーション・データベース・オブジェクトのDDLの作成

次に、問題追跡アプリケーションによって使用される必要なすべてのデータベース・オブジェクトを作成する場合のDDLを示します。問題追跡アプリケーションについては、第14章「問題追跡アプリケーションの設計方法」および第15章「問題追跡アプリケーションの作成およびデプロイ方法」で説明されています。

--
-- 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;
/

問題スクリプトの作成

次に、第14章「問題追跡アプリケーションの作成およびデプロイ方法」に記述されている問題追跡アプリケーションのIssues表に移入する場合のスクリプトを示します。

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', '[e-mail]', 'CEO', 'jcerno', null);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (2, 'Kim Roberts', '[e-mail]', 'Manager', 'kroberts', null);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (3, 'Tom Suess', '[e-mail]', 'Manager', 'tsuess', null);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (4, 'Al Bines', '[e-mail]', 'Lead', 'abines', 1);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (5, 'Carla Downing', '[e-mail]', 'Lead', 'cdowning', 2);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (6, 'Evan Fanner', '[e-mail]', 'Lead', 'efanner', 3);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (7, 'George Hurst', '[e-mail]', 'Lead', 'ghurst', 4);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (8, 'Irene Jones', '[e-mail]', 'Lead', 'ijones', 5);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (9, 'Karen London', '[e-mail]', 'Member', 'klondon', 1);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (10, 'Mark Nile', '[e-mail]', 'Member', 'mnile', 1);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (11, 'Jane Kerry', '[e-mail]', 'Member', 'jkerry', 5);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (12, 'Olive Pope', '[e-mail]', 'Member','opope', 2);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (13, 'Russ Sanders', '[e-mail]', 'Member', 'rsanders', 3);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (14, 'Tucker Uberton', '[e-mail]', 'Member', 'ruberton', 3);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (15, 'Vicky Williams', '[e-mail]', 'Member', 'vwilliams', 4);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (16, 'Scott Tiger', '[e-mail]', 'Member', 'stiger', 4);
insert into it_people (person_id, person_name, person_email, person_role, username, assigned_project)
values (17, 'Yvonne Zeiring', '[e-mail]', '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;
/