OraFAQ Forum: Server Utilities » SQLLDR: Loading Ref Columns

Home » RDBMS Server » Server Utilities » SQLLDR: Loading Ref Columns

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLLDR: Loading Ref Columns [message #202447]

Thu, 09 November 2006 21:08

tlahyani
Messages: 20
Registered: November 2006

Junior Member

Hello -

Is anybody familiar with the object-oriented feautures of oracle?
I have a couple of tables that I need to load using sqlldr, one one of them contains a column that is a REF to objects in the other table.

The types are declared as:

CREATE OR REPLACE TYPE DirectorType AS OBJECT (
did NUMBER(5),
name VARCHAR2(20),
birthdate DATE,
movies NUMBER
);

CREATE OR REPLACE TYPE MovieType AS OBJECT (
mid NUMBER(5),
title VARCHAR2(20),
year DATE,
director REF DirectorType
);

And the tables as follows:

CREATE TABLE DirectorTab OF DirectorType (did PRIMARY KEY);
CREATE TABLE MovieTab OF MovieType (mid PRIMARY KEY);

Here is the control file for loading the directorTab:

load data
infile './director.tbl'
into table directorTab
fields terminated by "," optionally enclosed by '"'
( did INTEGER EXTERNAL(5),
name CHAR(20),
birthdate "TO_DATE(:birthdate,'MM/DD/YYYY')",
movies INTEGER EXTERNAL(3) )

sample data:
10001,Leoni,09/11/1971,2,
10002,Smith,02/23/1956,9,
10003,Estwood,11/02/1909,28,

This works just fine! The problem is loading the movie table. Here is the control file:

LOAD DATA
INFILE './movie.tbl'
INTO TABLE movieTab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( mid INTEGER EXTERNAL(5),
title CHAR(20),
year "TO_DATE(:year,'YYYY')",
director REF (CONSTANT 'DIRECTORTAB', did),
did FILLER CHAR(5) )

sample data:
4, "Earth", 1997, 10003,
5, "Blast", 2006, 10001,

This is exactly the same as described in oracle documentation for loading primary key REF columns (http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_loading.htm#i1006786)
but I get the following error:
Record 1: Rejected - Error on table MOVIETAB, column DID.
error converting data
ORA-26007: invalid value for SETID or OID column

It seems like sqlldr thinks that the did column should be a system generated OID and expects 32 bytes of hex chars.

If anybody has encoutered this before or has an alternative, I would greatly appreciate it! Thanks,

-> Tariq

Report message to a moderator

Re: SQLLDR: Loading Ref Columns [message #202662 is a reply to message #202447]

Fri, 10 November 2006 15:13

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

-- director.tbl:

10001,Leoni,09/11/1971,2,
10003,Estwood,11/02/1909,28,

-- movie.tbl:

4,"Earth",1997,10003,
5,"Blast",2006,10001,

-- director.ctl:

LOAD DATA
INFILE director.tbl
INTO TABLE DirectorTab
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(did                                   INTEGER EXTERNAL(5),
name                                  CHAR(20),
birthdate                             "TO_DATE (:birthdate, 'MM/DD/YYYY')",
movies                                INTEGER EXTERNAL(3))

-- movie.ctl:

LOAD DATA
INFILE movie.tbl
INTO TABLE MovieTab
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(mid                                   INTEGER EXTERNAL(5),
title                                 CHAR(20),
year                                  "TO_DATE (:year, 'YYYY')",
director                              REF (CONSTANT 'DIRECTORTAB', did),
did                            FILLER CHAR)

SCOTT@10gXE> CREATE OR REPLACE TYPE DirectorType AS OBJECT
  2    (did			       NUMBER(5),
  3  	name			       VARCHAR2(20),
  4  	birthdate		       DATE,
  5  	movies			       NUMBER);
  6  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE MovieType AS OBJECT
  2    (mid			       NUMBER(5),
  3  	title			       VARCHAR2(20),
  4  	year			       DATE,
  5  	director		       REF DirectorType)
  6  /

Type created.

SCOTT@10gXE> CREATE TABLE DirectorTab OF DirectorType
  2    (PRIMARY KEY (did))
  3  	OBJECT ID PRIMARY KEY
  4  /

Table created.

SCOTT@10gXE> CREATE TABLE MovieTab OF MovieType
  2    (PRIMARY KEY (mid),
  3  	FOREIGN KEY (director) REFERENCES DirectorTab)
  4  	OBJECT ID PRIMARY KEY
  5  /

Table created.

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=director.ctl LOG=director.log

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=movie.ctl LOG=movie.log

SCOTT@10gXE> SELECT * FROM DirectorTab
  2  /

       DID NAME                 BIRTHDATE     MOVIES
---------- -------------------- --------- ----------
     10001 Leoni                11-SEP-71          2
     10003 Estwood              02-NOV-09         28

SCOTT@10gXE> COLUMN director FORMAT A30
SCOTT@10gXE> SELECT * FROM MovieTab
  2  /

       MID TITLE                YEAR      DIRECTOR
---------- -------------------- --------- ------------------------------
         4 Earth                01-NOV-97 00004A038A0046DF80FFB8D0DE4AD2
                                          B2ED81898713004500000014260100
                                          01000100290000000000090605002A
                                          00078401FE0000000C04C302010400
                                          000000000000000000000000000000
                                          0000

         5 Blast                01-NOV-06 00004A038A0046DF80FFB8D0DE4AD2
                                          B2ED81898713004500000014260100
                                          01000100290000000000090605002A
                                          00078401FE0000000C04C302010200
                                          000000000000000000000000000000
                                          0000


SCOTT@10gXE>

Report message to a moderator

Re: SQLLDR: Loading Ref Columns [message #202671 is a reply to message #202662]

Fri, 10 November 2006 18:20

tlahyani
Messages: 20
Registered: November 2006

Junior Member

Hello Barbara -

Thank you so much you just saved me a hell lot of time. I tried your modifications and they worked right away! Thank you again! Smile

-> Tariq

Report message to a moderator

Previous Topic:	DISABLE INDEXES SQL LOADER
Next Topic:	exp full=y do not want system user on imp full=y

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 16:23:04 CDT 2024