Write a PL/SQL block that retrieves entire COURSE table into a cursor. Then, ask user to input a courseId to search. If course exists then print its information, but if course does not exist throw a user-defined exception and handle it with a message ‘Course does not exist’ in the COURSE table when you execute the block with a course ID such as CIS555. (user-defined exception problem).

Question

What will be an ideal response?

Answer 1

```
SQL> DECLARE
2 no_course EXCEPTION;
3 CURSOR course_cur IS
4 SELECT * FROM course;
5 crs_id course.CourseId%TYPE := '&CourseId';
6 found BOOLEAN := FALSE;
7 course_rec course%ROWTYPE;
8 BEGIN
9 OPEN course_cur;
10 FETCH course_cur INTO course_rec;
11 WHILE course_cur%FOUND LOOP
12 IF course_rec.CourseId = crs_id THEN
13 found := TRUE;
14 DBMS_OUTPUT.PUT_LINE
15 ('Course Id: ' || course_rec.CourseId);
16 DBMS_OUTPUT.PUT_LINE
17 ('Title ' || course_rec.Title);
18 DBMS_OUTPUT.PUT_LINE
19 ('Credits: ' || course_rec.Credits);
20 DBMS_OUTPUT.PUT_LINE
21 ('PreReq: ' || course_rec.PreReq);
22 END IF;
23 FETCH course_cur INTO course_rec;
24 END LOOP;
25 IF found = false THEN
26 RAISE no_course;
27 END IF;
28 CLOSE course_cur;
29 EXCEPTION
30 WHEN no_course THEN
31 DBMS_OUTPUT.PUT_LINE('No such course: ' || crs_id);
32 END;
33 /
Enter value for courseid: EN100
Course Id: EN100
Title Basic English
Credits: 0
PreReq:

PL/SQL procedure successfully completed.

SQL> /
Enter value for courseid: CIS555
No such course: CIS555

PL/SQL procedure successfully completed.
```