PL/SQL Example Using SUBTYPE and VALUE_ERROR

This example was tested on Oracle 11.2. First it creates a single-digit variable called SMALL_NUMBER. It then uses this to define a subtype called SINGLE_DIGIT, which has the same type as SMALL_NUMBER. The SINGLE_DIGIT subtype is then used to create another single-digit variable called MINUS_NINE_THROUGH_PLUS_NINE. Negative and positive single-digit values are successfully assigned to this variable but an attempt to assign a double-digit value fails. This is picked up and handled by VALUE_ERROR in the exception section of the PL/SQL block:

 

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2   SMALL_NUMBER NUMBER(1,0);

  3   SUBTYPE SINGLE_DIGIT IS SMALL_NUMBER%TYPE;

  4   MINUS_NINE_THROUGH_PLUS_NINE SINGLE_DIGIT;

  5  BEGIN

  6   MINUS_NINE_THROUGH_PLUS_NINE := -9;

  7   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

  8   MINUS_NINE_THROUGH_PLUS_NINE := 0;

  9   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

 10   MINUS_NINE_THROUGH_PLUS_NINE := 9;

 11   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

 12   MINUS_NINE_THROUGH_PLUS_NINE := 10;

 13   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

 14  EXCEPTION

 15   WHEN VALUE_ERROR THEN

 16   DBMS_OUTPUT.PUT_LINE('Value out of range');

 17  END;

 18  /

-9

0

9

Value out of range

 

PL/SQL procedure successfully completed.

 

SQL>