Home » RDBMS Server » Server Utilities » SQL loader (oracle 10g)
| SQL loader [message #593618] |
Mon, 19 August 2013 05:06  |
 |
satheeshsharma
Messages: 11
Registered: August 2013
Location: Chennai
|
Junior Member |
|
|
This is my dat.file
200100 1000.00 600.00 08-JAN-08 C00015 A003 SOD
200110 3000.00 500.00 15-APR-08 C00019 A010 SOD
200107 4500.00 900.00 30-AUG-08 C00007 A010 SOD
200112 2000.00 400.00 30-MAY-08 C00016 A007 SOD
200113 4000.00 600.00 10-JUN-08 C00022 A002 SOD
200102 2000.00 300.00 25-MAY-08 C00012 A012 SOD
200114 3500.00 1500.00 15-AUG-08 C00002 A008 SOD
200122 2500.00 400.00 16-SEP-08 C00003 A004 SOD
200118 500.00 100.00 20-JUL-08 C00023 A006 SOD
200119 4000.00 700.00 16-SEP-08 C00007 A010 SOD
200121 1500.00 600.00 23-SEP-08 C00008 A004 SOD
200130 2500.00 400.00 30-JUL-08 C00025 A011 SOD
200134 4200.00 1800.00 25-SEP-08 C00004 A005 SOD
200115 2000.00 1200.00 08-FEB-08 C00013 A013 SOD
200108 4000.00 600.00 15-FEB-08 C00008 A004 SOD
200103 1500.00 700.00 15-MAY-08 C00021 A005 SOD
200105 2500.00 500.00 18-JUL-08 C00025 A011 SOD
200109 3500.00 800.00 30-JUL-08 C00011 A010 SOD
200101 3000.00 1000.00 15-JUL-08 C00001 A008 SOD
200111 1000.00 300.00 10-JUL-08 C00020 A008 SOD
200104 1500.00 500.00 15-MAR-08 C00006 A004 SOD
200106 2500.00 700.00 20-APR-08 C00005 A002 SOD
200125 2000.00 600.00 16-OCT-08 C00018 A005 SOD
200117 800.00 200.00 20-OCT-08 C00014 A001 SOD
200123 500.00 100.00 16-SEP-08 C00022 A002 SOD
200120 500.00 100.00 20-JUL-08 C00009 A002 SOD
200116 500.00 100.00 13-JUL-08 C00010 A009 SOD
200124 500.00 100.00 20-JUN-08 C00017 A007 SOD
200126 500.00 100.00 24-JUN-08 C00022 A002 SOD
200129 2500.00 500.00 20-JUL-08 C00024 A006 SOD
200127 2500.00 400.00 20-JUL-08 C00015 A003 SOD
200128 3500.00 1500.00 20-JUL-08 C00009 A002 SOD
200135 2000.00 800.00 16-SEP-08 C00007 A010 SOD
200131 900.00 150.00 26-AUG-08 C00012 A012 SOD
200133 1200.00 400.00 29-JUN-08 C00009 A002 SOD
200132 4000.00 2000.00 15-AUG-08 C00013 A013 SOD
This is my control file
load data
infile 'F:\sat.csv'
into table orders
fields terminated by ' '
(ord_number,ord_amount,advanced_amount,ord_date,cust_code,agent_code,ord_description)
When i am loading data i m getting error like this
C:\Documents and Settings\Administrator>sqlplus one/two
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 15:31:03 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> desc orders
Name Null? Type
----------------------------------------- -------- ----------------------------
ORD_NUMBER NUMBER
ORD_AMOUNT NUMBER(8,2)
ADVANCED_AMOUNT NUMBER(8,2)
ORD_DATE DATE
CUST_CODE VARCHAR2(10)
AGENT_CODE VARCHAR2(10)
ORD_DESCRIPTION VARCHAR2(10)
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>notepad me.ctl
C:\Documents and Settings\Administrator>sqlldr one/two me.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 15:32:00 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 35
Commit point reached - logical record count 36
C:\Documents and Settings\Administrator>exit
SQL> select * from orders;
no rows selected
This is my log file
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 15:32:00 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: me.ctl
Data File: F:\sat.csv
Bad File: sat.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 256000 bytes
Continuation: none specified
Path used: Conventional
Table ORDERS, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ORD_NUMBER FIRST * WHT CHARACTER
ORD_AMOUNT NEXT * WHT CHARACTER
ADVANCED_AMOUNT NEXT * WHT CHARACTER
ORD_DATE NEXT * WHT CHARACTER
CUST_CODE NEXT * WHT CHARACTER
AGENT_CODE NEXT * WHT CHARACTER
ORD_DESCRIPTION NEXT * WHT CHARACTER
Record 1: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 2: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 3: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 4: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 5: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 6: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 7: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 8: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 9: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 10: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 11: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 12: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 13: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 14: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 15: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 16: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 17: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 18: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 19: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 20: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 21: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 22: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 23: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 24: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 25: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 26: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 27: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 28: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 29: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 30: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 31: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 32: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 33: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 34: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 35: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Record 36: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number
Table ORDERS:
0 Rows successfully loaded.
36 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: 115584 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 36
Total logical records rejected: 36
Total logical records discarded: 0
Run began on Mon Aug 19 15:32:00 2013
Run ended on Mon Aug 19 15:32:01 2013
Elapsed time was: 00:00:00.31
CPU time was: 00:00:00.11
I dont know why i m getting datas not loaded... can anyone point my mistake.
|
|
|
|
Current Time: Tue Apr 23 04:27:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
