[SQL*Loader] How to really skip the processing of a column?

Question

In order to load data (from a CSV file) into an Oracle database, I use SQL*Loader.

In the table that receives these data, there is a varchar2(500) column, called COMMENTS. For some reasons, I want to ignore this information from the CSV file. Thus, I wrote this control file:

Options (BindSize=10000000,Readsize=10000000,Rows=5000,Errors=100)
  Load Data
  Infile 'XXX.txt'
  Append into table T_XXX
  Fields Terminated By ';'
  TRAILING NULLCOLS
(
    ...
    COMMENTS FILLER,
    ...
)

This code seems to work correctly, as the COMMENTS field in database is always set to null.

However, if in my CSV file I have a record where the corresponding COMMENTS field exceeds the 500 characters limit, I get an error from SQL*Loader:

Record 2: Rejected - Error on table T_XXX, column COMMENTS.
Field in data file exceeds maximum length

Is there a way to really exclude the processing of my COMMENTS fields?

Answer 1

Hi Romaintaz,

I can't reproduce your problem. I'm using Oracle 10.2.0.3.0 with SQL*Loader 10.2.0.1.

Here is my test case:

SQL> CREATE TABLE test_sqlldr (
  2     ID NUMBER,
  3     comments VARCHAR2(20),
  4     id2 NUMBER
  5  );

Table created

Control file:

LOAD DATA
INFILE test.data
INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( id,
  comments filler,
  id2
)

data file:

1;aaa;2
3;abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz;4
5;bbb;6

I'm using the command sqlldr userid=xxx/yyy@zzz control=test.ctl and I'm getting all the rows without errors:

SQL> select * from test_sqlldr;

        ID COMMENTS                    ID2
---------- -------------------- ----------
         1                               2
         3                               4
         5                               6

You may try another approach, I'm getting the same desired result with the following control file:

LOAD DATA
INFILE test.data
INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( id,
  comments "substr(:comments,1,0)",
  id2
)

Update following Romaintaz's comment: I looked into it again and managed to get the same error as you when the size of the column exceeded 255 characters. This is because the default datatype of SQL*Loader is char(255). If you have a column with more data you will have to specify the length. The following control file solved the problem for a column with 300 characters:

LOAD DATA
INFILE test.data
INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( id,
  comments filler char(4000),
  id2
)

Hope this Helps,

--
Vincent

Answer 2

Just to suggest a tiny improvement, you might try something like:

LOAD DATA
IN FILE test.data INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'TRAILING NULLCOLS
(
 id,
 comments char(4000) "substr(:comments, 1, 200)", 
 id2)

Now you'll grab the first 200 characters (or any number you specify in it's place) of all comments - unless some of your input records have values for the comments field that exceed 4000 characters, in which they'll be rejected by loader with the 'exceeds max length' error noted earlier. But assuming that's rare or not the case, all the records will load with some of the comments truncated to 200 chars.

If you go over char(4000) you'll get a SQL Loader error - there's a limit to how far you can push the beast.