ORA-00932: inconsistent datatypes error when inserting

Question

I get this error:

ORA-00932: inconsistent datatypes: expected REF ADDRESS_TYPE got ADDRESS_TYPE

This is my CREATE script:

CREATE OR REPLACE TYPE address_type AS OBJECT
(
    house_name VARCHAR2(20),
    street     VARCHAR2(20),
    city       VARCHAR2(20),
    postcode   VARCHAR2(9)
)
/

CREATE TABLE addresses OF address_type;

CREATE TABLE customers
(
    customer_id NUMBER(6),
    title       VARCHAR2(5),
    firstname   VARCHAR2(20),
    surname     VARCHAR2(20),
    telephone   VARCHAR2(12), --VARCHAR2 used as customer may use +44
    address     REF address_type SCOPE IS addresses
);

This is my Insert script:

INSERT INTO customers (customer_id, title, firstname, surname, telephone, address)
VALUES (1, 'mr', 'ross', 'xd', '01234881632', 
        address_type('exdee', 'may road', 'lurvey', 'rs34 8dt'));

I have tried putting REF address_type in the insert table but that didn't work, it just asked for a comma. I have also tried removing the REF from the create table. I'm not sure what I have done wrong here, would appreciate some help.

Possible duplicate of Inconsistent datatypes: expected REF got .. error when trying to insert data — tk421

eaolson eaolson · Accepted Answer · 2018-04-14T01:05:47

You don't need the REF or SCOPE IS address. Just change your line to:

address     address_type

SQLFiddle

ORA-00932: inconsistent datatypes error when inserting

1 Answers