Re: Trouble with user-defined types

From: Hakan <heren_at_home.com>
Date: 2000/07/18
Message-ID: <3974A3D4.56C8A9EC@home.com>#1/1

Hi,

the trick is to use an alias for the table.

SQLWKS> select * from v$version

     2> /
BANNER                                                          
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production      
PL/SQL Release 8.1.6.0.0 - Production                           
CORE	8.1.6.0.0	Production                                       
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production          
NLSRTL Version 3.4.1.0.0 - Production                           

5 rows selected.

SQLWKS>  create type CONTACT_TY as object (
     2>  Name VARCHAR2(30),
     3>  Email VARCHAR2(30))
     4>  /

Statement processed.
SQLWKS> create table MY_TABLE (Contact CONTACT_TY)

     2> /
Statement processed.
SQLWKS> insert into MY_TABLE values
(CONTACT_TY('John','jsmith_at_unix.sprint.net'))

     2> /
1 row processed.
SQLWKS> commit
Statement processed.
SQLWKS> /
SQLWKS> select a.Contact.Name from MY_TABLE a; CONTACT.NAME

---

John
1 row selected.
SQLWKS> regards

Hakan

Note: Learning is ok, but implementing is a different story.

      What I mean is that There is a limitation with objects option.
      Let's say you have 20 objects depending on another object.
      If you want to change this object you need to drop 20 objects
      then make the change then create those 20 objects again. good

luck.

John Smith wrote:
>
> The setup: I'm fairly new to Oracle and such, so please bear with me :) I'm
> learning about objects at the moment, following some examples from the
> Oracle 8 Complete Reference. The examples from the book don't work either,
> but for the sake of brevity I'll show you the stuff I made myself
>
> create type CONTACT_TY as object (
> Name VARCHAR2(30),
> Email VARCHAR2(30));
> /
>
> then:
>
> create table MY_TABLE (Contact CONTACT_TY);
>
> then:
>
> insert into MY_TABLE values (CONTACT_TY('John','jsmith_at_unix.sprint.net'));
>
> Now, I describe the table and look at the types attributes from the data
> dictionary, and all looks ok. When I try to get at the data though....
>
> select Contact.Name from MY_TABLE;
>
> I get this error:
> ERROR at line 1:
> ORA-00904: invalid column name
>
> ..with a star sitting directly below the N in Name. What am I doing wrong?
> This is on version 8.1.5 on Solaris.
  Received on Tue Jul 18 2000 - 00:00:00 CDT