Timespan – Creating custom data types in Oracle

20 05 2009

This is the Oracle counterpart to my original post on creating a timespan data type in Postgres. I’m not going to rehash what a timespan is or why and where you would use it. But quickly, we will be adding a custom data type to model a span of time. Your work shift starts at x and ends at y and lasted z.

Oracle is a little more object-relational than Postgres when it comes to data types. Here, in typical OOP fashion, we can define a data type that has attributes, member functions, static functions and custom constructors.

Creating a custom type is in many ways like creating a package with a spec and body.

--spec
CREATE OR REPLACE TYPE timespan AS OBJECT(
start_time     DATE,
end_time       DATE,

CONSTRUCTOR FUNCTION timespan(
p_start    DATE,
p_dur      NUMBER
) RETURN SELF AS RESULT,

MAP MEMBER FUNCTION get_start_time RETURN DATE,
MEMBER FUNCTION to_char(
fmt VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2
-- ** Snip ** the rest of the functions are excluded to save space
-- If you actually try to create this type, you'll have to copy
-- the function signatures from the body
)

Ok, so there are a few things to note here. When you create a custom type, Oracle creates a default constructor and will not allow you to override it. (If anyone knows how to do that please let me know.)
Oracle uses the MAP function to convert a complex type into a scalar value that it can sort on and order by.

CREATE OR REPLACE TYPE BODY timespan AS 

   /********************
   * Custom constructors
   *********************/

   CONSTRUCTOR FUNCTION timespan(
     p_start  DATE,
     p_dur    NUMBER
   ) RETURN SELF AS RESULT AS

   BEGIN
     IF p_dur >= 0 THEN
        SELF.start_time := p_start;
        SELF.end_time   := p_start + (p_dur / 86400);
     ELSE
        SELF.start_time := p_start + (p_dur / 86400);
        SELF.end_time   := p_start;
     END IF;
     RETURN;
   END;

   /*SNIP */
END;

Here is the complete implementation of type spec and body.

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: