We help IT Professionals succeed at work.
Get Started

Problem with FILLER in SQL*Loader

janoxley
janoxley asked
on
3,780 Views
Last Modified: 2013-12-12
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE