Home » RDBMS Server » Server Utilities » Sql Loader (Oracle, Version : 10 : OS : Unix)
Sql Loader [message #653666] Wed, 13 July 2016 22:01 Go to next message
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 
Column TRANS_NO on my table defined as Number(11,0).

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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: Sql Loader [message #653670 is a reply to message #653667] Thu, 14 July 2016 01:44 Go to previous message
GoGreen
Messages: 28
Registered: February 2012
Location: Java Island
Junior Member

Thanks Littlefoot.

My problem has been solved, when drop table MKG_MED_N and then recreate the table MKG_MED_N with the exact same structure . just not yet known why unsuccessful in the previous table. Smile
Previous Topic: How to increase SQL Loader performance
Next Topic: Field in data file exceeds maximum length - SQLLDR
Goto Forum:
  


Current Time: Fri Mar 29 04:05:48 CDT 2024