Home » RDBMS Server » Server Utilities » Urgent:Urgent:Sql-Loader Problem
| Urgent:Urgent:Sql-Loader Problem [message #74264] |
Tue, 26 October 2004 02:34  |
BhavinShah
Messages: 105
Registered: February 2004
|
Senior Member |
|
|
Dear frends,
I am using a version of Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
I want a Insert the following textfile data in a table BUY_TRAN.
DATAFILE FOR LOAD:
40 CF 2600 002600000013756 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 2:32:06PM 2 0 2 -543100 GTD Oct 25 2004 2:32:06PM 0 1 0833 N 4920
40 CF 2768 002768000011671 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 11:45:04AM 4 0 4 -540100 GTD Oct 25 2004 11:45:04AM 0 1 0833 N 2693
40 CF 2851 002851000010184 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 11:47:45AM 1 0 1 -550100 GTD Oct 25 2004 11:47:45AM 0 1 0833 N 2721
48 CF 2862 002862000009278 L 2 CL0101 CL0101 62 TR0090 Oct 21 2004 4:59:56PM 3 0 3 -658000 GTD Oct 27 2004 4:59:56PM 0 1 31V031 N 7507
65 CF 2789 002789000012543 L 2 CONT TM0001 12 TR0012 Oct 20 2004 11:28:34AM 2 0 2 -108000 GTC Oct 20 2004 11:28:34AM 0 1 MJC N 2965
40 CF 2600 002600000014026 L 2 CL0101 CL0101 62 TR0090 Oct 21 2004 4:59:32PM 2 0 2 -539000 GTD Oct 27 2004 4:59:32PM 0 1 31V031 N 7486
48 CF 3035 003035000000244 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 4:09:54PM 1 0 1 -673000 GTD Oct 25 2004 4:09:54PM 0 1 0888 N 6587
48 CF 2862 002862000008208 L 2 CL0029 CL0029 53 TR0071 Oct 21 2004 4:10:16PM 1 0 1 -662500 GTD Oct 25 2004 4:10:16PM 0 1 YMCR001 N 6594
40 CF 2851 002851000010849 L 2 CL0029 CL0029 60 TR0110 Oct 21 2004 4:08:36PM 1 0 1 -556100 GTC Oct 21 2004 4:08:36PM 0 1 03PV001 N 6571
48 CF 2862 002862000009213 L 2 CL0101 CL0101 56 TR0085 Oct 21 2004 12:23:27PM 1 0 1 -660300 GTD Oct 29 2004 12:23:27PM 0 1 34C0012 N 3186
65 CF 2861 002861000000085 L 2 CL0029 CL0029 52 TR0081 Oct 19 2004 2:44:08PM 1 0 1 -127200 GTC Oct 19 2004 2:44:08PM 0 1 PRCJ010 N 7231
40 CF 3027 003027000000896 L 2 CL0101 CL0101 56 TR0085 Oct 21 2004 1:40:41PM 1 0 1 -565300 GTD Oct 29 2004 1:40:41PM 0 1 34C0012 N 4349
40 CF 3027 003027000000898 L 2 CL0101 CL0101 12 TR0027 Oct 21 2004 1:43:19PM 2 0 2 -560100 GTD Oct 26 2004 1:43:19PM 0 1 045037 N 4377
40 CF 3027 003027000000899 L 2 CL0101 CL0101 12 TR0027 Oct 21 2004 1:43:44PM 2 0 2 -560000 GTD Oct 26 2004 1:43:44PM 0 1 045000 N 4383
65 CF 2357 002357000002511 L 2 CL0101 CL0101 2 TR0113 Oct 21 2004 4:42:12PM 2 0 2 -115110 GTD Oct 26 2004 4:42:11PM 0 1 29R002 N 7066
40 CF 2768 002768000011680 L 2 CL0101 CL0101 56 TR0085 Oct 21 2004 3:12:38PM 1 0 1 -544100 GTD Oct 29 2004 3:12:37PM 0 1 34C005 N 5594
my TABLE STRUCTURE:
PRODUCTID VARCHAR2(2)
PRODUCTTYPE VARCHAR2(2)
SERIESID VARCHAR2(6)
XACTID VARCHAR2(15)
XACTTYPE VARCHAR2(1)
EFFXACTTYPE VARCHAR2(50)
CLRMEMBERCD VARCHAR2(6)
MEMBERCD VARCHAR2(6)
GROUPID VARCHAR2(2)
TRADERCD VARCHAR2(6)
DTTIME DATE
TOTALQTY VARCHAR2(2)
REPLENISHQTY VARCHAR2(2)
PENDQTY VARCHAR2(2)
PRICE VARCHAR2(2)
ORDERDURATION VARCHAR2(3)
TILLDTTIME DATE
LASTTRADEID VARCHAR2(2)
TRADESESSNO VARCHAR2(2)
CLIENTCD VARCHAR2(11)
CLIENTTYPE VARCHAR2(1)
RECID VARCHAR2(1)
Error in Loader Logfile is :
SQL*Loader: Release 8.1.6.0.0 - Production on Tue Oct 26 15:54:39 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: C:bdscontrolfilesBUY_TRAN.txt
Data File: c:bdsbcpBUY_TRAN.bcp
Bad File: C:bdscontrolfilesBUY_TRAN.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table BUY_TRAN, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PRODUCTID FIRST * WHT CHARACTER
PRODUCTTYPE NEXT * WHT CHARACTER
SERIESID NEXT * WHT CHARACTER
XACTID NEXT * WHT CHARACTER
XACTTYPE NEXT * WHT CHARACTER
EFFXACTTYPE NEXT * WHT CHARACTER
CLRMEMBERCD NEXT * WHT CHARACTER
MEMBERCD NEXT * WHT CHARACTER
GROUPID NEXT * WHT CHARACTER
TRADERCD NEXT * WHT CHARACTER
DTTIME NEXT * WHT CHARACTER
TOTALQTY NEXT * WHT CHARACTER
REPLENISHQTY NEXT * WHT CHARACTER
PENDQTY NEXT * WHT CHARACTER
PRICE NEXT * WHT CHARACTER
ORDERDURATION NEXT * WHT CHARACTER
TILLDTTIME NEXT * WHT CHARACTER
LASTTRADEID NEXT * WHT CHARACTER
TRADESESSNO NEXT * WHT CHARACTER
CLIENTCD NEXT * WHT CHARACTER
CLIENTTYPE NEXT * WHT CHARACTER
RECID NEXT * WHT CHARACTER
Record 1: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 2: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 3: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 4: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 5: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 6: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 7: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 8: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 9: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 10: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 11: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 12: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 13: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 14: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 15: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 16: Rejected - Error on table BUY_TRAN, column DTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Table BUY_TRAN:
0 Rows successfully loaded.
16 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: 62436 bytes(11 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 16
Total logical records rejected: 16
Total logical records discarded: 0
Run began on Tue Oct 26 15:54:39 2004
Run ended on Tue Oct 26 15:54:40 2004
Elapsed time was: 00:00:00.81
CPU time was: 00:00:00.09
control file:
LOAD DATA
infile 'C:bdsbcpBUY_TRAN.bcp'
into table BUY_TRAN
fields terminated by " "
( ProductId ,
ProductType ,
SeriesId ,
XactId ,
XactType ,
EffXactType ,
ClrMemberCd ,
MemberCd ,
GroupId ,
TraderCd ,
DtTime ,
TotalQty ,
ReplenishQty ,
PendQty ,
Price ,
OrderDuration ,
TillDtTime ,
LastTradeId ,
TradeSessNo ,
ClientCd ,
ClientType ,
RecId )
Here my DTTIME COLUMN IN A TABLE IS DATE datatype but i want to insert a date which is in format "mon dd yyy hh:mm:ss:am".
Pl. give me solution as early as possible...It will be great help for me..
Regards,
Bhavin Shah
|
|
|
|
| Re: Urgent:Urgent:Sql-Loader Problem [message #74268 is a reply to message #74264] |
Tue, 26 October 2004 05:18  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Your field-termination character is a space, so I'm guessing that SQL*Loader naturally considers the following space to be a field delimiter:
----------------------------------------------------------------------
40 CF 2600 002600000013756 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 2:32:06PM 2 0 2 -543100 GTD Oct 25 2004 2:32:06PM 0 1 0833 N 4920
^
|
----------------------------------------------------------------------
If this were me, I would use positional notation instead of delimiters. When I pasted your data into Notepad, only row 15 (with its single-digit GROUPID of 2) looked out of whack.
So you could have your file as:
----------------------------------------------------------------------
40 CF 2600 002600000013756 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 2:32:06PM 2 0 2 -543100 GTD Oct 25 2004 2:32:06PM 0 1 0833 N 4920
40 CF 2768 002768000011671 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 11:45:04AM 4 0 4 -540100 GTD Oct 25 2004 11:45:04AM 0 1 0833 N 2693
40 CF 2851 002851000010184 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 11:47:45AM 1 0 1 -550100 GTD Oct 25 2004 11:47:45AM 0 1 0833 N 2721
48 CF 2862 002862000009278 L 2 CL0101 CL0101 62 TR0090 Oct 21 2004 4:59:56PM 3 0 3 -658000 GTD Oct 27 2004 4:59:56PM 0 1 31V031 N 7507
65 CF 2789 002789000012543 L 2 CONT TM0001 12 TR0012 Oct 20 2004 11:28:34AM 2 0 2 -108000 GTC Oct 20 2004 11:28:34AM 0 1 MJC N 2965
40 CF 2600 002600000014026 L 2 CL0101 CL0101 62 TR0090 Oct 21 2004 4:59:32PM 2 0 2 -539000 GTD Oct 27 2004 4:59:32PM 0 1 31V031 N 7486
48 CF 3035 003035000000244 L 2 CL0101 CL0101 61 TR0098 Oct 21 2004 4:09:54PM 1 0 1 -673000 GTD Oct 25 2004 4:09:54PM 0 1 0888 N 6587
48 CF 2862 002862000008208 L 2 CL0029 CL0029 53 TR0071 Oct 21 2004 4:10:16PM 1 0 1 -662500 GTD Oct 25 2004 4:10:16PM 0 1 YMCR001 N 6594
40 CF 2851 002851000010849 L 2 CL0029 CL0029 60 TR0110 Oct 21 2004 4:08:36PM 1 0 1 -556100 GTC Oct 21 2004 4:08:36PM 0 1 03PV001 N 6571
48 CF 2862 002862000009213 L 2 CL0101 CL0101 56 TR0085 Oct 21 2004 12:23:27PM 1 0 1 -660300 GTD Oct 29 2004 12:23:27PM 0 1 34C0012 N 3186
65 CF 2861 002861000000085 L 2 CL0029 CL0029 52 TR0081 Oct 19 2004 2:44:08PM 1 0 1 -127200 GTC Oct 19 2004 2:44:08PM 0 1 PRCJ010 N 7231
40 CF 3027 003027000000896 L 2 CL0101 CL0101 56 TR0085 Oct 21 2004 1:40:41PM 1 0 1 -565300 GTD Oct 29 2004 1:40:41PM 0 1 34C0012 N 4349
40 CF 3027 003027000000898 L 2 CL0101 CL0101 12 TR0027 Oct 21 2004 1:43:19PM 2 0 2 -560100 GTD Oct 26 2004 1:43:19PM 0 1 045037 N 4377
40 CF 3027 003027000000899 L 2 CL0101 CL0101 12 TR0027 Oct 21 2004 1:43:44PM 2 0 2 -560000 GTD Oct 26 2004 1:43:44PM 0 1 045000 N 4383
65 CF 2357 002357000002511 L 2 CL0101 CL0101 2 TR0113 Oct 21 2004 4:42:12PM 2 0 2 -115110 GTD Oct 26 2004 4:42:11PM 0 1 29R002 N 7066
40 CF 2768 002768000011680 L 2 CL0101 CL0101 56 TR0085 Oct 21 2004 3:12:38PM 1 0 1 -544100 GTD Oct 29 2004 3:12:37PM 0 1 34C005 N 5594
----------------------------------------------------------------------
and your control file as:
----------------------------------------------------------------------
LOAD DATA
INFILE 'C:bdsbcpBUY_TRAN.bcp'
INTO TABLE buy_tran (
productid POSITION(001:002) CHAR
, producttype POSITION(004:005) CHAR
, seriesid POSITION(007:010) CHAR
, xactid POSITION(012:026) CHAR
, xacttype POSITION(028:028) CHAR
, effxacttype POSITION(030:030) CHAR
, clrmembercd POSITION(032:037) CHAR TERMINATED BY WHITESPACE
, membercd POSITION(039:044) CHAR
, groupid POSITION(046:047) CHAR "TRIM(:groupid)"
, tradercd POSITION(049:054) CHAR
, dttime POSITION(056:077) DATE 'Mon fmDD YYYY HH:fmMI:SSAM'
, totalqty POSITION(079:079) CHAR
, replenishqty POSITION(081:081) CHAR
, pendqty POSITION(083:083) CHAR
, price POSITION(085:091) CHAR
, orderduration POSITION(093:095) CHAR
, tilldttime POSITION(097:118) DATE 'Mon fmDD YYYY HH:fmMI:SSAM'
, lasttradeid POSITION(120:120) CHAR
, tradesessno POSITION(122:122) CHAR
, clientcd POSITION(124:134) CHAR TERMINATED BY WHITESPACE
, clienttype POSITION(136:136) CHAR
, recid POSITION(138:141) CHAR
)
----------------------------------------------------------------------
Note, I ran into problems with too-short field lengths: your table had price defined as a VARCHAR2(2), but in your file had a length of 7? Also, shouldn't a field like price have a datatype of NUMBER?
The other column definition that gave me problems was recid. Your table has it defined with a length of 1, but in the file, this column had a length of 4.
Let's be careful out there.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 01:43:27 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
