Home » RDBMS Server » Server Utilities » Multiple references to same table in control file
Multiple references to same table in control file [message #204027] Fri, 17 November 2006 06:30 Go to next message
tlahyani
Messages: 20
Registered: November 2006
Junior Member
Hello -
Ihave this control file:

LOAD DATA
INFILE './TAs.cls'
INTO TABLE TATab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

          ( id          INTEGER EXTERNAL(8),
            name        CHAR(20),
            street      CHAR(20),
            city        CHAR(20),
            state       CHAR(20),
            zipcode     CHAR(5),
            birthdate   "TO_DATE(:birthdate, 'MM/DD/YYYY')",
            worksin     REF (CONSTANT 'DEPARTMENTTAB', dno),
            dno         FILLER INTEGER EXTERNAL(5),
            datehired   "TO_DATE(:birthdate, 'MM/DD/YYYY')",
            status      INTEGER EXTERNAL(2),
            semestersalary      INTEGER EXTERNAL(5),
            appfraction FLOAT EXTERNAL,
            studentid   INTEGER EXTERNAL(10),
            major       REF (CONSTANT 'DEPARTMENTTAB', dno),
            dno          FILLER INTEGER EXTERNAL(5),
            advisor     REF (CONSTANT 'PROFESSORTAB', pid),
            pid         FILLER INTEGER EXTERNAL(5) )



for the following table:

ID 	NOT NULL 	NUMBER
NAME 	  	VARCHAR2(20)
STREET 	  	VARCHAR2(20)
CITY 	  	VARCHAR2(20)
STATE 	  	VARCHAR2(20)
ZIPCODE 	  	VARCHAR2(5)
BIRTHDATE 	  	DATE
WORKSIN 	  	REF OF DEPARTMENTTYPE
DATEHIRED 	  	DATE
STATUS 	  	NUMBER
SEMESTERSALARY 	  	NUMBER
APPFRACTION 	  	FLOAT(126)
STUDENTID 	  	VARCHAR2(10)
MAJOR 	  	REF OF DEPARTMENTTYPE
ADVISOR 	  	REF OF PROFESSORTYPE


When run sqlldr, it gives me the following error:

SQL*Loader-404: Column DNO present more than once in TATAB's INTO TABLE block.

That is because the two fillers for the department refs are both named dno, which is the primary key for the department table.
When I change the second reference name to something else, I get this one:

ORA-22979: cannot INSERT object view REF or user-defined REF

Does it think that the second Ref is user-defined ref column because that name does not match the primary key? Any workarounds? Thanks,
Re: Multiple references to same table in control file [message #204204 is a reply to message #204027] Sat, 18 November 2006 22:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
The names for the fillers in the control file need not match the names of the ref columns. So, you need to change it to something like below.

LOAD DATA
INFILE './TAs.cls'
INTO TABLE TATab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

( id INTEGER EXTERNAL(8),
name CHAR(20),
street CHAR(20),
city CHAR(20),
state CHAR(20),
zipcode CHAR(5),
birthdate "TO_DATE(:birthdate, 'MM/DD/YYYY')",
worksin REF (CONSTANT 'DEPARTMENTTAB', dno1),
dno1 FILLER INTEGER EXTERNAL(5),
datehired "TO_DATE(:birthdate, 'MM/DD/YYYY')",
status INTEGER EXTERNAL(2),
semestersalary INTEGER EXTERNAL(5),
appfraction FLOAT EXTERNAL,
studentid INTEGER EXTERNAL(10),
major REF (CONSTANT 'DEPARTMENTTAB', dno2),
dno2 FILLER INTEGER EXTERNAL(5),
advisor REF (CONSTANT 'PROFESSORTAB', pid),
pid FILLER INTEGER EXTERNAL(5) )




Re: Multiple references to same table in control file [message #204669 is a reply to message #204027] Tue, 21 November 2006 10:52 Go to previous messageGo to next message
tlahyani
Messages: 20
Registered: November 2006
Junior Member
Hi Barbara -

At the bottom of my message I mention that when I change the name of one of the fillers, I get the following error:

ORA-22979: cannot INSERT object view REF or user-defined REF

Here is some sample data:

21,"studentName21","xxxx_streetname","city_name","North_Carolina","37901","3/17/1954",:1,"4/11/1959",2,17000,0.750000,514841697,10,287,

22,"studentName22","xxxx_streetname","city_name","Oregon","5579","10/16/1959",:1,"9/11/1988",5,11000,0.550000,620696577,1,15,

Thanks,
Re: Multiple references to same table in control file [message #204728 is a reply to message #204669] Tue, 21 November 2006 21:53 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
I would need to see your create table statements. Also, ":1" is not a valid numerical value. I just did a brief test of only the relevant fields below.

-- tas.cls:
1,11,21,
2,12,22,


-- test.ctl:
LOAD DATA
INFILE 'tas.cls'
INTO TABLE TATab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( id          INTEGER EXTERNAL(8),
worksin     REF (CONSTANT 'DEPARTMENTTAB', dno1),
dno1        FILLER INTEGER EXTERNAL(5),
major       REF (CONSTANT 'DEPARTMENTTAB', dno2),
dno2        FILLER INTEGER EXTERNAL(5))


 
SCOTT@10gXE> CREATE OR REPLACE TYPE departmenttype AS OBJECT
  2    (dno NUMBER);
  3  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE tatype AS OBJECT
  2    (id	NUMBER,
  3  	worksin REF DepartmentType,
  4  	major	REF DepartmentType);
  5  /

Type created.

SCOTT@10gXE> CREATE TABLE departmenttab OF departmenttype
  2    (PRIMARY KEY (dno))
  3    OBJECT ID PRIMARY KEY
  4  /

Table created.

SCOTT@10gXE> INSERT INTO departmenttab VALUES (departmenttype (11))
  2  /

1 row created.

SCOTT@10gXE> INSERT INTO departmenttab VALUES (departmenttype (12))
  2  /

1 row created.

SCOTT@10gXE> INSERT INTO departmenttab VALUES (departmenttype (21))
  2  /

1 row created.

SCOTT@10gXE> INSERT INTO departmenttab VALUES (departmenttype (22))
  2  /

1 row created.

SCOTT@10gXE> CREATE TABLE tatab OF tatype
  2    (FOREIGN KEY (worksin) REFERENCES DepartmentTab,
  3  	FOREIGN KEY (major) REFERENCES DepartmentTab)
  4  /

Table created.

SCOTT@10gXE> HOST SQLLDR SCOTT/TIGER CONTROL=TEST.CTL LOG=TEST.LOG

SCOTT@10gXE> SELECT * FROM tatab
  2  /

        ID
----------
WORKSIN
----------------------------------------------------------------------------------------------------
MAJOR
----------------------------------------------------------------------------------------------------
         1
00004A038A0046CACC550C97F14A5BBC1FFBD0E78F79C70000001426010001000100290000000000090600812A00078401FE
0000000A02C10C0000000000000000000000000000000000000000
00004A038A0046CACC550C97F14A5BBC1FFBD0E78F79C70000001426010001000100290000000000090600812A00078401FE
0000000A02C1160000000000000000000000000000000000000000

         2
00004A038A0046CACC550C97F14A5BBC1FFBD0E78F79C70000001426010001000100290000000000090600812A00078401FE
0000000A02C10D0000000000000000000000000000000000000000
00004A038A0046CACC550C97F14A5BBC1FFBD0E78F79C70000001426010001000100290000000000090600812A00078401FE
0000000A02C1170000000000000000000000000000000000000000


SCOTT@10gXE> 

Previous Topic: SQL LOADER ORA 01772
Next Topic: Export from schema to another schema
Goto Forum:
  


Current Time: Fri Dec 02 20:30:13 CST 2016

Total time taken to generate the page: 0.06369 seconds