Thursday, January 23, 2014

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>

No comments: