Functions, 126 of 177

SYS_TYPEID

Syntax

sys_typeid::=

Text description of functions160.gif follows

Purpose

SYS_TYPEID returns the typeid of the most specific type of the operand. This value is used primarily to identify the type-discriminant column underlying a substitutable column. For example, you can use the value returned by SYS_TYPEID to build an index on the type-discriminant column.

Notes:

Use this function only on object type operands.
All final root object types--that is, final types not belonging to a type hierarchy--have a null typeid. Oracle assigns to all types belonging to a type hierarchy a unique non-null typeid.

See Also:

Oracle9i Application Developer's Guide - Object-Relational Features for more information on typeids

Examples

The following examples use the tables persons and books, which are created in "Substitutable Table and Column Examples". Both tables in turn use the person_t type, which is created in "Type Hierarchy Example". The first query returns the most specific types of the object instances stored in the persons table.

SELECT name, SYS_TYPEID(VALUE(p)) "Type_id" FROM persons p;

NAME                      Type_id
------------------------- --------------------------------
Bob                       01
Joe                       02
Tim                       03

The next query returns the most specific types of authors stored in the table books:

SELECT b.title, b.author.name, SYS_TYPEID(author)
   "Type_ID" FROM books b;

TITLE                     AUTHOR.NAME          Type_ID
------------------------- -------------------- -------------------
An Autobiography          Bob                  01
Business Rules            Joe                  02
Mixing School and Work    Tim                  03

You can use the SYS_TYPEID function to create an index on the type-discriminant column of a table. For an example, see "Indexing on Substitutable Columns: Examples".