Home » SQL & PL/SQL » SQL & PL/SQL » how to use custom datatype in SQL ??? (Oracle 9i)
how to use custom datatype in SQL ??? [message #329802] Thu, 26 June 2008 10:40 Go to next message
Bonita
Messages: 32
Registered: June 2008
Member
I suppose my question is pretty common but didn't find answer . Any hints would be highly appreciated !

The Oracle 9i has one exemplary table

Table :CUSTOMERS

Field Datatype
CUSTOMER_ID .... NUMBER(6) not null,
CUST_FIRST_NAME ..... VARCHAR2(20),
CUST_LAST_NAME ..... VARCHAR2(20),
CUST_ADDRESS ..... CUST_ADDRESS_TYP
…..
./fa/4526/0/

CUST_ADDRESS is CUST_ADDRESS_TYPE, and it comes from type

CREATE OR REPLACE TYPE cust_address_typ
AS OBJECT
( street_address ..... VARCHAR2(40)
, postal_code ..... VARCHAR2(10)
, city ...... VARCHAR2(30)
, state_province ...... VARCHAR2(10)
, country_id ...... CHAR(2) );



SELECT CSUTOMER_ID, CUST_ADDRESS FROM CUSTOMERS
shows
CUSTOMER_ID, CUST_ADDRESS .STREE_ ADDRESS,CUST_ADDRESS .POSTAL_CODE,CUST_ADDRESS .CITY,CUST_ADDRESS .STATE_PROVINCE, CUST_ADDRESS .COUNTRY_ID.

Question 1

What should SQL be in order to show Customer ID and CUST_ADDRESS .STREE_ ADDRESS only ?

SELECT CUSTOMER_ID, CUST_ADDRESS .STREE_ ADDRESS doesn't work.

Question 2

Can I use CUST_ADDRESS as indepentent table ? How do I open it ( such as Select .. ) in SQL ?

Question 3

If I can not use CUST_ADDRESS as independent table, how do I create table such as CUST_address2 and feed all data from CUST_ADDRESS ?

Hope my question is clear. Thank you very much.
Re: how to use custom datatype in SQL ??? [message #329804 is a reply to message #329802] Thu, 26 June 2008 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Syntax for Schema Objects and Parts in SQL Statements
Subsection Referring to Object Type Attributes and Methods

Regards
Michel

Re: how to use custom datatype in SQL ??? [message #329813 is a reply to message #329804] Thu, 26 June 2008 11:02 Go to previous message
Bonita
Messages: 32
Registered: June 2008
Member
Many thanks. Michel. It works for me.Thanks again. Laughing

Previous Topic: Any idea why INTERSECT ALL is not working here
Next Topic: Hint CHOOSE
Goto Forum:
  


Current Time: Mon Dec 05 10:44:43 CST 2016

Total time taken to generate the page: 0.10966 seconds