Home » SQL & PL/SQL » SQL & PL/SQL » how do i insert the value like 232322.456 through sql loader
how do i insert the value like 232322.456 through sql loader [message #18579] Wed, 06 February 2002 04:58 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
I have a table tab1 with 2 columns
col1 number null,
col2 number null

I want to load the data through sql loader,but it is giving errors
ora-01722

sample data in sample.dat file is
-----------------
3372756.88,5673423
3330140.88,5691491

control file is
-----------------
LOAD DATA
APPEND
INTO TABLE tab1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
col1 "TO_NUMBER(:col1,'$99999.99')",
col2 "TO_NUMBER(:col2,'$99999.99')" )

I have tried the datatype char also,still it does not work.

It is urgent and i need to port the data...

any help is highly appreciated
Re: how do i insert the value like 232322.456 through sql loader [message #18583 is a reply to message #18579] Wed, 06 February 2002 06:26 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
what is col1 datatype?
Re: how do i insert the value like 232322.456 through sql loader [message #18585 is a reply to message #18583] Wed, 06 February 2002 07:45 Go to previous messageGo to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
the table structure is like this
table(col1 number null,col2 number null)

i want to insert the value like 123424.34,837377.34 through sql loader and it fails giving me error ora-01722 invalid number
Re: how do i insert the value like 232322.456 through sql loader [message #18587 is a reply to message #18583] Wed, 06 February 2002 08:28 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
you can't insert $ symbol into number column even from sqlplus.

you should be able to insert number values with out any error if you remove that format.
Re: how do i insert the value like 232322.456 through sql loader [message #18594 is a reply to message #18583] Thu, 07 February 2002 01:21 Go to previous messageGo to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
The $ format was removed,it was a mistake while copying.
I have removed the $ format and still it does not work.
Any suggestions
Re: how do i insert the value like 232322.456 through sql loader [message #18600 is a reply to message #18583] Thu, 07 February 2002 04:36 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member

SQL> create table tab1(col1 number,col2 number);

Table created.

SQL> select * from tab1;

no rows selected

datafile:(samp.dat)

3372756.881,5673423 
3330140.566,5691491 

control file:

LOAD DATA 
infile samp.dat
APPEND 
INTO TABLE tab1 
FIELDS TERMINATED BY "," optionally enclosed by '"'
TRAILING NULLCOLS 
( 
col1  , 
col2   ) 

SQL> select * from tab1;

      COL1       COL2
---------- ----------
3372756.88    5673423
3330140.57    5691491

SQL> select to_char(col1,'99999999.999'),col2 from tab1;

TO_CHAR(COL1,       COL2
------------- ----------
  3372756.881    5673423
  3330140.566    5691491

Re: how do i insert the value like 232322.456 through sql loader [message #18603 is a reply to message #18579] Thu, 07 February 2002 05:04 Go to previous message
INTROV
Messages: 20
Registered: February 2002
Junior Member
You should define just the column names, col1,col2 in the control file.
Change the following in the control file from
col1 "TO_NUMBER(:col1,'$99999.99')",
col2 "TO_NUMBER(:col2,'$99999.99')"
to
col1,col2

and this should work.
Previous Topic: Day of week
Next Topic: Indexes
Goto Forum:
  


Current Time: Mon Sep 27 16:27:17 CDT 2021