Re: External Table Question

From: <Cliff>
Date: Fri, 30 Jul 2010 18:49:28 -0400
Message-ID: <2010073018492816807-_at_news.giganews.com>

On 2010-07-24 04:11:25 -0400, Carlos <miotromailcarlos_at_netscape.net> said:

> On Jul 23, 3:07 pm, The Magnet <a..._at_unsu.com> wrote:

>> Is there an error in this definition? I cannot find it with the error
>> message below:
>>
>> CREATE TABLE MESSAGES_EXT
>> (
>>   MESSAGE_ID       VARCHAR2(10),
>>   SEC_ID           VARCHAR2(10),
>>   VARIABLES        VARCHAR2(500),
>>   NAME             VARCHAR2(500),
>>   BODY             CLOB,
>>   CREATED          VARCHAR2(100),
>>   LAST_CHANGED     VARCHAR2(100),
>>   CREATED_BY       VARCHAR2(100),
>>   CHANGED_BY       VARCHAR2(100),
>>   STATUS           VARCHAR2(100)
>> )
>> ORGANIZATION EXTERNAL
>>   (  TYPE ORACLE_LOADER
>>      DEFAULT DIRECTORY EXTERNAL_DIRECTORY
>>      ACCESS PARAMETERS
>>     (RECORDS DELIMITED BY NEWLINE
>>      FIELDS TERMINATED BY '||||' OPTIONALLY ENCLOSED BY '"'
>>      MISSING FIELD VALUES ARE NULL (
>>       "MESSAGE_ID"      VARCHAR2(10),
>>       "SEC_ID"          VARCHAR2(10),
>>       "VARIABLES"       VARCHAR2(500),
>>       "NAME"            VARCHAR2(500),
>>       "BODY"            VARCHAR2(10000),
>>       "CREATED"         VARCHAR2(100),
>>       "LAST_CHANGED"    VARCHAR2(100),
>>       "CREATED_BY"      VARCHAR2(100),
>>       "CHANGED_BY"      VARCHAR2(100),
>>       "STATUS"          VARCHAR2(100))
>>      )
>>      LOCATION (EXTERNAL_DIRECTORY:'messages.csv')
>>   )
>> REJECT LIMIT 1000
>> NOPARALLEL
>> NOMONITORING;
>>
>> select count(*) from MESSAGES_EXT
>> *
>> ERROR at line 1:
>> ORA-29913: error in executing ODCIEXTTABLEOPEN callout
>> ORA-29400: data cartridge error
>> KUP-00554: error encountered while parsing access parameters
>> KUP-01005: syntax error: found "identifier": expecting one of:
>> "binary_double,
>> binary_float, comma, char, date, defaultif, decimal, double, float,
>> integer, (,
>> nullif, oracle_date, oracle_number, position, raw, recnum, ),
>> unsigned,
>> varrawc, varchar, varraw, varcharc, zoned"
>> KUP-01008: the bad identifier was: VARCHAR2
>> KUP-01007: at line 4 column 25
>> ORA-06512: at "SYS.ORACLE_LOADER", line 19

> 
> 
> ""BODY"            VARCHAR2(10000), "
> 
> Ops! Look at the maximum size for Varchar2 in manuals...
> 
> Cheers.
> 
> Carlos.

Just a thought...

What happens if the BODY column spans multiple lines? With records delimited by NEWLINE, wouldn't it truncate if it found any CRs or LFs?

Cliff Received on Fri Jul 30 2010 - 17:49:28 CDT