running sqlldr control files in UNIX

Question

I have a question. I have this control files that works fine when I run it from a windows client. However when I run it directly in Linux, it shows load complete but when I look at my oracle data, there is NO DATA and there are even bad records. Below is my control file that works well in windows but fails in Linux. NOTE: The control file works if I remove the string or date converted fields

Control file

load data   
infile 'HOME/INPUT/FILEA.dat'  
badfile 'HOME/BAD/FILEA.bad'  
discardfile 'HOME/DIS/FILEA.dsc'  
truncate    
into table TEST  
fields terminated by '|'   
trailing nullcols  
( ABCcode  CHAR(11),    
ABCID  CHAR(6),    
ABC_SEQNO  "to_number(:ABC_SEQNO,'999999')",    
PSNO  "to_number(:PSNO,'99999999999.999')",    
ABDF  CHAR(1),    
ABCFI  CHAR(1),    
ABC_DATE NULLIF ABC_DATE="00000000" "to_date(:ABC_DATE, 'YYYYMMDD')",    
XZY_date NULLIF XZY_date="00000000" "to_date(:XZY_date, 'YYYYMMDD')",    
DESC  CHAR(1))

Any help or ideas to get this code to run in Linux will be appreciated

Notes about the logfile: The logfile had the following
ORA-00604: error occurred at recursive SQL level 1
ORA-12899: value too large for column "ABCschema"."TEST"."ABC_DATE" (actual: 9, maximum: 8)
Also, the date conversion had the following
NULL if ABC_DATE = 0X3030303030303030(character '00000000') SQL string for column : "to_date(:ABC_DATE, 'YYYYMMDD')"

Did you try generating the log file? If yes, what were the errors? If not, why? — mustaccio, Feb 18 '14 at 17:48
I did and I just updated my post to reflect the messages I got from the log file — user2008558, Feb 18 '14 at 18:05
Hmm, actually, don't think it is that... what data type is `TEST.ABC_DATE` - looks like a `VARCHAR2` from the error, not a `DATE`? And is this the same DB you were loading to from Windows? It looks like you're doing an implicit conversion of that formatted date back to a string for insertion, which is never good; the error might match an `NLS_DATE_FORMAT` of `DD-MON-RR` for example. Can you check what format `sysdate` is given if you do a plain `select sysdate from dual` from SQL*Plus on both boxes? Sample data, and table definition, might help too. — Alex Poole, Feb 18 '14 at 18:47
sysdate is in DD-MON-RR. The date from the data is a varchar but had to be converted to a date format of yyyymmdd. — user2008558, Feb 18 '14 at 18:55
I understand that the file has `YYYYMMDD` and you're converting it to a date; but I'm sure the `ABC_DATE` column in your `TEST` table is actually `VARCHAR2(8)`. With a valid input string like `20140218` and the NLS_DATE_FORMAT you showed, you would get error `ORA-12899: value too large for column ... (actual: 9, maximum: 8)`. I've just tested and verified it. — Alex Poole, Feb 18 '14 at 19:00
The ABC_DATE column in test is actually a DATE data type but the data containing the ABC_DATE column is in a character format 20140218. I had to use To_date to convert it to a date format in order for it to load successfully into the TEST table (using a windows platform). If I take out the To_date conversion and change the ABC_DATE to varchar2, it works fine but I don't want to go about changing the whole date types to VARCHAR2 for every column attribute in TEST — user2008558, Feb 18 '14 at 19:17
The error you show is from the table column being a string. If you're switching the table definition, please show what error(s) you get when it's a definitely a date. — Alex Poole, Feb 18 '14 at 19:26
Silly me! I just realized that the proper data types were not reflected on the table columns. I fixed it and everything seems to be fine. Thanks Alex — user2008558, Feb 18 '14 at 19:33

score 0 · Accepted Answer · answered Feb 18 '14 at 19:08

Your TEST table has the ABC_DATE column defined as VARCHAR2(8), not as a DATE.

If I create a table as:

create table test (
  ABCcode VARCHAR2(11),
  ABCID VARCHAR2(6),
  ABC_SEQNO NUMBER,
  PSNO NUMBER,
  ABDF VARCHAR2(1),
  ABCFI VARCHAR2(1),
  ABC_DATE DATE,
  XZY_date DATE,
  "DESC" VARCHAR2(1)
);

and have a data file with:

A|B|1|2.3|C|D|20140217|20140218|E

then it loads fine. If I recreate the table as:

create table test (
  ABCcode VARCHAR2(11),
  ABCID VARCHAR2(6),
  ABC_SEQNO NUMBER,
  PSNO NUMBER,
  ABDF VARCHAR2(1),
  ABCFI VARCHAR2(1),
  ABC_DATE VARCHAR2(8),
  XZY_date DATE,
  "DESC" VARCHAR2(1)
);

... then the same control file and data file now give me:

Record 1: Rejected - Error on table TEST, column ABC_DATE.
ORA-12899: value too large for column "<schema>"."TEST"."ABC_DATE" (actual: 9, maximum: 8)

You are converting the string value to a date, but then you're doing an implicit conversion back to a string when it actually inserts the data into the VARCHAR2 column. When it does that it's using your NLS_DATE_FORMAT settings, and the error I got was from having that set to DD-MON-RR.

You have three options really. Either modify your table to have actual DATE columns; or change the control file so it just inserts the plain text value and doesn't do the date conversion at all; or massage your environment so the conversion back to a string gets the format you want the string to be.

Only the first one is really sensible - if it's a date value, always store it as a DATE, never as a string.

The 0X30... thing isn't a problem, that's just showing the internal representation it's using.

Quick question, are you loading it directly from Linux or from a Windows environment — user2008558, Feb 18 '14 at 19:21
@user2008558 - Linux. But this is about your database and table, and your Db environment, not the operating system. — Alex Poole, Feb 18 '14 at 19:24
Thanks Alex. You are absolutely right. I just realized that the data attributes were modified to VARCHAR2. Now the control file works Thanks for you help. — user2008558, Feb 18 '14 at 19:35

running sqlldr control files in UNIX

1 Answers1