oracle@kapi#sqlldr dbadmin/xxx control=m.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Tue May 24 12:42:45 2022

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

Path used:      Conventional
Commit point reached - logical record count 7

Table MERRILL:
  7 Rows successfully loaded.

Check the log file:
  m.log
for more information about the load.
oracle@kapi#cat m.ctl
LOAD DATA
INFILE 'm.txt'
REPLACE INTO TABLE MERRILL
FIELDS TERMINATED BY '?'
(
   ACCOUNT_DATE date "MM/DD/YYYY",
   ACCOUNT_NUMBER CHAR  ,
   ACCOUNT_NUMBER_OLD CHAR  ,
   ACCOUNT_NAME CHAR  ,
   ACCOUNT_REGISTRATION CHAR  ,
   CASH_BALANCE  CHAR  ,
   MONEY_ACCOUNTS    CHAR    ,
   PRICED_INVESTMENTS   CHAR   ,
   MARGIN_BALANCE   CHAR    ,
   MARKET_VALUE      CHAR
    )

SQL> host type c:\merrill.ctl
LOAD DATA
INFILE 'C:\MERRILL_TEST.TXT'
BADFILE'C:\MERRILL_BAD.TXT'
DISCARDFILE 'C:\MERRILL_DISCARD.TXT'
REPLACE INTO TABLE MERRILL
FIELDS TERMINATED BY '?'
(
   ACCOUNT_DATE date 'MM/DD/YYYY',
   ACCOUNT_NUMBER CHAR  ,
   ACCOUNT_NUMBER_OLD CHAR  ,
   ACCOUNT_NAME CHAR  ,
   ACCOUNT_REGISTRATION CHAR  ,
   CASH_BALANCE  CHAR  "REPLACE(:CASH_BALANCE, ',', '')" ,
   MONEY_ACCOUNTS    CHAR  "REPLACE(:MONEY_ACCOUNTS, ',', '')"  ,
   PRICED_INVESTMENTS   CHAR "REPLACE(:PRICED_INVESTMENTS, ',', '')"  ,
   MARGIN_BALANCE   CHAR  "REPLACE(:MARGIN_BALANCE, ',', '')"  ,
   MARKET_VALUE      CHAR "REPLACE(:MARKET_VALUE, ',', '')"
    )

SQL> host type c:\merrill_test.txt
12/31/2018?511-29462?857-29462? JOHN ?CD1 ? 0.00? 0.00? 0.00? 88,597.36? 88,597.36?
12/31/2018?511-29463?857-29463? SAL ?CD2 ? 0.00? 0.00? 0.00? 26,222.81? 26,222.81?
12/31/2018?511-29464?857-29464? MARIA ?CD3 ? 0.00? 0.00? 0.00? 1,087,258.13? 1,087,258.13?
12/31/2018?511-29465?857-29465? PETER ?CD4 ? 0.00? 0.00? 0.00? 39,115.30? 39,115.30?
12/31/2018?511-29466?857-29466? MARVIN ?CD5 ? 0.00? 0.00? 0.00? 2,006,808.83? 2,006,808.83?
12/31/2018?511-29493?857-29493? RALPH ?CD6 ? 0.00? 0.00? 0.00? 0.00? 0.00?
12/31/2018? ?Total ? ?Total ? ? 0.00? 0.00? 0.00? 3 ,248,002.43? 3,248,002.43?

SQL> desc merrill
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ACCOUNT_DATE                              DATE
 ACCOUNT_NUMBER                            VARCHAR2(30 CHAR)
 ACCOUNT_NUMBER_OLD                        VARCHAR2(30 CHAR)
 ACCOUNT_NAME                              VARCHAR2(23 CHAR)
 ACCOUNT_REGISTRATION                      VARCHAR2(40 CHAR)
 CASH_BALANCE                              NUMBER(11,2)
 MONEY_ACCOUNTS                            NUMBER(11,2)
 PRICED_INVESTMENTS                        NUMBER(11,2)
 MARGIN_BALANCE                            NUMBER(11,2)
 MARKET_VALUE                              NUMBER(11,2)

SQL> select * from merrill;

no rows selected

SQL> host sqlldr michel/michel control=c:\MERRILL.CTL

SQL*Loader: Release 11.2.0.4.0 - Production on Mar. Mai 24 20:01:20 2022

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

Commit point reached - logical record count 7

SQL> select * from merrill;
ACCOUNT_DATE        ACCOUNT_NUMBER                 ACCOUNT_NUMBER_OLD             ACCOUNT_NAME
------------------- ------------------------------ ------------------------------ -----------------------
ACCOUNT_REGISTRATION                     CASH_BALANCE MONEY_ACCOUNTS PRICED_INVESTMENTS MARGIN_BALANCE MARKET_VALUE
---------------------------------------- ------------ -------------- ------------------ -------------- ------------
31/12/2018 00:00:00 511-29462                      857-29462                       JOHN
CD1                                                 0              0                  0       88597.36     88597.36
31/12/2018 00:00:00 511-29463                      857-29463                       SAL
CD2                                                 0              0                  0       26222.81     26222.81
31/12/2018 00:00:00 511-29464                      857-29464                       MARIA
CD3                                                 0              0                  0     1087258.13   1087258.13
31/12/2018 00:00:00 511-29465                      857-29465                       PETER
CD4                                                 0              0                  0        39115.3      39115.3
31/12/2018 00:00:00 511-29466                      857-29466                       MARVIN
CD5                                                 0              0                  0     2006808.83   2006808.83
31/12/2018 00:00:00 511-29493                      857-29493                       RALPH
CD6                                                 0              0                  0              0            0

6 rows selected.

SQL> host type c:\merrill.log

SQL*Loader: Release 11.2.0.4.0 - Production on Mar. Mai 24 20:01:20 2022

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

Control File:   c:\MERRILL.CTL
Data File:      C:\MERRILL_TEST.TXT
  Bad File:     C:\MERRILL_BAD.TXT
  Discard File: C:\MERRILL_DISCARD.TXT
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table MERRILL, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ACCOUNT_DATE                        FIRST     *   ?       DATE MM/DD/YYYY
ACCOUNT_NUMBER                       NEXT     *   ?       CHARACTER
ACCOUNT_NUMBER_OLD                   NEXT     *   ?       CHARACTER
ACCOUNT_NAME                         NEXT     *   ?       CHARACTER
ACCOUNT_REGISTRATION                 NEXT     *   ?       CHARACTER
CASH_BALANCE                         NEXT     *   ?       CHARACTER
    SQL string for column : "REPLACE(:CASH_BALANCE, ',', '')"
MONEY_ACCOUNTS                       NEXT     *   ?       CHARACTER
    SQL string for column : "REPLACE(:MONEY_ACCOUNTS, ',', '')"
PRICED_INVESTMENTS                   NEXT     *   ?       CHARACTER
    SQL string for column : "REPLACE(:PRICED_INVESTMENTS, ',', '')"
MARGIN_BALANCE                       NEXT     *   ?       CHARACTER
    SQL string for column : "REPLACE(:MARGIN_BALANCE, ',', '')"
MARKET_VALUE                         NEXT     *   ?       CHARACTER
    SQL string for column : "REPLACE(:MARKET_VALUE, ',', '')"

Record 7: Rejected - Error on table MERRILL, column CASH_BALANCE.
ORA-01722: invalid number


Table MERRILL:
  6 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 165120 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Mar. Mai   24 20:01:20 2022
Run ended on Mar. Mai   24 20:01:20 2022

Elapsed time was:     00:00:00.12
CPU time was:         00:00:00.07

SQL>