Ask Donneker

Geben Sie Ihre Suchbegriffe ein Suchformular senden Web donneker.de

• Top Links
  • b2evolution - Skins & Plugins
  • Handy Info & Review
  • DotA Banlist & more
  • radio_rip Recording Streams
  • Silkroad Online Autologin
  • Top Ratings Article/Downloads
  • MyMiniCity Graphics Statistics
  • HowTo/Manuals
• Donneker's Blog
  • Blog Home
  • Übersicht Blogkategorien
  • Liste der Blogposts
  • Blog als RSS
• Downloads
  • free downloads (new)
  • free downloads/projects (old stuff)
• Empfehlungen/Forum
  • Empfehlungen Home
  • Buch Empfehlungen
  • Handy Erfahrungen
  • Telefon & Internet
  • Forum
• About Stefan Hummert
  • my profile
  • contact
  • pictures
  • guestbook
  • activities
  • links & bookmarks

• www.donneker.de• HowTo• Oracle-SQL-Loader-Invalid-Number-Problem

 

HowTo: Possible Solution to your Oracle Invalid Number Error/Problem with sqlldr on Linux/UNIX.

Problem Description with SQL-Loader Configfile

My Problem was the folowing. I builded my SQL-Loader (sqlldr) controlfile with the known Tool "Toad", and it worked fine while I was using it under Windows (also with Toad, but Toad calls sqlldr).
Now, I wanted to use this controlfile (.ctl) under Linux.
As I didn't expected, it throwed an error called "ORA-01722: invalid number"
I had builded an controlfile with semicolon as seperator and the last column in the file was to load in a number database column. So the record format was variable size, not fixed ( I didn't work with the position clause )



So, this solution is perhaps for you, if you have variable sized records (as text file) and work with column seperator in your file. I think it's only a problem, when the number column is the last in your file, and if you load it in a number Database Column. This, and the fact that the input file was from a Windows Machine having CR/LF as Newline.

The solution maybe as simple as mine was:
Put after your last column the option TERMINATED BY WHITESPACE because if you are missing this, I think the loader tries to read the record seperator (CR from Windows) and tries to convert it together with the other digits into a number.
Then you will get the error I think you know well. Of course, there are also some other common mistakes I short will show you.
If you have perhaps other country settings on your Linux/UNIX Machine, check if the number in your file have "." or "," as decimal seperator. Maybe your file has others than your Server expects. You can correct this with the NLS_NUMERIC_CHARACTERS=",." setting. (this is for example for Germany for US this would be NLS_NUMERIC_CHARACTERS=".,"
First is the decimal seperator, second the group seperator to see thousands together.

Diese Seite bewerten / Vote this site:

Disclaimer/Impressum