Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using sqlldr to load a file which has a numerical value as one of its fields.

The problem is that in some of the records the number has commas whereas in other, it doesn't.

So doing something like

num "to_number(:num, '999,999,999.99')",

loads the records with commas but not those without commas and doing

num "to_number(:num)",

loads only those without commas.

Is there a way to tell Oracle (sqlldr) that the comma is optional?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

Here you go:

SQL> desc loadertst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> !cat loadertst.ctl
load data
infile *
into table loadertst
fields terminated by ',' enclosed by '"'
(
col1 "to_number(replace(:col1,',',''))"
)
begindata
"123456"
"1,2,3,4,5,6"

SQL> !sqlldr phil/phil control=loadertst.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Nov 29 23:33:17 2012

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

Commit point reached - logical record count 2

SQL> select count(*) from loadertst;

  COUNT(*)
----------
         2

SQL>

Obviously you may need to fiddle with the fields terminated by ',' enclosed by '"' part to suit your data.

share|improve this answer
    
OK, this works; I ended up doing something similar before seeing your answer. I guess, at first, my brain had been looking for a way of using the format model to 'logically' say that the value may or may not contain commas. –  Isaac Kleinman Nov 30 '12 at 15:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.