We help IT Professionals succeed at work.

Problem with FILLER in SQL*Loader

janoxley
janoxley asked
on
Hi,
   
   Using Oracle 10g

   I have a table defined thus;
CREATE TABLE "CCMIS"."TL_DASH_F_FIZZBACK"
   (      "EIN" VARCHAR2(20 BYTE) NOT NULL ENABLE,
      "ADVISOR" VARCHAR2(200 BYTE),
      "MANAGER" VARCHAR2(200 BYTE),
      "OPERATIONS_MANAGER" VARCHAR2(200 BYTE),
      "SEGMENT" VARCHAR2(200 BYTE),
      "SITE" VARCHAR2(200 BYTE),
      "FIZZBACK_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
      "CUSTOMER_MOBILE_NUMBER" VARCHAR2(20 BYTE),
      "DATE_TIME_OF_CALL" DATE NOT NULL ENABLE,
      "DATE_TIME_OF_POLL" DATE,
      "Q1" NUMBER NOT NULL ENABLE,
      "Q2" NUMBER NOT NULL ENABLE,
      "Q3" NUMBER NOT NULL ENABLE,
      "CAT1" VARCHAR2(200 BYTE),
      "CAT2" VARCHAR2(200 BYTE),
      "RESPONSE_DATE" DATE,
      "STATUS" VARCHAR2(20 BYTE),
       CONSTRAINT "TL_DASH_F_FIZZBACK_PK" PRIMARY KEY ("FIZZBACK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CC_DATA"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CC_DATA" ;


   my CTL file is;

   LOAD DATA
INFILE '\\xxxxx\xxxx\Oracle Upload\MYDATA_May-2010.csv'

REPLACE
INTO TABLE "TL_DASH_F_DATA"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
  (      
      EIN                         CHAR,
      ADVISOR                         CHAR,
      MANAGER                         CHAR,
      OPERATIONS_MANAGER             CHAR,
      SEGMENT                         CHAR,
      SITE                         CHAR,
      FIZZBACK_ID                   CHAR,
      CUSTOMER_MOBILE_NUMBER             CHAR,
      DATE_TIME_OF_CALL            "TO_DATE(:DATE_TIME_OF_CALL,'DD/MM/YYYY HH24:MI')",
      DATE_TIME_OF_POLL            "TO_DATE(:DATE_TIME_OF_POLL,'DD/MM/YYYY HH24:MI')",
      Q1,
      Q2,
      Q3,
      MESSAGE                         FILLER,      
      CAT1                         CHAR,
      CAT2                         CHAR,
      RESPONSE_DATE                  "TO_DATE(:RESPONSE_DATE,'DD/MM/YYYY HH24:MI')",      
      STATUS                         CHAR,
      NOTES_FROM_RESPONSE_MANAGER       FILLER,
      REASON_FOR_DISSATISFACTION         FILLER,
      FURTHER_DETAIL                   FILLER


)

As you can see 4 columns are defined as FILLER columns (my understanding is that no data from the data file for these columns will be loaded in to the DB)

So, in the table there are no columns for MESSAGE,NOTES_FROM_RESPONSE_MANAGER,REASON_FOR_DISSATISFACTION or FURTHER_DETAIL

But they exist in the data file. I've used the FILLER keyword to try and stop those columns from being loaded (or attempted to be loaded)

Now. Here's an extract from my log file;


SQL*Loader: Release 10.2.0.1.0 - Production on Thu Jun 10 11:21:16 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   ...........TL_DASH_FEEDS_FIZZBACK.ctl
Data File:      ...........FIZZBACK_May-2010.csv
  Bad File:     ...........TL_DASH_FEEDS_FIZZBACK.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table "TL_DASH_F_FIZZBACK", loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EIN                                 FIRST     *   ,  O(") CHARACTER            
ADVISOR                              NEXT     *   ,  O(") CHARACTER            
MANAGER                              NEXT     *   ,  O(") CHARACTER            
OPERATIONS_MANAGER                   NEXT     *   ,  O(") CHARACTER            
SEGMENT                              NEXT     *   ,  O(") CHARACTER            
SITE                                 NEXT     *   ,  O(") CHARACTER            
FIZZBACK_ID                          NEXT     *   ,  O(") CHARACTER            
CUSTOMER_MOBILE_NUMBER               NEXT     *   ,  O(") CHARACTER            
DATE_TIME_OF_CALL                    NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:DATE_TIME_OF_CALL,'DD/MM/YYYY HH24:MI')"
DATE_TIME_OF_POLL                    NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:DATE_TIME_OF_POLL,'DD/MM/YYYY HH24:MI')"
Q1                                   NEXT     *   ,  O(") CHARACTER            
Q2                                   NEXT     *   ,  O(") CHARACTER            
Q3                                   NEXT     *   ,  O(") CHARACTER            
MESSAGE                              NEXT     *   ,  O(") CHARACTER            
  (FILLER FIELD)
CAT1                                 NEXT     *   ,  O(") CHARACTER            
CAT2                                 NEXT     *   ,  O(") CHARACTER            
RESPONSE_DATE                        NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:RESPONSE_DATE,'DD/MM/YYYY HH24:MI')"
STATUS                               NEXT     *   ,  O(") CHARACTER            
NOTES_FROM_RESPONSE_MANAGER          NEXT     *   ,  O(") CHARACTER            
  (FILLER FIELD)
REASON_FOR_DISSATISFACTION           NEXT     *   ,  O(") CHARACTER            
  (FILLER FIELD)
FURTHER_DETAIL                       NEXT     *   ,  O(") CHARACTER            
  (FILLER FIELD)

value used for ROWS parameter changed from 64 to 58
Record 12: Rejected - Error on table "TL_DASH_F_FIZZBACK", column MESSAGE.
Field in data file exceeds maximum length
Record 18: Rejected - Error on table "TL_DASH_F_FIZZBACK", column MESSAGE.
Field in data file exceeds maximum length


So, the log file tells me that those columns ARE filler columns however it then complains that the MESSAGE column is too long!!?!?!

What am I missing??

Cheers
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I can't set up a test case right now but try setting the length of the filler to the length of the column.  If it still fails, verify the data in the file isn't longer than 200 characters.  You might need to play with the length in the control file.

...
Q3,
MESSAGE FILLER CHAR(200),      
CAT1 CHAR,
...

Author

Commented:
The data in those columns is very large >4000 characters. That's why i don't want to load them
Those columns don't exist in the table i'm loading to.
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Is there a MAX size for the data?

Try setting FILLER CHAR(1000000)

You might also try:
...
Q3,
MESSAGE FILLER CHAR(200) TERMINATED BY WHITESPACE,      
CAT1 CHAR,
...

Author

Commented:
Well that works. I'd be interested to know why though :-)

Cheers
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
It's been a LONG time since I dove into the SQL*Loader docs but the default for CHAR used to be 255.  

SQL Loader still 'looks' at the record in the file even though it's FILLER so as soon as the data exceeds 255 it complains.

As long as the first whitespace character is found before the length you specify sqlldr doesn't attempt to look at the whole string.