Home » RDBMS Server » Server Utilities » SQLLDR ERROR (SQLLDR)
SQLLDR ERROR [message #686578] |
Sat, 15 October 2022 15:56 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f1bc60548f153a82ff210ad04fb37f91?s=64&d=mm&r=g) |
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 ,
|
|
|
|
Re: ...SQLLDR PROBLEM [message #686580 is a reply to message #686579] |
Sun, 16 October 2022 00:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 #686606 is a reply to message #686605] |
Fri, 21 October 2022 11:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 ?
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:25:49 CDT 2024
|