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.