SQL*Loader question 1

[FORAND]

Hi!

I'm currently using SQL*Loader to insert data into a Oracle table.

Here is my sqlloader command :

sqlldr / CONTROL=pfc_tmp_int_new.ctl LOG=tmp_int.log BAD=tmp_int.bad DATA=intv.dat ERRORS=2 DIRECT=TRUE>/dev/null

here is my table :

TABLE TTMPINTPFC ( 
	INTCOD		 NUMBER,
	MSGIDF          CHAR    (16) not null,
	INTVINSTNUM    NUMBER       not null,
	INTVDATETIME    VARCHAR2(17),
	INTVSEQNB       VARCHAR2(15),
	INTVINTVNUM     VARCHAR2(5)  not null,
	INTVNAME        VARCHAR2(25),
	INTVCAT         VARCHAR2(30),
	INTVNICKNAME    VARCHAR2(10),
	INTVLOCATION    VARCHAR2(25),
	INTVPFNNAME     VARCHAR2(25),
	INTVAPPETIME    VARCHAR2(17),
	INTVAPPESEQ     VARCHAR2(15),
	INTVCHUNK       VARCHAR2(10),
	INTVCHUNKFL     VARCHAR2(10),
	INTVLENGTH      NUMBER,
	INTVTOKEN       VARCHAR2(10),
	INTVTEXT        VARCHAR2(200))

The problem is that some data in my file is WWAAYY bigger than one of my field (INTVTEXT). Sometimes it's 2000 caracter, and I've set my field for 200........

So, I basicly want to TRUNCATE my date so SQL*Loader can fit the first 200 into my database.

Here's my .ctl :

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE ttmpintpfc
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
                        (INTCOD         ,
                         MSGIDF         ,
                         INTVINSTNUM    ,
                         INTVDATETIME   ,
                         INTVSEQNB      ,
                         INTVINTVNUM    ,
                         INTVNAME       ,
                         INTVCAT        ,
                         INTVNICKNAME   ,
                         INTVLOCATION   ,
                         INTVPFNNAME    ,
                         INTVAPPETIME   ,
                         INTVAPPESEQ    ,
                         INTVCHUNK      ,
                         INTVCHUNKFL    ,
                         INTVLENGTH     ,
                         INTVTOKEN      ,
                         INTVTEXT       )

I have already set my .ctl to Truncate, but it does not work... can someone help ?

thanks a lot

FORAND

 

[FORAND]

It worked on your test_table here.
I will test tomorrow on the real thing.

 

[FORAND]

Ok. I changed the "CHAR" setting in the .ctl in my environment.

I changed it to 200000 (to be on the safe side). Here is what I have now :

Record 132867: Rejected - Error on table TTMPINTPFC.
ORA-01461: can bind a LONG value only for insert into a LONG column

What is the maximal value of the CHAR parameter in the .CTL of SQL*Loader ?

 

[Dagon]

For Oracle 8, I imagine it would be 2K. I think that was as large as Oracle 8 chars could go (although that might have been Oracle 7 - it's quite hard remembering with these ancient versions).