Home » RDBMS Server » Server Utilities » SQLLOADER (DATABASE 12C XE)
SQLLOADER [message #685999] Mon, 23 May 2022 15:19 Go to next message
Wideminnow6970
Messages: 21
Registered: January 2019
Junior Member
When I run SQLLOADER, the VARCHAR2 fields come back with the correct information.
When I run SQLLOADER, the Number fields come back with totally wrong data.
************************************************************************************
Her are my results!
************************************************************************************
My table description
SQL> DESC TABLE1
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_DATE DATE
ACCOUNT_NUMBER VARCHAR2(30)
ACCOUNT_NUMBER_OLD VARCHAR2(30)
ACCOUNT_NAME VARCHAR2(23)
ACCOUNT_REGISTRATION VARCHAR2(40)
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)
************************************************************************************************************



************************************************************************************************************


SQLLOADER RESULTS
************************************************************************************************************
SQL*Loader: Release 21.0.0.0.0 - Production on Mon May 23 15:41:21 2022
Version 21.3.0.0.0

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

Control File: J:\ORACLE_LOAD\INSERT_merrill.ctl
Data File: H:\TESTING\TEST2.TXT
Bad File: J:\MERRILLOUTPUT\MERRILL.BAD
Discard File: J:\MERRILLOUTPUT\MERRILL_DISCARD.TXT
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 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 4 INTEGER
MONEY_ACCOUNTS NEXT 4 INTEGER
PRICED_INVESTMENTS NEXT 4 INTEGER
MARGIN_BALANCE NEXT 4 INTEGER
MARKET_VALUE NEXT 4 INTEGER


Table MERRILL:
7 Rows successfully loaded.
0 Rows 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: 327500 bytes(250 rows)
Read buffer bytes: 1048576

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

Run began on Mon May 23 15:41:21 2022
Run ended on Mon May 23 15:41:42 2022

Elapsed time was: 00:00:21.05
CPU time was: 00:00:00.06

**************************************************************************************************************
MY INPUT FILE
************************************************************************************************************
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?
*****************************************************************************************************************

MY OUTPUT FILE
*****************************************************************************************************************
DATE ACCOUNT_NUMBER_OLD ACCOUNT_NAME REGISTRATION CASH_BALANCE MONEY_ACCOUNTS PRICED_INVESTMENTS MARGIN_BALANCE MARKET_VALUE
------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------

31-DEC-18 511-29462 857-29462 JOHN CD1 538976288 538976288 774905888 541012016 538976288
31-DEC-18 511-29463 857-29463 SAL CD2 538976288 538976288 774905888 541012016 538976288
31-DEC-18 511-29464 857-29464 MARIA CD3 538976288 538976288 774905888 541012016 538976288
31-DEC-18 511-29465 857-29465 PETER CD4 538976288 538976288 774905888 541012016 538976288
31-DEC-18 511-29466 857-29466 MARVIN CD5 538976288 538976288 774905888 541012016 538976288
31-DEC-18 511-29493 857-29493 RALPH CD6 538976288 538976288 774905888 541012016 538976288
31-DEC-18 Total Total 538976288 538976288 774905888 541012016 538976288

7 rows selected.

[Updated on: Mon, 23 May 2022 18:22] by Moderator

Report message to a moderator

Re: SQLLOADER [message #686000 is a reply to message #685999] Mon, 23 May 2022 18:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10703
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And what would be the contents of your control file or sqlldr commandline?
Re: SQLLOADER [message #686002 is a reply to message #685999] Tue, 24 May 2022 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68257
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

All fields should be defined as CHAR in the control file.
If you define some as INTEGER this means the data file contains the OS internal representation (binary dump) of an integer.

Re: SQLLOADER [message #686007 is a reply to message #686000] Tue, 24 May 2022 09:31 Go to previous messageGo to next message
Wideminnow6970
Messages: 21
Registered: January 2019
Junior Member
See attached for the CTL file
Re: SQLLOADER [message #686008 is a reply to message #686002] Tue, 24 May 2022 09:37 Go to previous messageGo to next message
Wideminnow6970
Messages: 21
Registered: January 2019
Junior Member
I changed all the Integer to CHAR.
When I run the SQLLOADER, the only record that comes back is for RALPH which contains all zeros.
See attached log file for the errors.
  • Attachment: log_file.txt
    (Size: 2.92KB, Downloaded 129 times)
Re: SQLLOADER [message #686009 is a reply to message #686007] Tue, 24 May 2022 11:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10703
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Works for me.

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
    )
Re: SQLLOADER [message #686010 is a reply to message #686008] Tue, 24 May 2022 11:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10703
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Is that the same input file you used early?
Why 8 records? Probably the input file has issues (in this case).
Re: SQLLOADER [message #686011 is a reply to message #686008] Tue, 24 May 2022 12:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10703
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I changed all the Integer to CHAR.

Looks like you did not fix the table.
Re: SQLLOADER [message #686014 is a reply to message #686008] Tue, 24 May 2022 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68257
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The commas in the numbers hurt.
You have to modify your control file like this:
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>
The last line is not imported as it is obviously not correct.

The alternative is to generate an input file without the commas in the numbers.

[Updated on: Tue, 24 May 2022 13:06]

Report message to a moderator

Re: SQLLOADER [message #686021 is a reply to message #686009] Wed, 25 May 2022 10:23 Go to previous message
Wideminnow6970
Messages: 21
Registered: January 2019
Junior Member
I changed Integer to Char.
It works the way that I wanted.
Thanks for the quick response.
Previous Topic: Update multiple table and rows in one go
Next Topic: ORA-39083,ORA-29821,ORA-06598
Goto Forum:
  


Current Time: Mon Aug 08 04:35:13 CDT 2022