create a data type that will only hold the values 0 and 1
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
A Solution Using User Defined Data Types
Oracle provides a large range of built-in data types but still there are times when we need to define our own as a way of best constraining the integrity of our data.

Oracle's SIGNTYPE is great in that it will accept only integer values of -1, 0, and 1. But often, I find, that what I really want is a variable that will only hold two values, 0 and 1, that I can use as a switch.

If you've always wanted to do this too ... here is a demo that shows how to do it.
set serveroutput on

DECLARE
 SUBTYPE flagtype IS PLS_INTEGER RANGE 0..1;
 x flagtype;
BEGIN
  FOR i IN -2 .. 2 LOOP
    BEGIN
      x := i;
      dbms_output.put_line('Success: ' || TO_CHAR(x));
    EXCEPTION
      WHEN others THEN
        dbms_output.put_line('Can not assign ' || TO_CHAR(i) || ' To Flagtype');
    END;
  END LOOP;
END;
/
Could it be easier?

Now lets extend this as a way of enforcing a business rule using the airplanes table demo table. If you haven't downloaded it, already, you can do so now [here].

The test we will do will generate 50,000 exceptions. First we will use the traditional method
DECLARE
 CURSOR ln_cur IS
 SELECT line_number, COUNT(*) testval
 FROM airplanes
 GROUP BY line_number;

 too_many_planes EXCEPTION;
BEGIN
  FOR ln_rec IN ln_cur LOOP
    BEGIN
      IF ln_rec.testval > 4 THEN
        RAISE too_many_planes;
      END IF;
    EXCEPTION
      WHEN too_many_planes THEN
        NULL;
    END;
  END LOOP;
END;
/
Now lets try handling it with a defined data type.
DECLARE
 SUBTYPE testtype IS PLS_INTEGER RANGE 1..4;
 x testtype;

 CURSOR ln_cur IS
 SELECT line_number, COUNT(*) testval
 FROM airplanes
 GROUP BY line_number;
BEGIN
  FOR ln_rec IN ln_cur LOOP
    BEGIN
      x := ln_rec.testval;
    EXCEPTION
      WHEN others THEN
        NULL;
    END;
  END LOOP;
END;
/
Which one is better I will leave for others to consider. On my demo laptop they time out almost identically. The subtype being very slightly faster. But what I like about using the subtype is that it more naturally treats a business rule violation as an exception.
 
Related Topics
Data Types
Types
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved