Re: fixed length ext table

From: Gus Spier <gus.spier_at_gmail.com>
Date: Mon, 6 Dec 2010 18:09:00 -0500
Message-ID: <AANLkTi=C2XJ+bQAd7cdLpGz=9Hr6cmmxA3uPKJYiWL=-_at_mail.gmail.com>

Is there something very subtle about PROC_STAT_CODE that it occupies three positions in the record (24, 25, and 26) and yet has only two characters?

v/r,

Gus

On Mon, Dec 6, 2010 at 5:57 PM, Eugene Pipko <epipko_at_unionbay.com> wrote:

> 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 - 17:09:00 CST