3

I have text delimited file and try to insert to oracle database use sql loader. The content of my file like this.

According to mathguy reply, this is the full of my text file content

 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 |
(6 rows)

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

Record 2: Rejected - Error on table "MKG_MED_N", column TRANS_NO.
ORA-01438: value larger than specified precision allowed for this column.

This come from log file as asked by Arkadiusz Łukasiewicz

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

Column TRANS_NO on my table defined as Number(11,0).

As asked by Finbarr O'B, 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.

sihombing_j
  • 41
  • 5
  • It sounds like you have at least one "transnmbr" that's more than 11 digits. Perhaps you can write just a short code fragment to pick out the rows where that happens. Or, you can go to your table and change the column precision, see if that fixes it. –  Jul 13 '16 at 04:09
  • Thanks for the reply mathguy. I can not found "transnmbr" row with more than 11 digits. I add the full of my file content on my question. – sihombing_j Jul 13 '16 at 04:39
  • The compiler doesn't lie. Some value in transnmbr is not correct. Try substr them to max 11 digits and convert it to number before insert. – Marcus Höglund Jul 13 '16 at 06:19
  • Add everything from log file till first rejected line "Record 2: Rejected". And encoding of data file – Arkadiusz Łukasiewicz Jul 13 '16 at 06:52
  • Marcus H: yeah, i know, that's why i feel so confuse. Arkadiusz Łukasiewicz: i edit my post to make it clearer. – sihombing_j Jul 13 '16 at 08:08
  • 1
    Is it necessary to 'substr(trim))' the TRANS_NO field? Have you tried to specify this in the CTL file as 'INTEGER EXTERNAL' instead? i.e. `TRAILING NULLCOLS ( STORE_CODE, TRANS_DATE "to_date(:TRANS_DATE, 'DD/MM/YY HH24:MI')", POS_NO, TRANS_NO INTEGER EXTERNAL,` Also, what rows have been logged into the MkgMed20160712.305.bad file? – Finbarr O'B Jul 13 '16 at 08:38
  • Finbarr O'B: just try my luck :) i've added content of MkgMed20160712.305.bad on my post. – sihombing_j Jul 13 '16 at 08:54

0 Answers0