Home » RDBMS Server » Server Utilities » Loading subtype with ref (ORA-22979)
Loading subtype with ref (ORA-22979) [message #207097] Mon, 04 December 2006 02:40
tlahyani
Messages: 20
Registered: November 2006
Junior Member
Hello -

I have the following types:

CREATE OR REPLACE TYPE DepartmentType AS OBJECT (
   dno          NUMBER,
   name         VARCHAR2(20),
   budget       NUMBER
);
/


CREATE OR REPLACE TYPE PersonType AS OBJECT (
   id           NUMBER,
   name         VARCHAR2(20),
   birthdate    DATE
)NOT INSTANTIABLE NOT FINAL;
/


CREATE OR REPLACE TYPE EmployeeType UNDER PersonType (
   worksIn      REF     DepartmentType,
   dateHired    DATE,
   status       NUMBER
);
/


And the following tables:
CREATE TABLE DepartmentTab OF DepartmentType
(PRIMARY KEY (dno))
 OBJECT ID PRIMARY KEY
/

CREATE TABLE PersonTab OF PersonType
(PRIMARY KEY (id))
OBJECT ID PRIMARY KEY
/


I need to load a file that contains employee data into the personTab table, but I get this error:

Record 1: Rejected - Error on table PERSONTAB.
ORA-22979: cannot INSERT object view REF or user-defined REF

I believe it is caused by the column worksIn in the employeeType, which is a REF to department. This column is not declared as a foreign key in personTab, because personTab is of personType not employeeType.
Any idea how to work around this??

Here are the control files:
LOAD DATA
INFILE './deps.cls'
INTO TABLE departmentTab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

          ( dno         INTEGER EXTERNAL(3),
            name        CHAR(20),
            budget      INTEGER EXTERNAL(12))


LOAD DATA
        INFILE './emps.cls'
        INTO TABLE personTab
        TREAT AS EmployeeType
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

          ( id          INTEGER EXTERNAL(8),
            name        CHAR(20),
            birthdate   "TO_DATE(:birthdate, 'MM/DD/YYYY')",
            worksin     REF (CONSTANT 'DEPARTMENTTAB', dno),
            dno         FILLER INTEGER EXTERNAL(5),
            datehired   "TO_DATE(:datehired, 'MM/DD/YYYY')",
            status      INTEGER EXTERNAL(2))


and sample data:
deps.cls:
1,"Biology",100000,
2,"German",200000,

emps.cls:
1,"Scott Jobs","8/12/1959",1,"11/10/1989", 4,
2,"Samir Barak","3/1/1963",1,"8/02/1985",6,



Thanks,
Previous Topic: Loading table with subtypes from multiple files
Next Topic: Execute an OS command using dbms_job
Goto Forum:
  


Current Time: Sun Dec 04 21:00:23 CST 2016

Total time taken to generate the page: 0.08786 seconds