Re: What is a UDT?

On Sun, 06 Jul 2003 10:08:36 -0700, Daniel Morgan <damorgan_at_exxesolutions.com> wrote:

>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:

 A UDT is a User Defined Type.

>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());

 Are you getting an error with this? This looks OK.

>INSERT INTO test
>VALUES
>(SYSDATE, person_tab_typ('111223456', 'Morgan', '123 Main Street'),
>student_tab_typ());

 That would give the error, though.

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

 The error is referring to an inconsistent datatype; but as with several of Oracle's error messages it unfortunately does not say which column.

 It appears to be the second column, person

 person_nested_tab is of type person_tab_typ.

 person_tab_type is of type TABLE OF person_type.

 You've passed the following:

person_tab_typ('111223456', 'Morgan', '123 Main Street')

 That's constructor syntax for a table, given the values to put into it. However you're now trying to construct three person_typ objects, using three strings, hence the message - inconsistent datatypes, expected UDT (i.e. the user-defined type person_typ), got CHAR (i.e. '111223456').

 Do you mean instead:

person_tab_typ(person_typ('111223456', 'Morgan', '123 Main Street'))

 i.e., a table containing one person_typ object, containing those three values for its fields.

SQL> INSERT into test
  2 VALUES (SYSDATE, person_tab_typ(person_typ('111223456', 'Morgan', '123 Main Street')), student_tab_typ());

1 row inserted

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)