Re: What is a UDT?

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3F085794.64CF69C_at_exxesolutions.com...
> Oracle 9.2.0.2.0 on Win2K
>
> Got the following error message:
> 'ORA-00932: inconsistent datatypes: expected UDT got CHAR'
> by the following route:
>
> CREATE OR REPLACE TYPE person_typ AS OBJECT (
> ssn NUMBER, name VARCHAR2(30), address VARCHAR2(100))
> NOT FINAL;
> /
>
> CREATE OR REPLACE TYPE person_tab_typ
> AS TABLE OF person_typ;
> /
>
> CREATE OR REPLACE TYPE student_typ UNDER person_typ (
> deptid NUMBER, major VARCHAR2(30))
> NOT FINAL;
> /
>
> CREATE OR REPLACE TYPE student_tab_typ
> AS TABLE OF student_typ;
> /
>
> CREATE TABLE test (
> regular_field DATE,
> person_nested_tab person_tab_typ,
> student_nested_tab student_tab_typ)
> NESTED TABLE person_nested_tab STORE AS per_tab
> NESTED TABLE student_nested_tab STORE AS stu_tab;
>
> INSERT INTO test
> VALUES
> (SYSDATE, person_tab_typ(), student_tab_typ());
>
> INSERT INTO test
> VALUES
> (SYSDATE, person_tab_typ('111223456', 'Morgan', '123 Main Street'),
> student_tab_typ());
>
> Now I know I intentionally tried to stuff a string into a NUMBER column
> so please don't point out the obvious ... but ...
> a search of tahiti and metalink provides not a clue as to the meaning of
> the error message. Any help will be appreicated.
>
> As I ask in the subject ... what is a UDT? And how about it Oracle? How
> about a definition of this term?
>
> Thanks.
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>

looks like they snagged UDT from object oriented terminology. Where you have your primitive data types such as ints, chars, etc... everything else is created by the user. then everything which is created by the programmer.

however, considering that most oracle people dont have Object oriented backgrounds, you would think they would beef up the documentation. Now I remember skimming the Object Type docs, and I think I remember a reference to the term User defined data type, but they really should put 'UDT' somewhere in the documentation to make it easier.

btw, has anyone found the Oracle Object types(other than the arrays and REF cursors) useful at all? I can see nested tables being useful in a datawarehouse environment where you want to denormalize for speed...

but I cant see any reason to use Object Types in the database. why mix a relational design and an object oriented design? Id think it would be harder to maintain.

anyone have a good use for them? Received on Sun Jul 06 2003 - 20:59:24 CDT