Re: External Table Question
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Sat, 24 Jul 2010 01:11:25 -0700 (PDT)
Message-ID: <a449e0ca-90c2-41c2-9aa4-63255432eb5f_at_w30g2000yqw.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
Date: Sat, 24 Jul 2010 01:11:25 -0700 (PDT)
Message-ID: <a449e0ca-90c2-41c2-9aa4-63255432eb5f_at_w30g2000yqw.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:11:25 CDT