Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF




Text description of functions160.gif follows
Text description of sys_typeid


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.

  • 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


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,, 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".