Amity Directorate of Distance Online Education Invalidday EXCEPTION BEGIN IF

Amity directorate of distance online education

This preview shows page 12 - 14 out of 36 pages.

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;
Image of page 12
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.
Image of page 13
Image of page 14

You've reached the end of your free preview.

Want to read all 36 pages?

  • Winter '17
  • DR Mubashir

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture