PL/SQL Exceptions – a primer demonstration

set serveroutput on

<<DEMO_AND_TEST_EXCEPTIONS>>
DECLARE

  MYOWNNODATAFOUND EXCEPTION;
--ERROR CAUSED BY  PRAGMA EXCEPTION_INIT (MYOWNNODATAFOUND, -1403);
--  Cannot rename Oracle native exceptions if their numbers are already named
--  in DBMS_STANDARD, e.g., ORA-01403 no data found

  userrange_exc_no constant number := -20202;

  USERRANGE_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT (USERRANGE_EXCEPTION, -20202);
  -- not allowed to use the constant to initialize the exception

  UNNUMBERED_USEREXCEPTION EXCEPTION;

  XTEST_NO NUMBER := 0;
  bogo number;
BEGIN
  DBMS_OUTPUT.ENABLE;

  <<XCEP_LOOP>>
  LOOP
    XTEST_NO := XTEST_NO + 1;
    DBMS_OUTPUT.PUT_LINE ('Exception test number ' || XTEST_NO);

    <<INNER_EXCEP_BLK>>
    BEGIN

      <<RAISERS>>
      CASE XTEST_NO
        WHEN 1 THEN
          -- triggers ORA-01403, no data found
          SELECT num_rows INTO BOGO
          FROM USER_TABLES
          WHERE 1 = NULL
            and rownum = 1
          ;
        WHEN 2 THEN
          RAISE USERRANGE_EXCEPTION;
        when 3 then
          RAISE UNNUMBERED_USEREXCEPTION;
        WHEN 4 THEN
          DBMS_STANDARD.RAISE_APPLICATION_ERROR (USERRANGE_EXC_NO, 'this is an intentional exception');
        else
          raise_application_error (-20002, 'ran out of exception cases!');
      END CASE RAISERS;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE ('[INNER] no data found exception: sqlcode ' || SQLCODE ||
          ', message ' || sqlerrm );
      WHEN USERRANGE_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE ('[INNER] user-range exception: sqlcode ' || SQLCODE ||
          ', message ' || sqlerrm );
      WHEN UNNUMBERED_USEREXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE ('[INNER] unnumbered exception: sqlcode ' || SQLCODE ||
          ', message ' || SQLERRM );
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('[INNER] exception: sqlcode ' || SQLCODE ||
          ', message ' || SQLERRM );

        EXIT XCEP_LOOP;   --<<<< THIS IS A PRETTY USEFUL APPLICATION OF PL/SQL BLOCK LABELS

    end inner_excep_blk;
  END LOOP XCEP_LOOP;

  DBMS_OUTPUT.PUT_LINE ('Ending exception test and demo block');

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('EXCEPTION: ' || SQLERRM);
END DEMO_AND_TEST_EXCEPTIONS;
/

anonymous block completed
Exception test number 1
[INNER] no data found exception: sqlcode 100, message ORA-01403: no data found
Exception test number 2
[INNER] user-range exception: sqlcode -20202, message ORA-20202:
Exception test number 3
[INNER] unnumbered exception: sqlcode 1, message User-Defined Exception
Exception test number 4
[INNER] user-range exception: sqlcode -20202, message ORA-20202: this is an intentional exception
Exception test number 5
[INNER] exception: sqlcode -20002, message ORA-20002: ran out of exception cases!
Ending exception test and demo block