InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
How to refer to the objects and subprograms declared within a package? |
|
Answer» Like other programming languages, PL/SQL ALSO allow users to create own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly. To create own exception, firstly consider the following table <DEPTBUDGET>: <DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | FINANCE | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+Let’s say we created the following procedure and a user-defined exception in it: DECLARE d_id deptbudget.id%type := &dd_id; d_name deptbudget.deptname%type; d_loc deptbudget.deptloc%type; my_exception EXCEPTION; BEGIN IF d_id <= 0 THEN RAISE my_exception; ELSE SELECT deptname, deptloc INTO d_name, d_loc FROM deptbudget WHERE deptid = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| d_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc); END IF; EXCEPTION WHEN my_exception THEN dbms_output.put_line('ID is always greater than zero!'); WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /On executing the above procedure, the following output is VISIBLE. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error: ID is always greater than zero! |
|