fixed length ext table
- From: Eugene Pipko <epipko@xxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Dec 2010 14:57:44 -0800
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
Other related posts:
