Re: creating a timecode type?

From: Laurenz Albe <>
Date: 24 Feb 2009 10:32:01 GMT
Message-ID: <> wrote:
> Here's a question from a coworker.  The data type is a video timecode,
> which is a standard used in video cameras, video editing programs, and
> video players such as quicktime.
> I've never created a custom type before, so I'm hoping to get
> some good advice for him here... thanks!
> My coworker writes:

>> We'd like to store time values in Oracle like this:
>> numerator int64
>> denominator int64
>> flags byte (normal, infinite, indeterminate)
>> Ideally we would also like to perform logical operations like =, < and >
>> on them.
>> I imagine three paths forward:
>> 1. Encode them into a string and use custom functions to perform
>> comparison operations.
>> 2. Encode them into three separate fields and use custom functions to
>> perform comparison operations.
>> 3. Tell Oracle about this new data type at a lower level.
>> I can imagine that someone else in the world has wanted to add timecode,
>> or complex numbers or astronomical positions or some other wacky data
>> type - does Oracle have a nice solution for this?

You can create a custom object type with the CREATE TYPE statement.

For comparisons, define a MAP member function.

Code for object type member functions is typically written in PL/SQL or Java.

Some pointers (for Oracle 10.2):

CREATE TYPE and CREATE TYPE BODY in the SQL Documentation:

How to do it in PL/SQL (contains a MAP function):

How to do it in Java (Chapter 5 and 6 are particularly interesting):

Laurenz Albe Received on Tue Feb 24 2009 - 04:32:01 CST

Original text of this message