DBM405B Lab 4 in PL/SQL – Exception Handlers – PL/SQL Script with 14 Screenshots – Instant Delivery – Perfect Solution

Lab Price = $23
Please feel free to send us your queries at: [email protected]
NOTE: THE QUERIES AND SCREENSHOTS ARE TAKEN IN PL/SQL DEVELOPER AND NOT IN IBM SYSTEM Z.
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

A. Lab 4 of 7 : Exception Handlers

B. Lab Overview – Scenario / Summary:

You have been asked to implement exception handlers to cater to various errors and conditions.

Upon the completion of this lab, students will have hands-on experience coding and using exceptions.

C. Deliverables:

Lab Steps (all in IBM System z) Points
Step 1 Aggregate Functions 15 Pts.
Step 2 Parent Key Not Found Exception Handler 15 Pts.
Step 3 Referential Integrity Exception Handler 15 Pts.
Step 4 Duplicate Key Found Exception Handler 15 Pts.
Total Lab Points 60 Pts.

D. Lab Steps:
Each program is independent of any other programs and is to be executed using the related database objects in the student database schema.

Step 1: Aggregate Functions
a) Code an SQL PL Procedure that will accept a student ID and count the number of courses in which that student is enrolled. (Count the number of records in the Enrollment table that correspond to the student.)
b) Output the message: [student_id] is enrolled in [tot_sec] courses.
c) For those students not enrolled in any courses, output the message: [student_id] is not enrolled in any courses.
d) Test your procedure with the following values: 102, 124, 292.

Step 2: Parent Key Not Found Exception Handler
a) Code an SQL PL Procedure to add a record to the Section table.
b) The procedure should accept the Section ID and Course No as parameters.
c) Include the following for the other columns:

Attribute Value
Section number 5
Instructor identification 106
Start date Today’s date
Location Null
Capacity Null

d) Use USER and CURRENT DATE for the auditing columns (Created By, Created Date, Modified By, Modified Date).
e) When a record can successfully be inserted, output the message: Section [section_id] of course number [course_no] was inserted.
f) Use an exception to handle the error when the course parent does not exist; in that case, output the message: The INSERT was not successful. You must insert the course parent first.
g) Execute your procedure with the following values:

• section id 77 with course number 10
• section id 78 with course number 400
Step 3: Referential Integrity Exception Handler

a) Create a stored procedure to delete Student records; the student ID should be passed into the procedure as a parameter.
b) Use an exception handler to trap the errors that arise when the procedure tries to delete a student who is still in the enrollment table.
c) For successful deletions, output a message which says:
Student id [student_id] has been deleted
d) For unsuccessful deletions, output a message which says:
The delete was not successful. Delete the student from enrollment first.
e) Execute the procedure for students 124 and 158.

Step 4: Duplicate Key Found

a) Create a stored procedure to insert Student records; the student ID should be passed into the procedure as a parameter. Default values can be used to set the other fields in the table.
b) Use an exception handler to trap the errors that arise when the procedure tries to insert a student with a number that is already in the table.
c) For successful insertions, output a message which says:
Student id [student_id] has been inserted
d) For unsuccessful insertions, output a message which says:
Student id [student_id] is already in the table
e) Execute the procedure using student ids of 110 and 190.

END OF LAB 4

For Submission into Week 4 iLab Dropbox:
Zipped file containing:
• SQL PL code for each task
• Output screenshots from executing each task

Relevant Material
Lab Price = $23
Please feel free to send us your queries at: [email protected]
Screenshots
Step 4: DuplicateKeyFoundSP
Step 4: DuplicateKeyFoundSP
Step 2: ParentKeyNotFoundSP
Step 2: ParentKeyNotFoundSP
Step 4: Output
Step 4: Output
Step 1: Output
Step 1: Output
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at [email protected].
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at [email protected] and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at [email protected].
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on [email protected]
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $23
Please feel free to send us your queries at: [email protected]
NOTE: THE QUERIES AND SCREENSHOTS ARE TAKEN IN PL/SQL DEVELOPER AND NOT IN IBM SYSTEM Z.

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply