Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: SQL Loader error

    Hi,
    I am stuck up with the below mentioned error since from copule of days any help in this regard is really great.
    I am trying to load data to oracle database using Oracle sql loader.
    I have to load data to two different tables using one control file, for first table all the datas are null and in the control file itself I am providing these data and primary key for this first table is loaded by calling a sequence. For this no problem, real problem starts when it starts trying to load data to the second table from a csv file.

    More over when I am trying to load data for only second table using a separate control file by giving the same csv file it is working fine and successfully loading the data to the respective table.

    Sample code of my control file :
    OPTIONS(SKIP=1)
    LOAD DATA
    APPEND
    INTO TABLE BATCH
    fields terminated by "," optionally enclosed by '"'
    TRAILING NULLCOLS
    (
    BATCH_ID "BATCH_ID_SEQ.nextval",
    BATCH_DATE "null",
    BATCH_TYPE "null",
    ------
    ------
    -------
    )
    INTO TABLE REPO
    fields terminated by "," optionally enclosed by '"'
    TRAILING NULLCOLS
    (
    REPO_ID "REPO_ID_SEQ.nextval",
    BATCH_ID "MARGIN_BATCH_ID_SEQ.currval"
    CLIENT "TRIM(:CLIENT)",
    ACCOUNT_NO "TO_NUMBER(:ACCOUNT_NO)",
    TRANS_TYPE "TRIM(:TRANS_TYPE)",
    MATURITY_DATE "TO_DATE(:MATURITY_DATE, 'mm/dd/yyyy')",
    ------
    ------
    -------

    )

    For table repo I am providing the csv file from cmd
    My SQL Loader command : CONTROL=C:\Repo.ctl DATA=C:\repo.csv log=C:\log.txt userid=***/*****

    Erro I am getting : Record 1: Rejected - Error on table REPO, column MATURITY_DATE.
    ORA-01858: a non-numeric character was found where a numeric was expected.

    I double checked the data type of maturity_date which is a date and also I checked in my csv file all data for maturity_date is date type.

    Thanks in Advance,
    Shashanka

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    also I checked in my csv file all data for maturity_date is date type
    However, I'd rather believe Oracle when it says that there's something wrong.

    Try to load that column's value into a CHARACTER datatype column (instead of a DATE one) and - in a loop - apply the TO_DATE function to it. Display value(s) which are incorrect.

Posting Permissions

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