Amity Directorate of Distance & Online EducationInvalid_day EXCEPTION;BEGINIF RTRIM (TO CHAR(SYSDATE, ‘DAY’), ‘ ‘)= ‘Thursday’ thenRAISE invalid_day;ELSEDBMS_OUTPUT.PUT_LINE(‘You are allowed to do the data entry’);END IF;EXCEPTIONWHEN Invalid_day thenRAISE_APPLICATION_ERROR (-20001, ‘Not a valid day for data entry’);END;Fig. 6.19When Others TriggerRather 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.EXCEPTIONWHEN NO_DATA_FOUND THENINSERT INTO error_table VALUES (‘No Employee joined in 1992’);WHEN TOO_MANY_ROWS THENINSERT INTO error_table VALUES (‘More Employee joined in 1992’);WHEN OTHERS THENINSERT 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.DECLAREError_message varchar2 (100);Error_code NUMBER;name emp.ename%type;salary emp.sal%type;
Data Base Management systems109NotesAmity Directorate of Distance & Online EducationBEGINSELECT 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);EXCEPTIONWHEN NO_DATA_FOUND THENINSERT INTO error_table VALUES (‘No employee joined in 92’);WHEN OTHERS THENerror_code:= SQLCODE;error_message:=SQLERRM;INSERT INTO error_table VALUES (error_code || ‘ ‘|| Error_message);END;Fig. 6.20CursorsPL/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.