Inconsistent datatypes: expected CHAR got REF

Question

CREATE TYPE empresa_type AS OBJECT (
    CNPJ INTEGER, 
    nome_fantasia VARCHAR2(30), 
    pais VARCHAR2(25),
    fundacao DATE
)

CREATE TYPE funcionario_type AS OBJECT (
    CPF INTEGER, 
    nome VARCHAR2(30), 
    sexo CHAR(1), 
    nasc DATE,
    empresa REF empresa_type
)

CREATE TABLE empresa_tab OF empresa_type (PRIMARY KEY(CNPJ))
CREATE TABLE funcionario_tab OF funcionario_type (PRIMARY KEY(CPF), FOREIGN KEY(empresa) REFERENCES empresa_tab)

report error: ORA-00932: inconsistent datatypes: expected CHAR got REF MY_WKSP.EMPRESA_TYPE

I can't reproduce your error; on my system, running Oracle 12.2.0.1, all statements execute without error. What is your Oracle version? Run select banner from v$version, the first row in the output should tell you your exact Oracle version. — mathguy
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production — Thiago Cavalcanti

MT0 MT0 · Accepted Answer · 2021-08-08T19:23:19

You want to create the table and then add a SCOPE to the reference (rather than trying to define it as a foreign key, because it is not):

CREATE TABLE funcionario_tab OF funcionario_type (
  PRIMARY KEY(CPF)
);

ALTER TABLE funcionario_tab ADD SCOPE FOR ( empresa ) IS empresa_tab;

db<>fiddle here

Inconsistent datatypes: expected CHAR got REF

1 Answers