Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Urget feedback needed SQL-LOADER Invalid Number

    Hi All

    I am trying to insert the record using SQLLDR through .csv file. i have created the proper control file ,temp table and try to insert the record.
    but while inserting the record i am getting "invalid number" error ,though i have check my csv file , the column which throwing the error contain number only , no specail charcter.

    Record 1: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 2: Rejected - Error on table T_MEAS_38_TMP, column RPT_TN.
    ORA-01722: invalid number


    But when i try to change the datatype from number to varchar2 with size compartivaly more then it allows me to insert the record for that column and error sift to other column having number datatype.
    Plz help me out 4 this and how can i ressolve this issue.

    I cant use varchar2 for each n evry column.


    Here are the details of my control file ,table and .csv file
    Table Defination
    CREATE TABLE T_MEAS_38_TMP
    (
    ACNA varchar2(4),
    YEARMO number(15),
    STATE varchar2(24),
    METRO varchar2(25),
    DPID number(15),
    NUM number(2),
    DEN number(2),
    RETAIL number(2),
    main_tn number(30),
    so_nbr varchar2(20),
    RPT_TN number(30),
    cls_name varchar2(19),
    stc varchar2(12),
    PROD_NM varchar2(30),
    RPTD_NM varchar2(30),
    CKTID varchar2(50),
    cac varchar2(45),
    busid varchar2(10),
    seg_cd varchar2(10),
    rpt_nbr varchar2(10),
    mctr varchar2(12),
    tr_type varchar2(15),
    disp number(20),
    cat number(11),
    tr_cd varchar2(4),
    recdt number(11),
    rec_tm number(11),
    clrdt number,
    clr_tm number(8),
    aclod number(11),
    act_dur number(11),
    appt_cd number(5),
    req varchar2(10),
    ttclsfn varchar2(5),
    fcas number(3),
    icase number(10),
    repeat number(1),
    fvi number(1),
    oos number(1),
    dsl number(1),
    storage_dt number(15),
    unep number(8),
    mcn varchar2(11),
    assgn_mcn varchar2(11)
    )
    tablespace RTW_APP_DAT
    ;


    Control file...
    LOAD DATA
    INFILE 'C:\test\38D_Measure\re038d_sta_une.csv'
    BADFILE 'C:\test\38D_Measure\re038d_sta_une.bad'
    DISCARDFILE 'C:\test\38D_Measure\re038d_sta_une.dsc'
    insert
    INTO TABLE t_meas_38_tmp
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    ACNA,
    YEARMO,
    STATE,
    METRO,
    DPID,
    NUM,
    DEN,
    RETAIL,
    main_tn,
    so_nbr,
    RPT_TN,
    cls_name,
    stc,
    PROD_NM,
    RPTD_NM,
    CKTID,
    cac,
    busid,
    seg_cd,
    rpt_nbr,
    mctr,
    tr_type,
    disp,
    cat,
    tr_cd,
    recdt,
    rec_tm,
    clrdt,
    clr_tm,
    aclod,
    act_dur,
    appt_cd,
    req,
    ttclsfn,
    fcas,
    icase,
    repeat,
    fvi,
    oos,
    dsl,
    storage_dt,
    unep,
    mcn,
    assgn_mcn
    )

    .csv file example record
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1
    99R 200902 IL CHICAGO 228 0 1 1

    this is only few line of the csv file


    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, you have specified that FIELDS TERMINATED BY "," but your sample data do not follow that statement (and result in rejecting all sample records).

    Secondly, even when this trivial error is fixed, none of the records fails because of the error you have mentioned (invalid number).

    Therefore, would you mind to post sample data which reflect your question?

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    HI Littlefoot,
    i have remove the Field Terminated by "," with " " in control file.
    On removing field terminated ,
    Here is the .csv excel (see the attachment) for excel .csv file and log file.

    Thanks for you Quick responce

  4. #4
    Join Date
    Jul 2009
    Posts
    7
    please find the log file on removing Field Terminnated by "," with " "
    Also see the sample data of .csv file i have appended on5 to 6 rows.

    -------------LOG FILE-----------

    SQL*Loader: Release 10.2.0.3.0 - Production on Wed Jul 15 18:42:45 2009

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

    Control File: re038d_sta_une.ctl
    Data File: C:\test\38D_Measure\re038d_sta_une.csv
    Bad File: C:\test\38D_Measure\re038d_sta_une.bad
    Discard File: C:\test\38D_Measure\re038d_sta_une.dsc
    (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 T_MEAS_38_TMP, loaded from every logical record.
    Insert option in effect for this table: APPEND
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    ACNA FIRST * WHT O( ) CHARACTER
    YEARMO NEXT * WHT O( ) CHARACTER
    STATE NEXT * WHT O( ) CHARACTER
    METRO NEXT * WHT O( ) CHARACTER
    DPID NEXT * WHT O( ) CHARACTER
    NUM NEXT * WHT O( ) CHARACTER
    DEN NEXT * WHT O( ) CHARACTER
    RETAIL NEXT * WHT O( ) CHARACTER
    MAIN_TN NEXT * WHT O( ) CHARACTER
    SO_NBR NEXT * WHT O( ) CHARACTER
    RPT_TN NEXT * WHT O( ) CHARACTER
    CLS_NAME NEXT * WHT O( ) CHARACTER
    STC NEXT * WHT O( ) CHARACTER
    PROD_NM NEXT * WHT O( ) CHARACTER
    RPTD_NM NEXT * WHT O( ) CHARACTER
    CKTID NEXT * WHT O( ) CHARACTER
    CAC NEXT * WHT O( ) CHARACTER
    BUSID NEXT * WHT O( ) CHARACTER
    SEG_CD NEXT * WHT O( ) CHARACTER
    RPT_NBR NEXT * WHT O( ) CHARACTER
    MCTR NEXT * WHT O( ) CHARACTER
    TR_TYPE NEXT * WHT O( ) CHARACTER
    DISP NEXT * WHT O( ) CHARACTER
    CAT NEXT * WHT O( ) CHARACTER
    TR_CD NEXT * WHT O( ) CHARACTER
    RECDT NEXT * WHT O( ) CHARACTER
    REC_TM NEXT * WHT O( ) CHARACTER
    CLRDT NEXT * WHT O( ) CHARACTER
    CLR_TM NEXT * WHT O( ) CHARACTER
    ACLOD NEXT * WHT O( ) CHARACTER
    ACT_DUR NEXT * WHT O( ) CHARACTER
    APPT_CD NEXT * WHT O( ) CHARACTER
    REQ NEXT * WHT O( ) CHARACTER
    TTCLSFN NEXT * WHT O( ) CHARACTER
    FCAS NEXT * WHT O( ) CHARACTER
    ICASE NEXT * WHT O( ) CHARACTER
    REPEAT NEXT * WHT O( ) CHARACTER
    FVI NEXT * WHT O( ) CHARACTER
    OOS NEXT * WHT O( ) CHARACTER
    DSL NEXT * WHT O( ) CHARACTER
    STORAGE_DT NEXT * WHT O( ) CHARACTER
    UNEP NEXT * WHT O( ) CHARACTER
    MCN NEXT * WHT O( ) CHARACTER
    ASSGN_MCN NEXT * WHT O( ) CHARACTER

    value used for ROWS parameter changed from 64 to 22
    Record 1: Rejected - Error on table T_MEAS_38_TMP, column ACNA.
    Field in data file exceeds maximum length
    Record 2: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 3: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 4: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 5: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 6: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 7: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 8: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 9: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 10: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 11: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number

    Record 12: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
    ORA-01722: invalid number


    MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

    Table T_MEAS_38_TMP:
    0 Rows successfully loaded.
    51 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.


    Space allocated for bind array: 249744 bytes(22 rows)
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 67
    Total logical records rejected: 51
    Total logical records discarded: 0

    Run began on Wed Jul 15 18:42:45 2009
    Run ended on Wed Jul 15 18:42:46 2009

    Elapsed time was: 00:00:01.14
    CPU time was: 00:00:00.09
    ==================================

    ----------SAMPLE DATA .csv -----------------
    ACNA DATA_MONTH STATE METRO_AREA TRACKING_ID NUMERATOR DENOMINATOR RETAIL_IND MAIN_TN SVC_ORDER REPT_TEL_NUM COS_NAME SERV_TYPE_CD PROD_NM RPTD_PROD_NM CKTID CAC BUS_ID BUS_SEG_CD REPORT_NUM MCTR TRBL_REPO_TYPE DISP_CODE CAT_CODE TRBL_CODE RECDT REC_TM CLRDT CLR_TM ACLOD ACTUAL_DUR MISSD_APT_CD REQ_FLAG TTCLSFN FCAS ICASE REPEAT FIELD_VISIT_IND OOS DSL_SHR_IND STORAGE_DT UNEP_IND
    99R 200902 IL CHICAGO 228 0 1 1 7735857336 773/585/7336 RESIDENCE 4 NAP2DT7C PL16RN06 1 4 1 2122009 709 2132009 360 2142009 1091 0 2 471 0 0 1 1 0 2152009 0
    99R 200902 IL CHICAGO 228 0 1 1 3122269536 312/226/9536 BUSINESS 5 NAP2EB4C PL179W13 1 4 1 2272009 631 2272009 950 2272009 319 0 2 471 0 0 1 1 0 2282009 0
    99R 200902 IL CHICAGO 228 0 1 1 7732710065 773/271/0065 RESIDENCE 4 NAP2DY2C PL174C28 1 4 1 2222009 938 2232009 597 2232009 1099 1 2 471 0 0 1 1 0 2242009 0
    99R 200902 IL CHICAGO 228 0 1 1 7737212875 773/721/2875 BUSINESS 5 NAN2YH7C PL178002 1 4 1 2262009 499 2262009 646 2262009 147 0 2 471 0 0 1 1 0 2272009 0
    99R 200902 IL CHICAGO 228 0 1 1 7735851662 773/585/1662 BUSINESS 5 NKP2EW2X PL16RM37 3 4 1 2122009 702 2132009 360 2142009 1098 0 2 471 0 0 1 0 0 2152009 0
    99R 200902 IL CHICAGO 228 0 1 1 773/745/4550 RESIDENCE 4 NAP2BZ8C PL174255 1 7 1 2212009 1081 2222009 850 2222009 1209 0 2 300 0 0 1 1 0 2232009 0
    99R 200902 IL CHICAGO 228 0 1 1 773/978/8771 RESIDENCE 4 NAP2DX4C PL172R43 1 7 1 2202009 464 2202009 910 2202009 446 0 2 471 0 0 1 1 0 2222009 0
    99R 200902 IL CHICAGO 228 0 1 1 7736386343 773/638/6343 RESIDENCE 4 NUA2LT9N CS PL171065 5 5 1 2182009 1124 2182009 1125 2182009 1 0 2 193 0 0 0 0 0 2192009 0
    99R 200902 IL CHICAGO 228 0 1 1 7733425844 773/342/5844 RESIDENCE 4 NUJ2TP7S CS PL178Z76 1 4 1 2262009 1038 2272009 829 2272009 1231 0 N 2 471 0 0 1 1 0 2282009 0

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post Operating System (OS) name & version for DB server system.
    Post Oracle version to 4 decimal place of database s/w from v$version.

    It appears 44 columns in table.
    >99R 200902 IL CHICAGO 228 0 1 1 7735857336 773/585/7336 RESIDENCE 4 NAP2DT7C PL16RN06 1 4 1 2122009 709 2132009 360 2142009 1091 0 2 471 0 0 1 1 0 2152009 0
    I counted only 33 spaces/fields in row above. Why?

    >i have created the proper control file ,temp table
    Then how do you explain the errors & data discrepancy?

    Either you or Oracle are in error.
    Which is more likely?
    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
  •