Skip to Main Content

SQL Developer

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-00913: too many values - how to change decimal separator?

PaavoNov 23 2010 — edited Nov 24 2010
I 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:
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 22 2010
Added on Nov 23 2010
6 comments
2,710 views