fixed length ext table

From: Eugene Pipko <epipko_at_unionbay.com>
Date: Mon, 6 Dec 2010 14:57:44 -0800
Message-ID: <34DB87F47199374280ADFD2968CDBCFA8865B84BAC_at_MAIL01KT.seattlepacificindustries.com>

Hi all,
Oracle 9.2.0.8 on Windows 2K3.
I can't figure out what I am missing in the following. Could you please help.

CREATE TABLE wm_trans
(

  TRAN_TYPE               VARCHAR2(3 BYTE),
  TRAN_CODE               VARCHAR2(3 BYTE),
  TRAN_NBR                NUMBER(9),
  PIX_SEQ_NBR             NUMBER(9),
  PROC_STAT_CODE          NUMBER(2),
  WHSE                    VARCHAR2(3 BYTE),
  CO                      VARCHAR2(10 BYTE),
  DIV                     VARCHAR2(10 BYTE),
  CASE_NBR                VARCHAR2(20 BYTE),
  SKU_ID                  VARCHAR2(10 BYTE)

)
ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER

     DEFAULT DIRECTORY WM_OUT
     ACCESS PARAMETERS
       ( records delimited by newline
         fields lrtrim
         MISSING FIELD VALUES ARE NULL
                (
                                                  TRAN_TYPE               position (001:003) char(3),
                                                  TRAN_CODE               position (004:006) char(3),
                                                  TRAN_NBR                position (007:015) char(9),
                                                  PIX_SEQ_NBR             position (016:024) char(9),
                                                  PROC_STAT_CODE          position (024:026) char(2),
                                                  WHSE                    position (027:029) char(3),
                                                  CO                      position (030:040) char(10),
                                                  DIV                     position (041:050) char(10),
                                                  CASE_NBR                position (051:076) char(20),
                                                  SKU_ID                  position (077:086) char(10)
                                )
         )
     LOCATION (WM_OUT:'P0022')

  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING; When I select from this table I get :

LOG file opened at 12/06/10 14:46:19
Field Definitions for table wm_trans
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform   Rows with all null fields are accepted

  Fields in Data Source:

    TRAN_TYPE                       CHAR (3)
      Record position (1, 3)
      Trim whitespace from left and right
    TRAN_CODE                       CHAR (3)
      Record position (4, 6)
      Trim whitespace from left and right
    TRAN_NBR                        CHAR (9)
      Record position (7, 15)
      Trim whitespace from left and right
    PIX_SEQ_NBR                     CHAR (9)
      Record position (16, 24)
      Trim whitespace from left and right
    PROC_STAT_CODE                  CHAR (2)
      Record position (24, 26)
      Trim whitespace from left and right
    WHSE                            CHAR (3)
      Record position (27, 29)
      Trim whitespace from left and right
    CO                              CHAR (10)
      Record position (30, 40)
      Trim whitespace from left and right
    DIV                             CHAR (10)
      Record position (41, 50)
      Trim whitespace from left and right
    CASE_NBR                        CHAR (20)
      Record position (51, 76)
      Trim whitespace from left and right
    SKU_ID                          CHAR (10)
      Record position (77, 86)
      Trim whitespace from left and right

Eugene

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 06 2010 - 16:57:44 CST