How do I load a column from a CSV file with the format #,##0.## to a table using SQL Loader?

Question

I'm trying to load a CSV file that includes three columns: DEBIT, CREDIT, and PREVIOUS_BALANCE_AMOUNT. The columns are formatted in custom format: #,##0.##. When I tried loading the columns in using SQL Loader as NUMBER, I would get an error like:

ORA-01722: invalid number

How would I be able to load the columns in properly?

I'm expecting SQL Loader to be able to read in the data using NUMBER, but this doesn't seem to work. I'm very new to the platform, so apologize for not providing more details.

Answer 1

Sample table:

SQL> create table test (debit number, credit number);

Table created.

Control file:

load data
infile *
replace
into table test
fields terminated by '|'
trailing nullcols
(
  credit  "to_number(:credit, '9,990.99')",
  debit   "to_number(:debit , '9,990.99')"
)

begindata
1,233.56|8,130.15
3,123.43|7,323.12

Loading session:

SQL> $sqlldr scott/tiger@pdb1 control=test14.ctl log=test14.log

SQL*Loader: Release 21.0.0.0.0 - Production on Tue Jun 13 22:55:04 2023
Version 21.3.0.0.0

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

Path used:      Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test14.log
for more information about the load.

Result:

SQL> select * from test;

     DEBIT     CREDIT
---------- ----------
   8130.15    1233.56
   7323.12    3123.43

SQL>