Restore and insert type objects in oracle

Question

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

Answer 1

The choice is simple. Just specify the type column in the query. Assuming the ADDRESS column is called contact_address:

select id, contact_name, contact_address from cust_contact / 

With the help of inserts you need to specify the type in the instruction:

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

Answer 2

You can also use "." The syntax for extracting columns is:

select c.contact_address.city from cust_contact c;

Note that if cust_contact is an object table, you must use table alias "c".

Answer 3

for example: first create an object of type called an address; for this syntax or query, use: create an address_ty type as an object (street varchar2 (50), City char (10), Postal number (6));

now use this address_ty as a data type during table creation for example: create table Example (emp_name varchar2 (10), number emp_id (10), address address_ty); this will create an Example table having an Address as address_ty as a data type ..

Now paste the values ​​into the example table; Insert values ​​into the example ('Sandeep Kumar', 595, address_ty ('Snap on sector 126', 'Noida', 201301);

Thanx ....