Amity directorate of distance online education

Amity Directorate of Distance & Online Education
Invalid_day EXCEPTION;
BEGIN
IF RTRIM (TO CHAR(SYSDATE, ‘DAY’), ‘ ‘)= ‘Thursday’ then
RAISE invalid_day;
ELSE
DBMS_OUTPUT.PUT_LINE(‘You are allowed to do the data entry’);
END IF;
EXCEPTION
WHEN Invalid_day then
RAISE_APPLICATION_ERROR (-20001, ‘Not a valid day for data entry’);
END;
Fig. 6.19
When Others Trigger
Rather than defining a separate handler for every exception type, we may use the ‘WHEN
OTHERS’ exception handler, which handles all errors not already handled in the block. If used,
therefore, WHEN OTHERS should be coded after any other exception handlers in the block.
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO error_table VALUES (‘No Employee joined in 1992’);
WHEN TOO_MANY_ROWS THEN
INSERT INTO error_table VALUES (‘More Employee joined in 1992’);
WHEN OTHERS THEN
INSERT INTO error_table VALUES(‘Some unknown error’);
END;
In case, it will execute the WHEN OTHERS trigger for all errors other than no_data_
found and too_many_rows. For this case, we may wish to evaluate the associated error
code and error message. PL/SQL provides two functions for this purpose.
SQLCODE: It returns numeric data as negative number ( except than NO_DATA_FOUND
for which it returns + 100). For the user - defined exception, it returns +1.
SQLERRM:- It returns the message related with the error, may be assigned to a VARCHAR2
type of variable. The maximum length of code is 512 characters, which includes code,
message, table name and column name. It is advisable to truncate the value to a
known length before attempting to write it to a variable.
DECLARE
Error_message varchar2 (100);
Error_code NUMBER;
name emp.ename%type;
salary emp.sal%type;

Data Base Management systems
109
Notes
Amity Directorate of Distance & Online Education
BEGIN
SELECT ename, sal INTO name, salary FROM emp WHERE hiredate
BETWEEN ‘01-JAN-92’ AND ‘31-DEC-92’;
DBMS_OUTPUT.PUT_LINE(‘In 1992 ‘ || name||’ joined on Rs. ‘|| salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO error_table VALUES (‘No employee joined in 92’);
WHEN OTHERS THEN
error_code:= SQLCODE;
error_message:=SQLERRM;
INSERT INTO error_table VALUES (error_code || ‘ ‘|| Error_message);
END;
Fig. 6.20
Cursors
PL/SQL raises an error, if an embedded SELECT statement retrieves more than one
row, which causes an abnormal termination of the PL/SQL block. Such an error can be
eliminated by using a cursor. A cursor is a handle, or pointer to the work area. Through the
cursor PL/SQL program can control this work area and what happens to it as the statement
is processed. The data that is stored in the work area is called the Active Data Set. When
the SELECT…INTO or any DML statement is used the Oracle internally opens the cursor,
called as implicit cursor. Processing an implicit cursor is taken care of automatically by
PL/SQL. On the other hand, if a query returns more than one row then cursor has to be
defined explicitly. It is called as explicit cursor.