Database Administration - Oracle Database

Set echo on

Set up a spool file to receive your output for submission. I would suggest c:\cs423\S6project5spool.txt

Drop and create a sequence named DDI.NEWPATRON_SEQ that starts at 39 and increments by 1

Drop and create a sequence named DDI.NEWREG_SEQ (copy from S5 Project 4)

Create or replace a trigger named DDI.NEW_REGISTRATIONS_TRG (copy from S5 Project 4)

Create or replace a procedure named DDI.NEW_REGISTRATION that accepts as parameters: v_FirstName, v_LastName, v_PhoneNum, v_EMail, v_RegDate, v_AdultCnt, v_ChildCnt, and v_RoomNum. The procedure starts by selecting PATRONID into v_PatronID from DDI.PATRONS with the indicated EMail address. If the person already exists in DDI.PATRONS, then insert a record into DDI.REGISTRATION as you did in S5 Project 4. If the person does not exist in DDI.PATRONS, it will raise a NO_DATA_FOUND exception and you use the exception handler to insert a new record into DDI.PATRONS using NEWPATRON_SEQ.NEXTVAL for PATRONID. Also insert a new record into DDI.REGISTRATIONS using NEWPATRON_SEQ.CURRVAL for PATRONID. REGID will be provided by the trigger DDI.NEW_REGISTRATIONS_TRG

Compile the procedure

(If there are errors, SHOW ERRORS to see what needs to be corrected)

EXEC DDI.NEW_REGISTRATION('Dag', 'Renborn', '555-595-6240', 'Dag.Renborn@mymail.com', '01-JUN-16', 2, 0, 103)

Print the new record in DDI.REGISTRATIONS (REGID=46)

EXEC DDI.NEW_REGISTRATION('Glenn', 'Cummings', '555-123-4569', 'Glenn.Cummings@mymail.com, '01-JUN-16', 2, 0, 108)

Print the new record in DDI.REGISTRATIONS (REGID=47)

Print the new record in DDI.PATRONS (PATRONID=39)

(Be sure to ROLLBACK your transaction)

Close the spool file

**If you are unable to execute them all, just partial work will be fine***