Inconsistent datatypes: expected REF got .. error when trying to insert data

1
votes

Long story short what I'm trying to do is to insert some data into a table and I'm having some problems understanding what is wrong. I'll let the code talk for itself. Hope you can help me out. Thanks in advance!

CREATE OR REPLACE TYPE Departament IS OBJECT (
  deptno NUMBER(2),
  dname CHAR(14)
  );
/

CREATE OR REPLACE TYPE Employee IS OBJECT (
  empno NUMBER(4),
  ename CHAR(10),
  dept REF Departament,
  sal NUMBER(7,2)
  ) NOT FINAL;
/

CREATE OR REPLACE TYPE Manager UNDER Employee (
  nrEmp NUMBER(2)
  );
/

CREATE TABLE departament_list AS (SELECT deptno, dname FROM dept);
/

CREATE TABLE manager_list OF Manager;
/

INSERT INTO manager_list VALUES(Manager(7782, 'JOHN', Departament(20, 'TEXAS'), 6000, 2));

Well here is the problem on the last line I get the following error

ORA-00932: inconsistent datatypes: expected REF SYS.DEPARTAMENT got SYS.DEPARTAMENT.

Now don't get me wrong I have tried doing the whole select thingy with: REF(d) from departament_list d ... but I get another error saying incorrect number of arguments for default constructor.

Maybe I'm doing this the wrong way and you can help shed some light on where I'm mistaken. Thanks again!

2
sqloracleplsql
Can you show the version where you used REf(d) and the exact error for that too? – Alex Poole
Shouldn't this line: 'CREATE TABLE departament_list AS (SELECT deptno, dname FROM dept);' be 'CREATE TABLE departament_list AS (SELECT deptno, dname FROM Department'); – Typo
@Typo - from the column names it looks like it's based on the old scott emp/dept schema, not the hr schema that has department? Don't think it's relevant to the issue either way though. – Alex Poole
@AlexPoole I think it's based on the type Department that he's creating above – Typo
It will help knowing on which line of code the exception rises – Typo

2 Answers

2
votes

This seems to be the error reason:

CREATE TABLE departament_list AS (SELECT deptno, dname FROM dept);

Steps which worked for me:

CREATE TABLE departament_list of Departament;

insert into departament_list values (20, 'Texas');

CREATE TABLE manager_list OF Manager;

INSERT INTO manager_list VALUES(Manager(7782, 'JOHN', 
  (select ref(d) from departament_list d where deptno=20), 6000, 2));

From documentation:

The OF clause lets you explicitly create an object table of type object_type. The columns of an object table correspond to the top-level attributes of type object_type. Each row will contain an object instance, and each instance will be assigned a unique, system-generated object identifier when a row is inserted.

0
votes

datatypes are inconsistent, just modify Employee object definition as follows:

CREATE OR REPLACE TYPE Employee IS OBJECT (
  empno NUMBER(4),
  ename CHAR(10),
  dept  Departament,
  sal NUMBER(7,2)
  ) NOT FINAL;
/

And then run insert statement: 

SQL> INSERT INTO manager_list VALUES(Manager(7782, 'JOHN', Departament(20, 'TEXAS'), 6000, 2));

1 row created.

SQL>

Note: in order to update employee object you will need to drop "manager_list" (table) and drop "manager" (type), then update employee object definition and then create "manager_list" and "manager" again.