How to insert records into Object table from the relational table

[Guest_imported]

Hi All!

I have the following object type:

create type address_ty as object
( street varchar2(50),
city varchar2(25),
state char(2),
zip number
)

create type person_ty as object
( name varchar2(25),
address address_ty
)

Based on the above data type, I have created the following object table.

create table customer
( customer_id number,
person person_ty
)

There is another relational table named "customer". I want to insert the records into the above object-relational table "customer" from the relational table "customer" which is in some other schema. How to do it?. The normal style will not work. Any suggestion from anyone?.

The structure of the relational table is as follows:

SQL> desc customer
Name Null? Type
------------------------------ -------- -----------------
CUSTID NUMBER(6)
NAME VARCHAR2(45)
STREET VARCHAR2(40)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP VARCHAR2(9)

Thanks in advance.....

 

[KeepItSimpleEngr]

Hi....

You can insert into an object table using VALUES(...) and the default constructor for the objects.

DECLARE
  CURSOR another_table.....;
BEGIN
  FOR record IN another_table LOOP
    INSERT INTO customer
    VALUES( record.id
          , person_ty( record.name
                     , address_ty( record.street
                                 , record.city
                                 , record.state
                                 , record.zip
                                 )
                      )
           ) ;
  END LOOP ;
  COMMIT ;
END ;
/

More details at....

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a77069/09_objs.htm#4643

Hope this helps. Good Luck.