1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Issue with sql loader (control file)

Discussion in 'Oracle Apps Technical' started by heepth, Dec 11, 2014.

  1. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    The below one is the error im getting when im running the control file

    error:

    SQL*Loader: Release 10.2.0.1.0 - Production on Fri Dec 12 00:04:50 2014

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

    SQL*Loader-350: Syntax error at line 17.
    Expecting "," or ")", found "TO_NUMBER".
    "TO_NUMBER:)revenue,'$99,999.99')" )
    ^

    control file:


    load data
    infile 'C:\Users\Bharath\Desktop\load_eval.csv'
    badfile 'C:\Users\Bharath\Desktop\load_bad.bad'
    discardfile 'C:\Users\Bharath\Desktop\load_dsc.csv'
    replace into table xx_testload
    fields terminated by ',' optionally enclosed by '"'
    TRAILING NULLCOLS
    ( COMPANY_NAME NULLIF COMPANY_NAME=BLANKS,
    add_LOCATION NULLIF COMPANY_NAME=BLANKS,
    PHONE_NUMBER NULLIF COMPANY_NAME=BLANKS,
    EMAIL NULLIF COMPANY_NAME=BLANKS,
    ESTABLISHED DATE "YYYY-MM-DD\"T\"HH24:MI:SS\"TZH:TZM"" ,
    TAN NULLIF COMPANY_NAME=BLANKS,
    BANK_ACCOUNT_NUMBER NULLIF COMPANY_NAME=BLANKS,
    EMPLOYEE_STRENGTH NULLIF COMPANY_NAME=BLANKS,
    revenue
    "TO_NUMBER:)revenue,'$99,999.99')" )

    help me out ..
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    I think problem is here..

    revenue
    "TO_NUMBER:)revenue,'$99,999.99')"

    Check like this...

    revenue expression "to_number(':revenue,'$99,999.99')")
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi heepth,

    Correct the line being reported with:

    Code (SQL):
    revenue "TO_NUMBER(:revenue ,'$99,999.99','NLS_NUMERIC_CHARACTERS='',.''')"
    NB: I assume the content of your field "revenue" in the file is something like, for example: ...,"$12,345.67"
    You can also change the format to something higher (for ex., '$99,999,999.99') just to cater for larger revenues.