Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


sqlloader - invalid number

Posted on 2013-05-26
Medium Priority
Last Modified: 2013-05-30
Attached is my control file and .csv file. I did attach another .xls which is the copy of .csv file for matching the name of the fields with the columns.

I am using .csv thru sqlloader, I keep getting "invalid number" when the data is numeric..
This is happening for columns, BX(Expenses- name of the column) and BB(TRA - name of the column).

Also, I get error on column BF, saying actual length is 4, maximum length is 3. But I see only three characters in.csv file for the column BF(settle_ccy -name of the column). Also, you can see I have used TRIM in my control file for varchar.

This is very urgent issue, please help !

Quick response is appreciated.
Question by:d27m11y
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 20

Expert Comment

ID: 39198094
can you provide the table definition of  stg_citco_intraday_trade ?
LVL 20

Expert Comment

ID: 39198159
$tradehome points to the right place ?

I used an external table definition csv_load (definition attached  (all columns varchar(255) except the one you mentioned) and a directory 'ext' i already had defined for

SQL> select expenses, settle_ccy, tra from csv_load
  2  /

---------- --- ----------
         0 USD          1
         0 USD          1
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39198832
How may records are loaded, this is feedback from the sqlldr command.

Have a look at the discard (.dsc) file to see which records are not loaded.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 39201311
I tried to basically use a shell script for reading this, here is my shell and then tried to convert .csv file from dos to unix format and then it worked.

Please find attached DDL for the table stg_citco_intraday_trade. The first column is a sequence number for which I created a sequence and also created a trigger (before insert) so that it gets first column loaded there for every insert statement. Am attaching those as well.

#!/bin/ksh -xve


echo   'Started sqlloader stg_citco_intraday_trade'

if ( test -e "$tradehome/ods/data/csv/test_0523.csv")
   dos2unix $tradehome/ods/data/csv/test_0523.csv $tradehome/ods/data/csv/test_0523.csv
   echo $odspass | sqlldr $odsuser control=$tradehome/ods/ctl/stg_citco_intraday_trade.ctl  log=$tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log
   cat $tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log


echo  'Ended sqlloader stg_citco_intraday_trade'

Open in new window

I may have to change my shell to ftp .csv file. Could someone suggest how to do that ?
LVL 20

Accepted Solution

flow01 earned 1000 total points
ID: 39202163
# this is how a shell ftp session looks on our unix-box
ftp -i -n << EOF
open servername
quote USER username
quote PASS password
put filename1 filename2

#within the ftp-session you can use the regular ftp-commands

Author Closing Comment

ID: 39207966

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question