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  |
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  |
 |
Barbara Boehmer
Messages: 9097
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 |
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 |
|
Barbara Boehmer
Messages: 9097
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:
-- 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>
|
|
|
|
Goto Forum:
Current Time: Thu Jun 27 20:40:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
