1.

How many types of Exceptions occur in PL/SQL?

Answer»

There are two types of EXCEPTIONS that exist in PL/SQL:

System Defined Exceptions

The predefined exceptions in PL/SQL are System Defined Exceptions. If a PL/SQL program violate the rule of Oracle, then an exception is raised. The exceptions are handled by name, for example, DUP_VAL_ON_INDEX exceptions are raised when an attempt is MADE to store DUPLICATE values in a database column constrained by a unique index.

Let us learn about the predefined exceptions in PL/SQL:

  • ACCESS_INTO_NULL: This exception is raised when an attempt is made to the attributes of an uninitialized object.
  • CASE_NOT_FOUND: This exception is raised when none of the choices in the WHEN clause of a CASE statement is selected and there is no ELSE clause.
  • COLLECTION_IS_NULL: This exception is raised when an attempt is made to apply collection methods other than EXISTS to an uninitialized nested table or varray.
  • DUP_VAL_ON_INDEX: This exception is raised when duplicate values are attempted to be stored in a column with unique index.
  • INVALID_CURSOR: This exception is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
  • INVALID_NUMBER: This exception is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
  • LOGIN_DENIED: This exception is raised when a program attempts to log on to the database with an invalid username or password.
  • NO_DATA_FOUND: This exception is raised when a SELECT INTO statement returns no rows.
  • NOT_LOGGED_ON: This exception is raised when a database call is issued without being connected to the database.
  • PROGRAM_ERROR: This exception is raised when there is an internal problem.
  • ROWTYPE_MISMATCH: This exception is raised when a cursor fetches value in a variable having incompatible data type.
  • SELF_IS_NULL: This exception is raised when a member method is invoked, but the instance of the object type was not initialized.
  • STORAGE_ERROR: This exception is raised when PL/SQL ran out of memory.
  • TOO_MANY_ROWS: This exception is raised when SELECT INTO statement returns more than one row.
  • VALUE_ERROR: This exception is raised when arithmetic, conversion, or truncation error occurs.
  • ZERO_DIVIDE: This exception is raised when an attempt is made to divide a number by zero.

User-Defined Exceptions

Like other programming languages, PL/SQL also allow users to create own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly.

To create own exception, firstly consider the following table <DEPTBUDGET>:

<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME  | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ |  1 | Finance          | NORTH | 56678      | |  2 | Marketing     | EAST |      87687 | |  3 | Operations   | WEST |      98979 | |  4 | Technical      | SOUTH |    76878 | |  5 | Accounting   | NORTHWEST| 86767      | +----------+------------------+------------------+--------------+

Let’s say we created the following procedure and a user-defined exception in it:

DECLARE   id deptbudget.id%type := &d_id;   deptname deptbudget.deptname%type;   deptloc deptbudget.deptloc%type;     my_exception  EXCEPTION; BEGIN   IF d_id <= 0 THEN      RAISE my_exception;   ELSE      SELECT  deptname, deptloc INTO deptname, deptloc      FROM deptbudget      WHERE id = d_id;      DBMS_OUTPUT.PUT_LINE ('Name: '||  deptname);      DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc);   END IF; EXCEPTION   WHEN my_exception THEN      dbms_output.put_line('ID is always greater than zero!');   WHEN no_data_found THEN      dbms_output.put_line('No such department in the company!');   WHEN others THEN      dbms_output.put_line('Error!');   END; /

On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:

ID is always greater than zero!


Discussion

No Comment Found

Related InterviewSolutions