Home » RDBMS Server » Server Utilities » To_number sqlldr control file
To_number sqlldr control file [message #552286] Tue, 24 April 2012 21:16 Go to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Hello,

I have a csv file extracted from mainframe which has to be loaded into oracle using sqlldr utility.The numbers are in the format +0000003333, -0000003232.44 etc

I have to convert it to 3333 and -3232.44 and insert into the table.

I have used syntax like

Load file....
append into table
(t_num expression "to_number(':tnum,'99999.999')")

This gives me an invalid number error. Any help is highly appreciated.


Thanks
Nammu

Re: To_number sqlldr control file [message #552288 is a reply to message #552286] Tue, 24 April 2012 22:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The following works for me. Does it work for you? If not, then please post an example showing what you get, as I have done below, your Oracle version and the result of the following query:

select parameter, value from v$nls_parameters order by parameter;

SCOTT@orcl_11gR2> host type test.ctl
load data
infile *
into table test_tab
fields terminated by ','
trailing nullcols
(tnum)
begindata:
+0000003333
-0000003232.44

SCOTT@orcl_11gR2> create table test_tab
  2    (tnum  number (8, 3))
  3  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 11.2.0.1.0 - Production on Tue Apr 24 20:24:36 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2

SCOTT@orcl_11gR2> select * from test_tab
  2  /

      TNUM
----------
      3333
  -3232.44

2 rows selected.

Re: To_number sqlldr control file [message #552291 is a reply to message #552288] Tue, 24 April 2012 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe the problem is there:
t_num expression "to_number(':tnum,'99999.999')")

Regards
Michel

Re: To_number sqlldr control file [message #552292 is a reply to message #552291] Tue, 24 April 2012 23:26 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Tue, 24 April 2012 21:22
Maybe the problem is there:
t_num expression "to_number(':tnum,'99999.999')")

Regards
Michel



That looks like it was typed, not copied and pasted. Not only are the names different, but there is an extra single quote that does not belong there. However, even if you fix all of that, it throws an error. Just using tnum without anything else or with integer external works for me.
Previous Topic: expdp query
Next Topic: sql loader error for direct path(3 Merged)
Goto Forum:
  


Current Time: Sat Jan 18 00:00:26 CST 2020