Different Kinds of Exception in DBMS

Different Kinds of Exception in DBMS

• 1.
  Pl/sql exception
• 2.
  Errors • Two typesof errors can be found in a program: compilation errors and runtime errors. • There is a special section in a PL/SQL block that handles the runtime errors. • This section is called the exception-handling section, and in it, runtime errors are referred to as exceptions. • The exception-handling section allows programmers to specify what actions should be taken when a specific exception occurs.
• 3.
  How to handleException • In order to handle run time errors in the program, an exception handler must be added. • The exception-handling section has the following structure: EXCEPTION WHEN EXCEPTION_NAME THEN ERROR-PROCESSING STATEMENTS; • The exception-handling section is placed after the executable section of the block.
• 4.
  How exception canbe classified???  Build-in exception  Userdefined exception
• 5.
  BUILT-IN EXCEPTIONS  Whena built-in exception occurs, it is said to be raised implicitly.  In other words, if a program breaks an Oracle rule, the control is passed to the exception- handling section of the block.  At this point, the error processing statements are executed.  It is important for you to realize that after the exception-handling section of the block has executed, the block terminates.  Control will not return to the executable section of this block.
• 6.
  This example producesthe following output: There is no such student PL/SQL procedure successfully completed. Why??? Because there is no record in the STUDENT table with student ID 101, the SELECT INTO statement does not return any rows. As a result, control passes to the exception-handling section of the block, and the error message “There is no such student” is displayed on the screen. Even though there is a DBMS_OUTPUT.PUT_LINE statement right after the SELECT statement, it will not be executed because control has been transferred to the exception-handling section. Example DECLARE v_student_name VARCHAR2(50); BEGIN SELECT first_name||‘ ’||last_name INTO v_student_name FROM student WHERE student_id = 101; DBMS_OUTPUT.PUT_LINE (‘Student name is’||v_student_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (‘There is no such student’); END;
• 7.
  What are predefinedexception??  The list shown below explains some commonly used predefined exceptions and how they are raised:  NO_DATA_FOUND This exception is raised when a SELECT INTO statement, which makes no calls to group functions, such as SUM or COUNT, does not return any rows.  For example, you issue a SELECT INTO statement against STUDENT table where student ID equals 101.  If there is no record in the STUDENT table passing this criteria (student ID equals 101), the NO_DATA_FOUND exception is raised.
• 8.
  TOO_MANY_ROWS  This exceptionis raised when a SELECT INTO statement returns more than one row.  By definition, a SELECT INTO can return only single row.  If a SELECT INTO statement returns more than one row, the definition of the SELECT INTO statement is violated.  This causes the TOO_MANY_ROWS exception to be raised.  For example, you issue a SELECT INTO statement against the STUDENT table for a specific zip code.  There is a big chance that this SELECT statement will return more than one row because many students can live in the same zip code area.
• 9.
  ZERO_DIVIDE  This exceptionis raised when a division operation is performed in the program and a divisor is equal to zero.  Previous example in the illustrates how this exception is raised. LOGIN_DENIED  This exception is raised when a user is trying to login on to Oracle with invalid username or password.
• 10.
  PROGRAM_ERROR  This exceptionis raised when a PL/SQL program has an internal problem VALUE_ERROR  This exception is raised when conversion or size mismatch error occurs.  For example, you select student’s last name into a variable that has been defined as VARCHAR2(5).  If student’s last name contains more than five characters, VALUE_ERROR exception is raised.
• 11.
  DUP_VALUE_ON_INDEX  This exceptionis raised when a program tries to store a duplicate value in the column or columns that have a unique index defined on them.  For example, you are trying to insert a record into the SECTION table for the course number “25,” section 1.  If a record for the given course and section numbers already exists in the SECTION table, DUP_VAL_ON_INDEX exception is raised because these columns have a unique index defined on them.
• 12.
  Example FROM enrollment WHERE student_id= v_student_id; DBMS_OUTPUT.PUT_LINE ('Student is registered for '||v_total||' course(s)'); */inner block*/EXCEPTION WHEN VALUE_ERROR OR INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('An error has occurred'); END; */outer block */ EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('There is no such student'); END;
• 13.
  • The innerblock has structure similar to the outer block. • It has a SELECT INTO statement and an exception section to handle errors. • When VALUE_ERROR or INVALID_NUMBER error occurs in the inner block, the exception is raised. • It is important that you realize that exceptions VALUE_ERROR and INVALID_NUMBER have been defined for the inner block only. • Therefore, they can be raised in the inner block only. • If one of these errors occurs in the outer block, this program will be unable to terminate successfully.
• 14.
  User Defined Exceptions •Often in programs you may need to handle problems that are specific to the program you write. • For example, your program asks a user to enter a value for student_id. This value is then assigned to the variable v_student_id that is used later in the program. • Generally, you want a positive number for an id. By mistake, the user enters a negative number. • However, no error has occurred because student_id has been defined as a number, and the user has supplied a legitimate numeric value. • Therefore, you may want to implement your own exception to handle this situation.
• 15.
  How to declareuser-defined exception • A user-defined exception is declared in the declarative part of a PL/SQL block as shown below: DECLARE exception_name EXCEPTION; • Once an exception has been declared, the executable statements associated with this exception are specified in the exception-handling section of the block. • The format of the exception-handling section is the same as for built-in exceptions
• 16.
  Raising Exception • user-defined exceptionmust be raised explicitly. • In other words, you need to specify in your program under which circumstances an exception must be raised as shown : DECLARE exception_name EXCEPTION; BEGIN … IF CONDITION THEN RAISE exception_name; ELSE … END IF; EXCEPTION WHEN exception_name THEN ERROR-PROCESSING STATEMENTS; END;
• 17.
  example declare e exception; vamt number(10); begin selectamt into vamt from ac_det where acno=124; vamt:=vamt-77000; if(vamt<2000) then raise e; end if; exception when e then dbms_output.put_line('Minimum balance amount conflict'); * end; SQL> / Declare exception if the particular data is not found Raise exception ‘E’ If ‘E’ is exception the it will be displayed as ‘minimum amount conflict’
• 18.
  Thank you By R.sujaritha