Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    29

    Unanswered: SQl loader CTl file issue

    Hello

    i am trying to load data from text file to a oracle table,but it is giving me error as

    Record 1: Rejected - Error on table DATUSER.FEALINKC_TEST_STEVE, column POLNUM.
    ORA-01722: invalid number


    CTl file:

    options (skip = 1)
    LOAD DATA
    INFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\test\da ta_comparision\fealinkc_rr.txt'
    BADFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\test\da ta_comparision\fealinkc.bad'
    DISCARDFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\test\da ta_comparision\fealinkc.dsc'
    replace
    INTO TABLE DATUSER.FEALINKC_test_steve
    TRUNCATE
    FIELDS TERMINATED BY x'9'
    OPTIONALLY ENCLOSED BY "'"TRAILING NULLCOLS
    (CLAIM# ,
    LINCVG ,
    FEAT# ,
    CAUSE ,
    LORE ,
    POLNUM ,
    POLREN ,
    POLSEQ ,
    VEHNUM ,
    CVGCODE ,
    CVGGRP ,
    CYCVG ,
    COMPGRP ,
    PRODGRP ,
    UWSYS ,
    STATE ,
    CLMOPENDATE "TO_DATE(:CLMOPENDATE,'DDMONYYYY:HH24:MIS')",
    FEAOPENDATE "TO_DATE(:FEAOPENDATE,'DDMONYYYY:HH24:MIS')",
    VALID ,
    CVGVALID ,
    STATE2 ,
    COMPANY ,
    PRODUCT ,
    PRICING_COVERAGE
    )


    Table structure:

    CREATE TABLE DATUSER.FEALINKC_TEST_STEVE
    (
    CLAIM# CHAR(11 BYTE),
    LINCVG NUMBER(5),
    FEAT# NUMBER(5),
    CAUSE CHAR(1 BYTE),
    LORE CHAR(1 BYTE),
    POLNUM NUMBER(9),
    POLREN NUMBER(2),
    POLSEQ NUMBER(2),
    VEHNUM NUMBER(2),
    CVGCODE VARCHAR2(8 BYTE),
    CVGGRP CHAR(4 BYTE),
    CYCVG CHAR(1 BYTE),
    COMPGRP VARCHAR2(8 BYTE),
    PRODGRP VARCHAR2(6 BYTE),
    UWSYS VARCHAR2(6 BYTE),
    STATE CHAR(2 BYTE),
    CLMOPENDATE DATE,
    FEAOPENDATE DATE,
    VALID CHAR(1 BYTE),
    CVGVALID CHAR(1 BYTE),
    STATE2 CHAR(2 BYTE),
    COMPANY NUMBER(2),
    PRODUCT CHAR(3 BYTE),
    PRICING_COVERAGE VARCHAR2(6 BYTE)
    )


    I have attached the sample data:

    I know the error is coming because i have null values in the POLNUM filed how can i write ctl file to accept those values i have tried

    POLNUM "nvl(:POLNUM, ' ')",
    POLREN "nvl(:POLREN, ' ')",
    POLSEQ "nvl(:POLSEQ, ' ')",
    VEHNUM "nvl(:VEHNUM, ' ')" ,

    But it didn't worked for me


    Please help me to write ctl file to accept those null values


    Thanks in advance
    Hasya
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since when is a space character a number?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •