不一致的数据类型：尝试插入数据时预期的REF得到..错误

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.

ORA-00932:不一致的数据类型:预期的REF SYS.DEPARTAMENT得到了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.

现在不要误会我的意思我已经尝试用以下内容完成整个选择:REF(d)来自departament_list d ...但我得到另一个错误,说明默认构造函数的参数数量不正确。

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

也许我这样做是错误的,你可以帮我解释一下我错的地方。再次感谢!

2 个解决方案

#1

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.

OF子句允许您显式创建object_type类型的对象表。对象表的列对应于object_type类型的*属性。每行将包含一个对象实例,并且在插入行时将为每个实例分配一个唯一的系统生成的对象标识符。

#2

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

数据类型不一致,只需修改Employee对象定义,如下所示:

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:

然后运行insert语句:

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.

注意:为了更新员工对象,您需要删除“manager_list”(表)并删除“manager”(类型),然后更新员工对象定义,然后再次创建“manager_list”和“manager”。

#1