Packages, Triggers, and Collections
1. (35 Points) Manually create the following database tables:
CREATE TABLE dept
( DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(16),
LOC VARCHAR2(16)
);
CREATE TABLE dept_shadow
( DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(16),
LOC VARCHAR2(16),
USER VARCHAR2(32),
MODTIME CHAR(17)
);
Create a trigger to track all inserts into a table. Specifically, for each reocrd
inserted into the DEPT table, the trigger should insert a duplicate record into the
DEPT_SHADOW table along with the information of the user who performs the insertion (the
USER column) as well as the date/time of the insertion (the MODTIME column). The MODTIME
column keeps track of the date/time in the following character string format:
MM/DD/YY hh:mm:ss.
Save your program in the script file prog4a.sql
2. (35 Points) For the DEPT table created in problem #1 above,insert the following records by using bulk binding
Dept = {(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'DALLAS'),
(50, 'MARKETING', 'BOSTON')}
Save your program in the script file prog4b.sql
3. (30 Points) Create a PL/SQL block to retrieve all the information about each department from the DEPT table and print the information to the screen by using a PL/SQL table of records.
Save your program in the script file prog4c.sql
