OraFAQ Forum: Server Utilities » To_number sqlldr control file

Home » RDBMS Server » Server Utilities » To_number sqlldr control file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To_number sqlldr control file [message #552286]

Tue, 24 April 2012 21:16

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

Report message to a moderator

Re: To_number sqlldr control file [message #552288 is a reply to message #552286]

Tue, 24 April 2012 22:28

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: To_number sqlldr control file [message #552291 is a reply to message #552288]

Tue, 24 April 2012 23:22

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe the problem is there:
t_num expression "to_number(':tnum,'99999.999')")

Regards
Michel

Report message to a moderator

Re: To_number sqlldr control file [message #552292 is a reply to message #552291]

Tue, 24 April 2012 23:26

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Previous Topic:	expdp query
Next Topic:	sql loader error for direct path(3 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:40:45 CST 2025