Home » RDBMS Server » Server Utilities » SQLLDR ERROR (SQLLDR)
SQLLDR ERROR [message #686578] Sat, 15 October 2022 15:56 Go to next message
gladiator511
Messages: 8
Registered: October 2022
Junior Member
I am trying to insert records into a table using sqlldr.

I am enclosing the table description the sqlldr command file and the error message I get.

ERROR WHEN I TRY TO TO INSERT INTO TESTING_NEW TABLE
SQL*Loader-350: Syntax error at line 15.
Expecting "," or ")", found "NUMBER".
CASH_BALANCE NUMBER ,


...SQLLDR PROBLEM [message #686579 is a reply to message #686578] Sat, 15 October 2022 16:00 Go to previous messageGo to next message
gladiator511
Messages: 8
Registered: October 2022
Junior Member
I am trying to insert records into a table using SQLLDR.

Here is the error that I am getting:
ERROR WHEN I TRY TO TO INSERT INTO TESTING_NEW TABLE
SQL*Loader-350: Syntax error at line 15.
Expecting "," or ")", found "NUMBER".
CASH_BALANCE NUMBER ,

I am enclosing the table description the sqlldr command file and the error message I get.
Re: ...SQLLDR PROBLEM [message #686580 is a reply to message #686579] Sun, 16 October 2022 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most likely, all number fields in SQL*Loader control file must be a CHAR type.

In this file the type is not the target type but the type of the data inside the data file which is almost always CHAR for target columns of string or number types.

Please always post your Oracle version, with 4 decimals (query v$version and for an utility the banner), as often solution depends on it.

[Updated on: Sun, 16 October 2022 00:36]

Report message to a moderator

Re: ...SQLLDR PROBLEM [message #686588 is a reply to message #686580] Tue, 18 October 2022 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 21756
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no NUMBER; use something else, such as INTEGER EXTERNAL (if numbers are integers) or apply the TO_NUMBER function with appropriate format model.

Sample table:

SQL> CREATE TABLE  TESTING_NEW
  2  (
  3     ACCOUNT_DATE                    DATE,
  4     ACCOUNT_NUMBER                  CHAR  (09),
  5     ACCOUNT_NUMBER_OLD              CHAR  (09),
  6     ACCOUNT_NAME                    CHAR  (23),
  7     ACCOUNT_REGISTRATION            CHAR  (23),
  8     CASH_BALANCE                    NUMBER (19,2),
  9     MONEY_ACCOUNTS                  NUMBER (12,2),
 10     PRICED_INVESTMENTS              NUMBER (13,2),
 11     MARGIN_BALANCE                  NUMBER (13,2),
 12     MARKET_VALUE                    NUMBER (13,2)
 13    );

Table created.

SQL>
Control file:
LOAD DATA
INFILE *
REPLACE
INTO TABLE TESTING_NEW 
FIELDS TERMINATED BY '?'  
TRAILING NULLCOLS 
( 
 ACCOUNT_DATE              DATE 'MM/DD/YYYY',
 ACCOUNT_NUMBER            CHAR,
 ACCOUNT_NUMBER_OLD        CHAR,
 ACCOUNT_NAME              CHAR, 
 ACCOUNT_REGISTRATION      CHAR, 
 CASH_BALANCE              "TO_NUMBER(:cash_balance, '999990.00')",
 MONEY_ACCOUNTS            INTEGER EXTERNAL,
 PRICED_INVESTMENTS        INTEGER EXTERNAL,
 MARGIN_BALANCE            INTEGER EXTERNAL,
 MARKET_VALUE              INTEGER EXTERNAL                 
)

BEGINDATA
10/18/2022?ACC12345?ACC98765?MY ACCOUNT?NO IDEA?1000.13?2000?125?100?1250
Loading session and the result:
SQL> $sqlldr scott/tiger@orcl control=test44.ctl log=test44.log

SQL*Loader: Release 18.0.0.0.0 - Production on Uto Lis 18 08:37:31 2022
Version 18.5.0.0.0

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

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

Table TESTING_NEW:
  1 Row successfully loaded.

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

SQL> select * from testing_new;

ACCOUNT_ ACCOUNT_N ACCOUNT_N ACCOUNT_NAME            ACCOUNT_REGISTRATION
-------- --------- --------- ----------------------- -----------------------
CASH_BALANCE MONEY_ACCOUNTS PRICED_INVESTMENTS MARGIN_BALANCE MARKET_VALUE
------------ -------------- ------------------ -------------- ------------
18.10.22 ACC12345  ACC98765  MY ACCOUNT              NO IDEA
     1000,13           2000                125            100         1250


SQL>
Re: ...SQLLDR PROBLEM [message #686599 is a reply to message #686588] Thu, 20 October 2022 14:01 Go to previous messageGo to next message
gladiator511
Messages: 8
Registered: October 2022
Junior Member
MOST OF IT WORKS CORRECTLY.
When I get a line with "total" the numeric fields are shifted one field to the left.
Also, if a field has more than one word, it deletes the spaces.
See attached.
Re: ...SQLLDR PROBLEM [message #686604 is a reply to message #686599] Fri, 21 October 2022 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post your control file as well as your log file.

Re: ...SQLLDR PROBLEM [message #686605 is a reply to message #686604] Fri, 21 October 2022 09:32 Go to previous messageGo to next message
gladiator511
Messages: 8
Registered: October 2022
Junior Member
Post your control file as well as your log file.
I had to put both files into one.
I could not include 2 files
Re: ...SQLLDR PROBLEM [message #686606 is a reply to message #686605] Fri, 21 October 2022 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Using what you posted (create table control file, 2 lines of data) I get:
SQL> CREATE TABLE  TESTING_NEW
  2  (
  3     ACCOUNT_DATE                    DATE,
  4     ACCOUNT_NUMBER             CHAR  (09),
  5    ACCOUNT_NUMBER_OLD    CHAR  (09),
  6     ACCOUNT_NAME                  CHAR  (23),
  7     ACCOUNT_REGISTRATION CHAR (23),
  8     CASH_BALANCE                   NUMBER (19,2),
  9     MONEY_ACCOUNTS            NUMBER (12,2),
 10     PRICED_INVESTMENTS      NUMBER   (13,2),
 11     MARGIN_BALANCE            NUMBER   (13,2),
 12     MARKET_VALUE                 NUMBER (13,2)
 13    )
 14  /

Table created.

SQL> host type c:\testing.log

SQL*Loader: Release 11.2.0.4.0 - Production on Ven. Oct. 21 18:42:42 2022

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

Control File:   c:\t.ctl
Data File:      C:\TESTING_NEW1.TXT
  Bad File:     C:TESTING_BAD.TXT
  Discard File: C:\TESTING_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 TESTING_NEW, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   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 : "to_number(:CASH_BALANCE,'99999999999999999.00')"
MONEY_ACCOUNTS                       NEXT     *   ?       CHARACTER
    SQL string for column : "to_number(:MONEY_ACCOUNTS,'999999999.00')"
PRICED_INVESTMENTS                   NEXT     *   ?       CHARACTER
    SQL string for column : "to_number(:PRICED_INVESTMENTS,'9999999999.00')"
MARGIN_BALANCE                       NEXT     *   ?       CHARACTER
    SQL string for column : "to_number(:MARGIN_BALANCE,'9999999999.00')"
MARKET_VALUE                         NEXT     *   ?       CHARACTER
    SQL string for column : "to_number(:MARKET_VALUE,'9999999999.00')"

Record 1: Rejected - Error on table TESTING_NEW, column CASH_BALANCE.
ORA-01722: invalid number

Record 2: Rejected - Error on table TESTING_NEW, column ACCOUNT_NUMBER.
ORA-12899: value too large for column "MICHEL"."TESTING_NEW"."ACCOUNT_NUMBER" (actual: 14, maximum: 9)


Table TESTING_NEW:
  0 Rows successfully loaded.
  2 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:                 165120 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Ven. Oct.  21 18:42:42 2022
Run ended on Ven. Oct.  21 18:42:42 2022

Elapsed time was:     00:00:00.11
CPU time was:         00:00:00.05

SQL> host type C:\TESTING_NEW1.TXT
08/19/2019?721-30380?721-30380?TEST1 BROKERAGE 123 ?123     ?          0.04          ?0.00          ?408,954.96     ?0.00          ?408,955.00    ?
08/19/2019?Total         ?Total         ?                                                  ?            ?          0.43          ?183,876.39?3,511,193.07  ?0.00         ?3,695,069.89  ?
Re: ...SQLLDR PROBLEM [message #686608 is a reply to message #686606] Sat, 22 October 2022 16:25 Go to previous message
gladiator511
Messages: 8
Registered: October 2022
Junior Member
I deleted everything from the table and reran the program and everything worked correctly.
Previous Topic: External Table INTEGER Conversion to NUMBER is wrong
Next Topic: Sudden degrade in Datapump performance
Goto Forum:
  


Current Time: Sun Jan 29 07:19:36 CST 2023