Prev: CPA AFFILIATE NETWORKS AWESOME EARNINGS FROM YOUR HOME
Next: Incompetent Oracle Support
From: Carlos on 24 Jul 2010 04:11
On Jul 23, 3:07 pm, The Magnet <a...(a)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.
From: Cliff on 30 Jul 2010 18:49
On 2010-07-24 04:11:25 -0400, Carlos <miotromailcarlos(a)netscape.net> said:

> On Jul 23, 3:07�pm, The Magnet <a...(a)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

First  |  Prev  | 
Pages: 1 2
Prev: CPA AFFILIATE NETWORKS AWESOME EARNINGS FROM YOUR HOME
Next: Incompetent Oracle Support