Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Abstract datatypes - HELP!!!

Re: Abstract datatypes - HELP!!!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 18 Jan 2000 08:55:32 -0500
Message-ID: <g5s88skp4v36mobs0f894ja6n4srfvt9nn@4ax.com>


A copy of this was sent to alistair.thomson_at_sphinxcst.co.uk (if that email address didn't require changing) On Tue, 18 Jan 2000 09:27:52 GMT, you wrote:

>Hi
>
>I'm using Oracle 8.0.5 Enterprise Edition with the Objects option
>installed (NT 4 platform).
>
>I've created an abstract datatype as:
>
>create type DT_ADDRESS as object
>(street varchar2(20),
>town varchar2(20),
>city varchar2(20),
>postcode varchar2(10));
>
>This works okay and I can create a table using the abstract datatype as:
>
>create table CUSTOMER (
>id varchar2(6),
>customer_name varchar2(20),
>address dt_address);
>
>I can insert data into the customer table :
>
>insert into CUSTOMER values
>(1234,'ACME','DT_ADDRESS('5 mystreet','mytown','mycity','mypostcvode');
>
>When I select data from CUSTOMER this works
>
>select * from CUSTOMER;
>select address.* from customer;
>
>But I cant select an individual column from the abstract datatype eg
>
>select address.street from customer;
>
>It always gives the error
>
>ERROR at line 1:
>ORA-00904: invalid column name
>

You need to use a CORRELATION name to do this. for example:

ops$tkyte_at_8.0> insert into CUSTOMER values   2 (1234,'ACME', DT_ADDRESS('5 mystreet','mytown','mycity','mypostcode') );

1 row created.

ops$tkyte_at_8.0> 
ops$tkyte_at_8.0> 
ops$tkyte_at_8.0> select C.address.city

  2 from customer c
  3 /

ADDRESS.CITY



mycity

Alias customer with C, use c.address.<attribute>

>Can anyone explain whats happening here? Am I right in thinking that
>you CAN select an individual column from an abstract datatype?
>
>Any help would be appreciated. Please reply to
>alistair.thomson_at_sphinxcst.co.uk
>
>Thanks for the help.
>
>Alistair Thomson
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 18 2000 - 07:55:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US