Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01722: Invalid number when importing .csv file

986305Jan 22 2013 — edited Jan 22 2013
Hi,

I did not find any information regarding my specific problem until now.

I try to import a *.csv file containing id, double, double, double, double, double (e.g. as a sample line "id_1, 674,6703459157907, 4212,205538937771, 674,6703459158016, 5561,546230769363, 2714,6367797576822") into a table with the following definition:

CREATE TABLE "foo"."BUILDING_SURFACES"
( "ID" VARCHAR2(40 BYTE),
"AREA1" BINARY_DOUBLE DEFAULT 0,
"AREA2" BINARY_DOUBLE DEFAULT 0,
"AREA3" BINARY_DOUBLE DEFAULT 0,
"AREA4" BINARY_DOUBLE DEFAULT 0,
"AREA5" BINARY_DOUBLE DEFAULT 0
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

I am doing it with the help of the importer tool in the SQLDeveloper, by doing a right-click onto the table and selecting import data. In the assistant everything seem to be fine, even the data preview.
But when I try to import, I get an error: "ORA-01722: Invalid number" and a couple of failure windows appear. These windows display a NullPointerException:

java.lang.NullPointerException
at oracle.dbtools.raptor.data.writers.DataTypeFormatterRegistry.getFormattor(DataTypeFormatterRegistry.java:42)
at oracle.dbtools.raptor.data.writers.ImportGenerator.getBatchForInsert(ImportGenerator.java:1837)
at oracle.dbtools.raptor.data.writers.ImportGenerator.access$1800(ImportGenerator.java:84)
at oracle.dbtools.raptor.data.writers.ImportGenerator$1.afterLoopProcessing(ImportGenerator.java:1125)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutorTask.loopThroughAllStatements(ScriptExecutorTask.java:220)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutorTask.doWork(ScriptExecutorTask.java:165)
at oracle.dbtools.raptor.data.writers.ImportGenerator$1.doWork(ImportGenerator.java:782)
...

If I cancel the task, the insert statements are displayed:

SET DEFINE OFF

--Einfügen für Zeilen 1 bis 2 nicht erfolgreich
--ORA-01722: Ungültige Zahl
--Zeile 1
INSERT INTO BUILDING_SURFACES (ID, AREA1, AREA2, AREA3, AREA4, AREA5) VALUES ('BLDG_0003000b002ea10f','674.6703459157907','4212.205538937771','674.6703459158016','5561.546230769363','2714.6367797576822');
--Zeile 2
...

As one can see, the numbers are quoted ('). If I delete the quotes by hand, the insert statement works correctly.

What can I do, so that the import tool does not quote the numbers?
Deleting the quotes with the help of a regular expression is not workaround, since a lot of error windows appear, which needs to be closed by hand...

Thanks in advance,
Richard

Some infos about my machine:

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 12.10
Release: 12.10
Codename: quantal

$ java -version
java version "1.6.0_38"
Java(TM) SE Runtime Environment (build 1.6.0_38-b05)
Java HotSpot(TM) 64-Bit Server VM (build 20.13-b02, mixed mode)

Oracle SQL Developer 3.2.20.09
Version 3.2.20.09
Build MAIN-09.87
Copyright © 2005, 2012 Oracle. All Rights Reserved. Alle Rechte vorbehalten.
IDE Version: 11.1.1.4.37.59.48
Product ID: oracle.sqldeveloper
Product Version: 11.2.0.09.87
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 19 2013
Added on Jan 22 2013
1 comment
2,338 views