NOdatafound 100 TOOMANYROWS 1422 CASENOTFOUND 6592 INVALIDNUMBER 1722

Nodatafound 100 toomanyrows 1422 casenotfound 6592

This preview shows page 32 - 39 out of 119 pages.

NO_data_found 100 TOO_MANY_ROWS -1422 CASE_NOT_FOUND -6592 INVALID_NUMBER -1722 VALUE_ERROR -6502 CURSOR_ALREADY_OPEN -6511 INVALID_CURSOR -1001 ZERO_DIVIDED -1476 DUP_VAL_ON_INDEX -0001 COLLECTION_IS_NULL -6531 SUBSCRIPT_BEYOND_COUNT -6533 SUBSCRIPT_OUTSIDE_LIMIT -6532 PROGRAM_ERROR -6501 STORAGE_ERROR -6500 LOGIN_DENIED -1017 MEMORY_ERROR SELF_IS_NULL -30625 ROWTYPE_MISMATCH -6504 NO_DATA_NEEDED -6548 OTHERS User_defined exceptions: Declare Ex exception; Vno emp.empno%type :=&n; Vrow emp%rowtype; 32
Image of page 32
ORACLE BY NARAYANA REDDY Begin Select * into vrow from emp where empno = vno; If vrow.comm is null then CASES IN EXCEPTIONS OR EXCEPTIONAL CASES CASE: 1 DECLARE ----------------; BEGIN ----------------; EXCEPTION WHEN OTHERS THEN; ----------------------------------; WHEN EX THEN; ---------------------------------; END; NOTE : It throws an error. Others should not be at first in the list of exceptions. It should be always at last among exceptions. CASE: 2 DECLARE ----------------; BEGIN ----------------; EXCEPTION WHEN EX1 OR EX2 THEN; ---------------------------------------; END; NOTE: It is possible to mention the exception in series by separating with ‘OR’ operator. 33
Image of page 33
ORACLE BY NARAYANA REDDY CASE: 3 DECLARE ------------- BEGIN ------------- EXCEPTION WHEN EX1 OR OTHERS THEN; ----------------------------------------------- END; NOTE: It is not possible to mention the exception in series by separating with’OR’ operator. CASE: 4 DECLARE ----------------; BEGIN ----------------; EXCEPTION WHEN EX1 AND EX2 THEN; ------------------------------------------; END; NOTE: It is not possible to mention the exception in series by separating with AND operator. 34
Image of page 34
ORACLE BY NARAYANA REDDY CASE: 5 DECLARE ------------------ BEGIN BEGIN RAISE EX; EXCEPTION; WHEN EX2 THEN; END; EXCEPTION WHEN EX THEN; END; NOTE : Exceptions raised in inner block can be handled in the outer block. This is called as EXCEPTION PROPAGATION. CASE:6 DECLARE ---------------; BEGIN RAISE EX2; BEGIN RAISE EX; EXCEPTION WHEN EX2 THEN; END; EXCEPTION WHEN EX THEN; END; 35
Image of page 35
ORACLE BY NARAYANA REDDY NOTE: Exception raised in outer block cannot be handled in inner block. CASE: 7 DECLARE ---------------- BEGIN BEGIN RAISE EX EXCEPTION WHEN EX THEN; -------------------------- RAISE WHEN EX THEN; -----------------------; END; EXCEPTION WHEN EX THEN; -------------------------; END; NOTE : Exception raised in exception section cannot be handled in the same block exceptional section. But possible to handle outer bock exceptional sections which is also called as EXCEPTION PROPAGATION 36
Image of page 36
ORACLE BY NARAYANA REDDY CASE: 8 DECLARE --------------- BEGIN DECLARE V NUMBER (2):=12345 BEGIN ---------------- EXCEPTION WHEN VALUE_ERROR THEN ----------------------------------------; END; EXCEPTION WHEN VALUE_ERROR THEN ------------------------------------------; END; NOTE: Exceptions raised in declarative section cannot be handled in same block exception sections. TRACING AN EXCEPTION DECLARE BEGIN SELECT ................................... ; SELECT ................................... ; SELECT ................................... ; SELECT ................................... ; SELECT ................................... ; SELECT ................................... ; EXCEPTION WHEN NO_DATA_FOUND THEN -------------------------------------------------; 37
Image of page 37
ORACLE BY NARAYANA REDDY END; NOTE: In above case we are not able to identify in which select statement the exception has occurred. so to overcome this situation we writ as follows DECLARE ----------------- ; BEGIN DBMS_OUTPUT.PUT_LINE('H'); SELECT ............................................ ; DBMS_OUTPUT.PUT_LINE('A'); SELECT ............................................ ; DBMS_OUTPUT.PUT_LINE('R'); SELECT ............................................ ; DBMS_OUTPUT.PUT_LINE('I'); SELECT ............................................ ; DBMS_OUTPUT.PUT_LINE('S'); SELECT ............................................ DBMS_OUTPUT.PUT_LINE ('H'); EXCEPTION WHEN NO_DATA_FOUND THEN END; NOTE: If we get only 'H' as output then in first select statement the exception has occurred.
Image of page 38
Image of page 39

You've reached the end of your free preview.

Want to read all 119 pages?

  • Fall '16
  • Computer Science, Subroutine, ........., Control flow, Narayana Reddy

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture