Insert my data from Relational Tables to Object-Relational Tables

Question

I tried with the command insert into select... but it didn't work. How can I load the data from the Relational tables to the Object-Relational tables?

Example for table Categories:

insert into CATEGORIES_NEW select * from Categories;

The above worked,but the similar way doesn't work for the other tables.

Below are all the tables:

Relational Tables

    CREATE TABLE Customers (
    CUSTOMERID NUMBER NOT NULL,
    FIRSTNAME VARCHAR2 (20) NOT NULL,
    LASTNAME VARCHAR2 (20) NOT NULL,
    ADDRESS1 VARCHAR2 (40) NOT NULL,
    ADDRESS2 VARCHAR2 (40),
    CITY VARCHAR2 (20) NOT NULL,
    STATE VARCHAR2 (20) NOT NULL,
    ZIP VARCHAR2 (20) NOT NULL,
    COUNTRY VARCHAR2 (20) NOT NULL,
    REGION VARCHAR2 (20) NOT NULL,
    EMAIL VARCHAR2 (20) NOT NULL,
    PHONE VARCHAR2 (20) NOT NULL,
    CREDITCARDTYPE VARCHAR2 (20) NOT NULL,
    CREDITCARD VARCHAR2 (20) NOT NULL,
    CREDITCARDEXPIRATION VARCHAR (10) NOT NULL,
    USERNAME VARCHAR2 (20) NOT NULL,
    "PASSWORD" VARCHAR2 (20) NOT NULL,
    AGE NUMBER NOT NULL,
    INCOME NUMBER NOT NULL,
    GENDER VARCHAR2 (8) NOT NULL,
    PRIMARY KEY (CUSTOMERID));

    CREATE TABLE Orders (
    ORDERID NUMBER NOT NULL,
    ORDERDATE DATE NOT NULL,
    CUSTOMERID NUMBER NOT NULL REFERENCES Customers,
    NETAMOUNT NUMBER NOT NULL,
    TAX NUMBER NOT NULL,
    TOTALAMOUNT NUMBER NOT NULL,
    PRIMARY KEY (ORDERID));

    CREATE TABLE Categories (
    "CATEGORY" NUMBER NOT NULL,
    CATEGORYNAME VARCHAR (20) NOT NULL,
    PRIMARY KEY ("CATEGORY"));

CREATE TABLE Products (
    PROD_ID NUMBER NOT NULL ,
    "CATEGORY" NUMBER NOT NULL REFERENCES Categories,
    TITLE VARCHAR (40) NOT NULL,
    ACTOR VARCHAR (40) NOT NULL,
    PRICE VARCHAR (40) NOT NULL,
    PRIMARY KEY (PROD_ID));

    CREATE TABLE Orderlines (
    ORDERLINEID NUMBER NOT NULL,
    ORDERID NUMBER NOT NULL REFERENCES Orders,
    PROD_ID NUMBER NOT NULL REFERENCES Products,
    QUANTITY NUMBER NOT NULL,
    ORDERDATE VARCHAR2 (80) NOT NULL,
    PRIMARY KEY (ORDERLINEID,ORDERID));

Object-Relational Tables

CREATE TYPE Customer_obj AS OBJECT (
   CUSTOMERID NUMBER, 
   CUST_NAME NAME,
   CUST_ADDRESS ADDRESS_TYPE,
   CUST_CONTACT CONTACT_INFO,
   CUST_CREDITCARD CREDITCARD_INFO,
   CUST_PERSONALINFO PERSONAL_INFO  
);

CREATE TYPE NAME AS OBJECT (
     FIRSTNAME VARCHAR2(80) , 
     LASTNAME VARCHAR2(80) 
  );


CREATE TYPE ADDRESS_TYPE AS OBJECT (
    ADDRESS VARCHAR2(280) , 
    CITY VARCHAR2(80) , 
    STATE VARCHAR2(20), 
    ZIP VARCHAR2(40) ,
  COUNTRY VARCHAR2(40) ,
  REGION VARCHAR2(40)
  );


CREATE TYPE CONTACT_INFO AS OBJECT (
  EMAIL VARCHAR2(40),
    PHONE VARCHAR2(40) 
  );



CREATE TYPE CREDITCARD_INFO AS OBJECT (
 CREDITCARDTYPE VARCHAR2(40) , 
 CREDITCARD VARCHAR2(40) ,
 CREDITCARDEXPIRATION VARCHAR2(40) 
  );


CREATE TYPE PERSONAL_INFO AS OBJECT (    
     USERNAME VARCHAR2(80) ,
     PASSWORD VARCHAR2(80) ,
     AGE NUMBER ,
     INCOME NUMBER ,
     GENDER VARCHAR(20)
  );




CREATE TABLE CUSTOMERS_NEW OF Customer_obj(
PRIMARY KEY (CUSTOMERID));



CREATE TYPE Categories_obj AS Object(
    CATEGORY NUMBER ,
    CATEGORYNAME VARCHAR2(80) );

CREATE TABLE CATEGORIES_NEW OF Categories_obj(
  PRIMARY KEY (CATEGORY));


CREATE TYPE Orders_obj AS Object(
  ORDERID NUMBER , 
    ORDERDATE VARCHAR2(400), 
  CUSTOMERID NUMBER,
    NETAMOUNT NUMBER ,
  TAX NUMBER,
  TOTALAMOUNT NUMBER
    );

CREATE TABLE ORDERS_NEW OF Orders_obj(
  PRIMARY KEY (ORDERID),
  FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMERS_NEW 
  );

CREATE TYPE Products_obj AS Object(
  PROD_ID NUMBER ,
  CATEGORY NUMBER ,
  TITLE VARCHAR2 (40) ,
  ACTOR VARCHAR2 (40) ,
  PRICE VARCHAR2 (40)
  );

CREATE TABLE PRODUCTS_NEW OF Products_obj(
  PRIMARY KEY (PROD_ID),
  FOREIGN KEY (CATEGORY) REFERENCES CATEGORIES_NEW 
  );

CREATE TYPE Orderlines_obj AS Object(
  CUST_ID_INFO ID_INFO,
  QUANTITY NUMBER,
  ORDERDATE VARCHAR2 (80)
  );


CREATE TYPE ID_INFO AS Object (
  ORDERLINEID NUMBER ,
  ORDERID NUMBER ,
  PROD_ID NUMBER 
  );


CREATE TABLE ORDERLINES_NEW OF Orderlines_obj(
PRIMARY KEY (CUST_ID_INFO.ORDERLINEID,CUST_ID_INFO.ORDERID),
FOREIGN KEY (CUST_ID_INFO.ORDERID) REFERENCES ORDERS_NEW,
FOREIGN KEY (CUST_ID_INFO.PROD_ID) REFERENCES PRODUCTS_NEW
);

Answer 1

Object tables are tricky, and probably not worth the effort. But anyway.

You're working with objects, which means you need to instantiate them. When the object table is defined on a type which just has scalar attributes Oracle is clever enough to implicitly instantiate the object. But when the Type is complex, with attributes which are themselves Types ypu need to instantiate them explicitly.

Here is the insert for CUSTOMERS_NEW

insert into CUSTOMERS_NEW 
select Customer_obj (
        c.CUSTOMERID , 
        c.FIRSTNAME||' '||c.LASTNAME ,
        ADDRESS_TYPE(
            c.ADDRESS1||' ' ||c.ADDRESS2 , 
            c.CITY  , 
            c.STATE , 
            c.ZIP  ,
            c.COUNTRY ,
            c.REGION
        ),
        CONTACT_INFO(
            c.EMAIL,
            c.PHONE 
        ),
        CREDITCARD_INFO (
            c.CREDITCARDTYPE  , 
            c.CREDITCARD  ,
            c.CREDITCARDEXPIRATION  
        ),
        PERSONAL_INFO  (    
            c.USERNAME,
            c.PASSWORD,
            c.AGE  ,
            c.INCOME  ,
            c.GENDER 
    )
)
from customers ;