Prev: CPA AFFILIATE NETWORKS AWESOME EARNINGS FROM YOUR HOME
Next: Incompetent Oracle Support
From: The Magnet on 23 Jul 2010 09:07
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


From: Mladen Gogala on 23 Jul 2010 09:12
On Fri, 23 Jul 2010 06:07:51 -0700, The Magnet wrote:

> KUP-01005: syntax error: found "identifier": expecting one of:
> "binary_double,
> binary_float, comma, char, date, defaultif, decimal, double, float,

Try some more. I will not do your job.



--
http://mgogala.byethost5.com
From: The Magnet on 23 Jul 2010 09:36
On Jul 23, 8:12 am, Mladen Gogala <gogala.mla...(a)gmail.com> wrote:
> On Fri, 23 Jul 2010 06:07:51 -0700, The Magnet wrote:
> > KUP-01005: syntax error: found "identifier": expecting one of:
> > "binary_double,
> > binary_float, comma, char, date, defaultif, decimal, double, float,
>
> Try some more. I will not do your job.
>
> --http://mgogala.byethost5.com


Decided to try SQLLDR and now receive this:

SQL*Loader-510: Physical record in data file (messages.csv) is longer
than the maximum(1048576)
SQL*Loader-2026: the load was aborted because SQL Loader cannot
continue.

All I am trying to do is load a CLOB column.
From: Ed Prochak on 23 Jul 2010 13:50
On Jul 23, 9:07 am, 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

This may or may not help:
your field terminator is FOUR pipe | characters?
what exactly are they enclosed by?
Does it work without the CLOB?

Ed
From: Carlos on 24 Jul 2010 04:08
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.
 |  Next  |  Last
Pages: 1 2
Prev: CPA AFFILIATE NETWORKS AWESOME EARNINGS FROM YOUR HOME
Next: Incompetent Oracle Support