ORA-00913: too many values - how to change decimal separator?
PaavoNov 23 2010 — edited Nov 24 2010I want to use SQL Developer's database export and need advice how to tweak the decimal separator from , to . in the sql-inserts created.
Preferrably in SQL Developer, because this error can be easily happen and is hard to catch during import.+
See below how to reproduce this:
h3. 1.) ddl (ugly)
set SERVEROUTPUT on
CREATE TABLE AADECIMEXPORT
(
"DECIMALNUMBER" NUMBER
, "HUBBABUBBA" varchar2(20 byte)
);
CREATE TABLE succeeded.
h3. 2.) inserts by hand
insert into AADECIMEXPORT(DECIMALNUMBER,HUBBABUBBA) values(10,'smells integer');
insert into AADECIMEXPORT(DECIMALNUMBER,HUBBABUBBA) values(3.141592654,'smells rounded pi');
1 rows inserted
1 rows inserted
ยจ
h3. 3.) select * from AADECIMEXPORT
DECIMALNUMBER HUBBABUBBA
---------------------- --------------------
10 SMELLS INTEGER
3,141592654 smells rounded pi
h3. 4.) then use sqldevelopers Tools-"database export" to export this table
--------------------------------------------------------
-- File created - tiistai-marraskuu-23-2010
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table AADECIMEXPORT
--------------------------------------------------------
CREATE TABLE "AADECIMEXPORT"
( "DECIMALNUMBER" NUMBER,
"HUBBABUBBA" VARCHAR2(20)
) ;
---------------------------------------------------
-- DATA FOR TABLE AADECIMEXPORT
-- FILTER = none used
---------------------------------------------------
REM INSERTING into AADECIMEXPORT
Insert into AADECIMEXPORT (DECIMALNUMBER,HUBBABUBBA) values (10,'smells integer');
Insert into AADECIMEXPORT (DECIMALNUMBER,HUBBABUBBA) values (3,141592654,'smells rounded pi');
---------------------------------------------------
-- END DATA FOR TABLE AADECIMEXPORT
---------------------------------------------------
h3. 5.) Test the insert
Insert into AADECIMEXPORT (DECIMALNUMBER,HUBBABUBBA) values (3,141592654,'smells rounded pi');
Error starting at line 49 in command:
Insert into AADECIMEXPORT (DECIMALNUMBER,HUBBABUBBA) values (3,141592654,'smells rounded pi')
Error at Command Line:49 Column:12
Error report:
SQL Error: ORA-00913: too many values
00913. 00000 - "too many values"
*CAUSE:
*Action: