Home Error report – ORA-02330: datatype specification not allowed 02330. 00000 – when creating object relational table

Questions

Error report – ORA-02330: datatype specification not allowed 02330. 00000 – when creating object relational table

July 7, 2022

I was trying to create two object-relational tables and wanted to create a foreign key in the department table to reference the primary key of the employee table.
It throws "ORA-02330: datatype specification not allowed" when I try to create the DEPARTMENT TABLE when I try to create the department table.

CREATE or Replace TYPE EMP_TYPE AS OBJECT
 (
EMP_ID INTEGER,
EMP_FNAME VARCHAR2(20),
EMP_LNAME VARCHAR2(25),
ADDRESS VARCHAR(30),
SALARY   NUMBER (10),
JOB_TITLE   VARCHAR2 (20));

CREATE TABLE EMPLOYEE OF EMP_TYPE (EMP_ID PRIMARY KEY);

CREATE OR REPLACE TYPE DEPT_TYPE AS OBJECT(
DEPT_ID INTEGER,
EMP_ID INTEGER,
DEPT_NAME VARCHAR(20),
LOCATION VARCHAR(20));

CREATE TABLE DEPARTMENT OF DEPT_TYPE (EMP_ID FOREIGN KEY REFERENCES EMPLOYEE(EMP_ID))

The last line is the one that throws the error

>Solution :

Wrong syntax. Should be

SQL> CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT
  2  (
  3     EMP_ID INTEGER,
  4     EMP_FNAME VARCHAR2 (20),
  5     EMP_LNAME VARCHAR2 (25),
  6     ADDRESS VARCHAR (30),
  7     SALARY NUMBER (10),
  8     JOB_TITLE VARCHAR2 (20)
  9  );
 10  /

Type created.

SQL> CREATE TABLE EMPLOYEE OF EMP_TYPE
  2  ( EMP_ID PRIMARY KEY );

Table created.

SQL> CREATE OR REPLACE TYPE DEPT_TYPE AS OBJECT
  2  (
  3     DEPT_ID INTEGER,
  4     EMP_ID INTEGER,
  5     DEPT_NAME VARCHAR (20),
  6     LOCATION VARCHAR (20)
  7  );
  8  /

Type created.

SQL> CREATE TABLE DEPARTMENT OF DEPT_TYPE
  2  ( EMP_ID REFERENCES EMPLOYEE (EMP_ID) );

Table created.

SQL>