top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

what are the Predefined Exceptions in plsql?

+2 votes
494 views
what are the Predefined Exceptions in plsql?
posted Oct 9, 2015 by Kunal Kapoor

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.

Table lists the names and error codes of the predefined exceptions.

Table: PL/SQL Predefined Exceptions

Exception Name Error Code

ACCESS_INTO_NULL

-6530

CASE_NOT_FOUND

-6592

COLLECTION_IS_NULL

-6531

CURSOR_ALREADY_OPEN

-6511

DUP_VAL_ON_INDEX

-1

INVALID_CURSOR

-1001

INVALID_NUMBER

-1722

LOGIN_DENIED

-1017

NO_DATA_FOUND

+100

NO_DATA_NEEDED

-6548

NOT_LOGGED_ON

-1012

PROGRAM_ERROR

-6501

ROWTYPE_MISMATCH

-6504

SELF_IS_NULL

-30625

STORAGE_ERROR

-6500

SUBSCRIPT_BEYOND_COUNT

-6533

SUBSCRIPT_OUTSIDE_LIMIT

-6532

SYS_INVALID_ROWID

-1410

TIMEOUT_ON_RESOURCE

-51

TOO_MANY_ROWS

-1422

VALUE_ERROR

-6502

ZERO_DIVIDE

-1476

Example : calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE and the executable part of the block transfers control to the exception-handling part.

Example : Anonymous Block Handles ZERO_DIVIDE

DECLARE
  stock_price   NUMBER := 9.73;
  net_earnings  NUMBER := 0;
  pe_ratio      NUMBER;
BEGIN
  pe_ratio := stock_price / net_earnings;  -- raises ZERO_DIVIDE exception
  DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Company had zero earnings.');
    pe_ratio := NULL;
END;
/

Result:

Company had zero earnings.
Example uses error-checking code to avoid the exception that Example 11-6 handles.

Example : Anonymous Block Avoids ZERO_DIVIDE

DECLARE

 stock_price   NUMBER := 9.73;
  net_earnings  NUMBER := 0;
  pe_ratio      NUMBER;
BEGIN
  pe_ratio :=
    CASE net_earnings
      WHEN 0 THEN NULL
      ELSE stock_price / net_earnings
    END;
END;
/
answer Oct 12, 2015 by Shivaranjini
...