Re: Problem with object types

The solution is to use a table alias. In the following example, TT is the table alias.

select TT.address.street from tablename TT;

Jonathan Lewis from the UK provided me with this solution. Jonathan says that you must use a table alias. His exact words were:

"...select statement is missing its 'corelation variable' which is the object-term for the table-alias."

I was surprised that the solution was so simple. I was equally surprised that my code did not work without the table alias, since I got this code out of a book from Oracle Press:

"Oracle 8: The Complete Reference"
by: Koch and Loney

This book covers both versions 7 and 8. Perhaps the need for table aliases when selecting objects is new in version 8. Does anyone know if this is the case?

Chris

Christopher Maher wrote:

> I am running Oracle 8i on an NT box with 128 MB of RAM.
>
> I have created an object type called ADDRESS_TY and used it as the data
> type of the ADDRESS column in a table. I have been able to insert rows
> of data into the table. I have also creeated an Oracle Form that
> displays the table and accepts data entry perfectly.
>
> My problem is with SQL. When I use select to list an attribute within
> the object type, I get the error message ORA-00904: invalid column name.
>
> My object type ADDRESS_TY has four attributes, one of them is STREET. I
> am separating the column from the attribute with a period as required.
> Why would the following syntax cause an error?
>
> select ADDRESS.STREET from TABLENAME;
>
> When I ask for all columns, the select runs Ok and lists the data
> contents of all of the object type’s attributes:
>
> select * from TABLENAME;
>
> Any help would be greatly appreciated. Thanks.
>
> Chris
Received on Mon Mar 27 2000 - 00:00:00 CST