CODEWITHSUNDEEP
sqloracle-databasetypesoracle10g
Anuja Nehete
Anuja Nehete

Reputation: 71

Retrieve and insert into type objects in oracle

I have created an object type(address-city,state) in Oracle 10g .Then table cust_contact contains field of type address.Can anyone please provide SQL query to insert and retrieve values from this table including the type?

Upvotes: 4

Views: 2751

Answers (3)

Sandeep Kumar
Sandeep Kumar

Reputation: 46

for example : first create type object say as address ,for this synatx or query is used: create type address_ty as object(Street varchar2(50),City char(10),Zip number(6));

now use this address_ty as datatype in the time of table creation for example: create table Example(emp_name varchar2(10),emp_id number(10),address address_ty); this will create table Example having Address as address_ty as a datatype..

Now insert into Values In Example Table Insert Into example Values('Sandeep Kumar',595,address_ty('Snap on sector 126','Noida',201301);

tHANX....

Upvotes: 0

ayokomiz
ayokomiz

Reputation: 1

You can also use the "." syntax when retrieving columns:

select c.contact_address.city from cust_contact c;

Please note that if cust_contact is a table of objects, then you must use the table alias "c".

Upvotes: 0

APC
APC

Reputation: 146239

Selection is easy. Just include the type column in the query projection. Assuming that the ADDRESS column is called contact_address:

select id, contact_name, contact_address
from cust_contact
/

With inserts you need to specify the type in the statement:

insert into cust_contact values
    (some_seq.nextval
     , 'MR KNOX'
     , address(34, 'Main Street', 'Whoville', 'SU')
    )
/

Upvotes: 4

Related Questions