Number Conversion With SQL Loader In DIRECT Path Mode - UPDATED
UPDATE 19/10/2003 : According to this article by Sanjay Mishra, the feature discussed in this posting (using SQL expressions in Direct Path mode) is only available from Oracle 9i upwards. It therefore won't work for Jon's particular problem, although it would work on an Oracle 9i database. Sorry for any confusion.
Jon Mead came up with the following problem:
"I have a problem whereby I am loading data from a flat file using OWB, the data I want to import is a number, but in the file it is represented in the format 1,234,567.
I would like to be able to load this straight into a column of NUMBER datatype, however I am getting an invalid number error.
I have tried to use the TO_NUMBER function in the OWB mapping (in between the file and the table) but I get a SQL loader error - 417 SQL String XXX not allowed in direct path.
I have also tried to use a format mask, but OWB does not like this at all.
The database is 8.1.7, so I can't use external tables, and I am aware I could resolve the issue using 2 mappings, but this will double the workload and maintenance (there are a large number of file with this number format). Does anyone have any ideas how to resolve this in one mapping from the file to the table?"
I've managed to get this to work, but it's involved a workaround as i'll explain later on. Also - this was done using an Oracle 9i Release 2 database, and it might not work with SQLLDR on Oracle 8.1.7
First of all, define your flat file source module, and when you come to sample the file, select CHAR as the type and leave the MASK column blank.
Then, define your mapping, bring in your target table (with the target column defined as a number datatype), and drag across the column from source to target.
Deploy your mapping now, but don't run it. If you ran it, you'd get an error as the data in the source column is in the format 999,999,999.99 and would be an invalid number.
Open up the .ctl file that you've deployed, and append to the end of each column that needs converting, the string :
"TO_NUMBER(:c1,'999,999,999.99')"
where :c1 is the name of the field you're converting.
An example CTL file with this extra bit in is as follows;
OPTIONS ( DIRECT=TRUE,PARALLEL=FALSE, ERRORS=50, BINDSIZE=50000, ROWS=200, READSIZE=65536)
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE c:\test2.txt' "STR X'0A'"
READBUFFERS 4
INTO TABLE "JMEAD"
APPEND
REENABLE DISABLED_CONSTRAINTS
FIELDS
(
"C1" POSITION (1:13) CHAR "TO_NUMBER(:c1,'999,999,999.99')"
)
Make sure the DIRECT_MODE entry in the mapping configuration is set to TRUE, as this speeds up the load.
I then used the following test data;
3,343,654.56
12,634,765.45
354,644.00
454.00
And the load ran ok, with the following log file;
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table "JMEAD", loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype