Home » RDBMS Server » Server Utilities » Sql Loader (Oracle, Version : 10 : OS : Unix)
| Sql Loader [message #653666] |
Wed, 13 July 2016 22:01  |
 |
GoGreen
Messages: 28
Registered: February 2012
Location: Java Island
|
Junior Member |
|
|
I have text delimited file and try to insert to oracle database use sql loader. The content of my file like this.
store | datetime | termnmbr | transnmbr | mdesc | mediaamnt | accountnmbr
305 | 12/07/16 10:07 | 1 |6070000449 | CASH | 50000 |
305 | 12/07/16 10:07 | 1 |6070000448 | CASH | 100000 |
305 | 12/07/16 10:07 | 1 |6070000447 | CASH | 50000 |
305 | 12/07/16 10:07 | 1 |6070000445 | CASH | 209000 |
305 | 12/07/16 12:07 | 1 |6070000430 | CASH | 100000 |
305 | 12/07/16 12:07 | 1 |6070000429 | CASH | 50000 |
My control file
OPTIONS (SILENT=(FEEDBACK),SKIP=1)
LOAD DATA
INFILE *
APPEND
INTO TABLE "MKG_MED_N"
WHEN (1:1) <> '('
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
STORE_CODE,
TRANS_DATE "to_date(:TRANS_DATE, 'DD/MM/YY HH24:MI')",
POS_NO,
TRANS_NO "substr(trim(:TRANS_NO),1,10)",
MED_DESC "trim(:MED_DESC)",
MED_AMOUNT,
MED_ACC_NO "trim(TRANSLATE (:MED_ACC_NO, 'x'||CHR(10)||CHR(13), 'x'))",
)
The problem is, i've got error.
This come from log file
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jul 13 14:35:43 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: /u07/mkg/script/MkgMed20160712.305.ctl
Data File: /u07/mkg/1607/MkgMed20160712.305
Bad File: /u07/mkg/bad/MkgMed20160712.305.bad
Discard File: /u07/mkg/discard/MkgMed20160712.305.dis
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 999
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK
Table "MKG_MED_N", loaded when 1:1 != 0X28(character '(')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
STORE_CODE FIRST * | CHARACTER
TRANS_DATE NEXT * | CHARACTER
SQL string for column : "to_date(:TRANS_DATE, 'DD/MM/YY HH24:MI')"
POS_NO NEXT * | CHARACTER
TRANS_NO NEXT * | CHARACTER
SQL string for column : "substr(trim(:TRANS_NO),1,10)"
MED_DESC NEXT * | CHARACTER
SQL string for column : "trim(:MED_DESC)"
MED_AMOUNT NEXT * | CHARACTER
MED_ACC_NO NEXT * | CHARACTER
SQL string for column : "trim(TRANSLATE (:MED_ACC_NO, 'x'||CHR(10)||CHR(13), 'x'))"
Record 1: Rejected - Error on table "MKG_MED_N", column TRANS_NO.
ORA-01438: value larger than specified precision allowed for this column
Record 2: Rejected - Error on table "MKG_MED_N", column TRANS_NO.
ORA-01438: value larger than specified precision allowed for this column
this from bad file:
305 | 12/07/16 10:07 | 1 |6070000449 | CASH | 50000 |
305 | 12/07/16 10:07 | 1 |6070000448 | CASH | 100000 |
305 | 12/07/16 10:07 | 1 |6070000447 | CASH | 50000 |
305 | 12/07/16 10:07 | 1 |6070000445 | CASH | 209000 |
305 | 12/07/16 12:07 | 1 |6070000430 | CASH | 100000 |
305 | 12/07/16 12:07 | 1 |6070000429 | CASH | 50000 |
Anyone can help me? Thanks before.
|
|
|
|
| Re: Sql Loader [message #653667 is a reply to message #653666] |
Thu, 14 July 2016 00:11  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You forgot to specify MKG_MED_N description. Without it, we can only guess (instead of try your code).
So, I guessed and everything went well.
Control file:options (skip=1)
load data
infile *
replace
into table test
fields terminated by '|' trailing nullcols
(
STORE_CODE,
TRANS_DATE "to_date(:TRANS_DATE, 'DD/MM/YY HH24:MI')",
POS_NO,
TRANS_NO "substr(trim(:TRANS_NO),1,10)",
MED_DESC "trim(:MED_DESC)",
MED_AMOUNT,
MED_ACC_NO "trim(TRANSLATE (:MED_ACC_NO, 'x'||CHR(10)||CHR(13), 'x'))"
)
begindata
store | datetime | termnmbr | transnmbr | mdesc | mediaamnt | accountnmbr
305 | 12/07/16 10:07 | 1 |6070000449 | CASH | 50000 |
305 | 12/07/16 10:07 | 1 |6070000448 | CASH | 100000 |
305 | 12/07/16 10:07 | 1 |6070000447 | CASH | 50000 |
305 | 12/07/16 10:07 | 1 |6070000445 | CASH | 209000 |
305 | 12/07/16 12:07 | 1 |6070000430 | CASH | 100000 |
305 | 12/07/16 12:07 | 1 |6070000429 | CASH | 50000 |
SQL> desc test
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
STORE_CODE NUMBER
TRANS_DATE DATE
POS_NO NUMBER
TRANS_NO NUMBER
MED_DESC VARCHAR2(20)
MED_AMOUNT NUMBER
MED_ACC_NO VARCHAR2(20)
SQL> $sqlldr scott/tiger@ora11 control=test17.ctl log=test17.log
SQL*Loader: Release 11.2.0.2.0 - Production on ╚et Srp 14 07:08:52 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 6
SQL> select * from test;
STORE_CODE TRANS_DATE POS_NO TRANS_NO MED_DESC MED_AMOUNT MED_ACC_NO
---------- ------------------- ---------- ---------- -------------------- ---------- ----------
305 12.07.2016 10:07:00 1 6070000449 CASH 50000
305 12.07.2016 10:07:00 1 6070000448 CASH 100000
305 12.07.2016 10:07:00 1 6070000447 CASH 50000
305 12.07.2016 10:07:00 1 6070000445 CASH 209000
305 12.07.2016 12:07:00 1 6070000430 CASH 100000
305 12.07.2016 12:07:00 1 6070000429 CASH 50000
6 rows selected.
SQL>
There's an error in your control file, here (comma at the end of the line):
MED_ACC_NO "trim(TRANSLATE (:MED_ACC_NO, 'x'||CHR(10)||CHR(13), 'x'))",
Apart from that, I'm not sure what causes your problems.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 20:16:38 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
