Oracle sqlldr 固定長形式

Oracle において sqlldr ローダ 固定長形式 に関して示す。

制御ファイル

以下の制御ファイルを作成します。

  LOAD DATA
  INFILE 'sample3.dat'
  TRUNCATE

  INTO TABLE EMP
    (EMPNO     POSITION(1:4)   INTEGER EXTERNAL,
     ENAME     POSITION(6:15)  CHAR,
     DEPTNO    POSITION(17:18) CHAR,
     MGR       POSITION(20:23) INTEGER EXTERNAL)

  INTO TABLE PROJ
  WHEN PROJNO != ''
    (EMPNO     POSITION(1:4)   INTEGER EXTERNAL,
     PROJNO    POSITION(25:27)   INTEGER EXTERNAL)

  INTO TABLE PROJ
  WHEN PROJNO != ''
    (EMPNO     POSITION(1:4)   INTEGER EXTERNAL,
     PROJNO    POSITION(29:31)   INTEGER EXTERNAL)

データファイル(固定長形式)

以下のデータファイルを作成します。

  1234 BAKER      10 9999 101 102
  2664 YOUNG      20 2893 425 abc
  2849 EDDS       xx 4555     294
  4532 PERKINS    10 9999  40
   123 DOOLITTILE 12 9940

ログファイル

以下のログファイルが出力されます。

  SQL*Loader: Release 10.1.0.2.0 - Production on 金 8月 26 17:17:37 2005

  Copyright (c) 1982, 2004, Oracle.  All rights reserved.

  Control File:   sample3.ctl
  Data File:      sample3.dat
    Bad File:     sample3.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 EMP, loaded from every logical record.
  Insert option in effect for this table: TRUNCATE

     Column Name                  Position   Len  Term Encl Datatype
  ------------------------------ ---------- ----- ---- ---- ---------------------
  EMPNO                                 1:4     4           CHARACTER
  ENAME                                6:15    10           CHARACTER
  DEPTNO                              17:18     2           CHARACTER
  MGR                                 20:23     4           CHARACTER

  Table PROJ, loaded when PROJNO != BLANKS
  Insert option in effect for this table: TRUNCATE

     Column Name                  Position   Len  Term Encl Datatype
  ------------------------------ ---------- ----- ---- ---- ---------------------
  EMPNO                                 1:4     4           CHARACTER
  PROJNO                              25:27     3           CHARACTER

  Table PROJ, loaded when PROJNO != BLANKS
  Insert option in effect for this table: TRUNCATE

     Column Name                  Position   Len  Term Encl Datatype
  ------------------------------ ---------- ----- ---- ---- ---------------------
  EMPNO                                 1:4     4           CHARACTER
  PROJNO                              29:31     3           CHARACTER

  Record 3: Rejected - Error on table EMP, column DEPTNO.
  ORA-01722: invalid number

  Record 2: Rejected - Error on table PROJ, column PROJNO.
  ORA-01722: invalid number

  Table EMP:
    3 Rows successfully loaded.
    2 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.

  Table PROJ:
    2 Rows successfully loaded.
    1 Row not loaded due to data errors.
    2 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

  Table PROJ:
    1 Row successfully loaded.
    2 Rows not loaded due to data errors.
    2 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

  Space allocated for bind array:                   3328 bytes(64 rows)
  Read   buffer bytes: 1048576

  Total logical records skipped:          0
  Total logical records read:             5
  Total logical records rejected:         2
  Total logical records discarded:        0

  Run began on 金 8月  26 17:17:37 2005
  Run ended on 金 8月  26 17:17:37 2005

  Elapsed time was:     00:00:00.55
  CPU time was:         00:00:00.02

廃棄ファイル

以下の廃棄ファイルが出力されます。

  2849 EDDS       xx 4555     294
  2664 YOUNG      20 2893 425 abc

ご訪問頂き有難う御座います。 当サイトを効率良く使うためにまずは FrontPage を見て下さい。 検索方法、一覧表示などの各情報を纏めています。
当サイトの説明 → Frontpage