External table definition question 1

[SJSFoxPro]

Hi,

I have a tab delimited text file with just two columns. I'm looking for help with the definition because it seems to be limited me to CHAR(255) and rejects longer records. I can't find any documentation that states a limitation. Here's my current table definition:
CREATE TABLE TMP_MY_XTBL
(
V_NUMBER VARCHAR2(13 BYTE),
TEXT VARCHAR2(2000 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY CCL_INPUT_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE CCL_LOG_DIR:'TMP_MY_XTBL_%p.bad'
DISCARDFILE CCL_LOG_DIR:'TMP_MY_XTBL_%p.dsc'
LOGFILE CCL_LOG_DIR:'TMP_MY_XTBL_%p.log'
FIELDS TERMINATED BY x0'09'
MISSING FIELD VALUES ARE NULL
(
v_number,
text
)
)
LOCATION (CCL_INPUT_DIR:'CCL_2008.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

The error message in my log file:
Field Definitions for table TMP_MY_XTBL
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:

V_NUMBER CHAR (255)
Terminated by "09"
Trim whitespace same as SQL Loader
TEXT CHAR (255)
Terminated by "09"
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field TEXT
KUP-04026: field too long for datatype
KUP-04101: record 1 rejected in file /vg05lvol2/batch/ccl/hltd/input/CCL_2008.txt

Any help or links to external table definition or limitations would be greatly appreciated!

Thanks!

 

[LKBrwnDBA]

Try:

-- etc --
         FIELDS TERMINATED BY x0'09'
            MISSING FIELD VALUES ARE NULL
            (
                v_number,
                text CHAR(2000)
            )
-- etc --

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SJSFoxPro]

1000 purple stars for you if I could!

Thanks, that did it. I haven't seen it anywhere! I greatly appreciate your help!