How can I insert the data to the cars table if it's referenced to the customer table?

1
votes
CREATE TYPE Customer_t AS OBJECT 
(
    cid char(6),
    name varchar(15),
    birthdate date,
    phone char(10),
    address varchar(50)  
);

CREATE TABLE Customer OF Customer_t 
(
    cid PRIMARY KEY 
);

CREATE TYPE Car_t AS OBJECT 
(
    regno char(9),
    make varchar(12),
    model varchar(10),
    mdate date,
    owner REF Customer_t,
    value number(8,2)
);
/

CREATE TABLE Cars OF Car_t (regno PRIMARY KEY );

INSERT INTO Cars (regno, make, model, mdate, owner, value)
VALUES ('car1', 'german','honda',
  TO_DATE('2000/12/16 12:00:00', 'yyyy/mm/dd hh:mi:ss'), 
  'c1', 8000 );

Error :

SQL Error: ORA-00932: inconsistent datatypes: expected REF got CHAR 00932. 00000 - "inconsistent datatypes: expected %s got %s"

1
sqldatabaseoracleoracle11g
By the way, varchar2 is the standard short string type in Oracle. char is just a weird ANSI compatibility feature there to waste space and create bugs. – William Robertson

1 Answers

1
votes

First, you need to have a customer already created, in order to reference it. Then you include the reference in the INSERT statement. See below:

CREATE TYPE Customer_t AS OBJECT (
  cid  char(6),
  name  varchar(15) ,
  birthdate  date ,
  phone char(10),
  address varchar(50)  
);

CREATE TABLE Customer OF Customer_t (
             cid      PRIMARY KEY );

insert into customer values (customer_t('cl1234', 'Peter', 
  to_date('1990-05-04', 'YYYY-MM-DD'), '1234567890', 
  '123 Maple St'));

CREATE TYPE Car_t AS OBJECT (
  regno char(9),
  make varchar(12) ,
  model varchar(10) ,
  mdate date,
  owner REF  Customer_t,
  value number(8,2)
  );

CREATE TABLE Cars OF Car_t (
            regno      PRIMARY KEY );

insert into cars values (
  car_t('reg123456', 'honda', 'accord',
    to_date('2000-12-16 12:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    (select ref(cu) from customer cu where cid = 'cl1234'),
    8000)
  );