creating a timecode type?

#1

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:

Quote:

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?

--
Mark Harrison
Pixar Animation Studios

#2

mh (AT) pixar (DOT) com wrote:

Quote:

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/B...b14200/toc.htm

How to do it in PL/SQL (contains a MAP function):
http://download.oracle.com/docs/cd/B...61/objects.htm

How to do it in Java (Chapter 5 and 6 are particularly interesting):
http://download-uk.oracle.com/docs/c...b14187/toc.htm

Yours,
Laurenz Albe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

creating a timecode type?

comp.databases.oracle.misc comp.databases.oracle.misc

creating a timecode type? - 02-24-2009 , 01:37 AM

Re: creating a timecode type? - 02-24-2009 , 04:32 AM