Re: External Table Question

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Sat, 24 Jul 2010 01:08:29 -0700 (PDT)
Message-ID: <e3749361-5298-4ff0-b2e9-c6b12a8f50f9_at_w31g2000yqb.googlegroups.com>

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. Received on Sat Jul 24 2010 - 03:08:29 CDT