Re: creating a timecode type?
From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 24 Feb 2009 10:32:01 GMT
Message-ID: <1235471518.278425_at_proxy.dienste.wien.at>
mh_at_pixar.com wrote:
>> 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?
Date: 24 Feb 2009 10:32:01 GMT
Message-ID: <1235471518.278425_at_proxy.dienste.wien.at>
mh_at_pixar.com 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: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
How to do it in PL/SQL (contains a MAP function): http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/objects.htm
How to do it in Java (Chapter 5 and 6 are particularly interesting): http://download-uk.oracle.com/docs/cd/B19306_01/java.102/b14187/toc.htm
Yours,
Laurenz Albe
Received on Tue Feb 24 2009 - 04:32:01 CST