Home » RDBMS Server » Server Utilities » Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb
Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb [message #465489] Wed, 14 July 2010 06:10 Go to next message
soagra
Messages: 1
Registered: July 2010
Location: bangalore
Junior Member
I"M using this syntax for loading my data to external table

create table EXT_SCFILTER_RECOGNITION_DUMP (
serialno varchar2(20),
userid varchar2(20),
txnid varchar2(100),
songid varchar2(45),
vcode varchar2(45),
title varchar2(100),
album varchar2(100),
messagesent CLOB,
datetime timestamp,
circle varchar(45),
filename varchar2(100),
filelength number,
server varchar2(20),
responsetime float(126),
language varchar2(20),
speed float(126),
signalquality float(126),
blankcount number(38),
isfilevalid varchar2(20))
organization external(
type oracle_loader
default directory test_xtern_data_dir
access parameters
( records delimited by newline
fields terminated by X'9'
missing field values are null (
serialno char(20) nullif serialno=BLANKS,
userid char(20) nullif userid=BLANKS,
txnid char(100) nullif txnid=BLANKS,
songid char(45) nullif songid=BLANKS,
vcode char(45) nullif vcode=BLANKS,
title char(100) nullif title=BLANKS,
album char(100) nullif album=BLANKS,
messagesent char(4000) nullif messagesent=BLANKS,
datetime char(20) DATE_FORMAT DATE MASK "YYYY-MM-DD HH24:MI:SS" nullif datetime=BLANKS,
circle char(45) nullif circle=BLANKS,
filename char(100) nullif filename=BLANKS,
server char(20) nullif server=BLANKS,
responsetime char(126) nullif responsetime=BLANKS,
language char(20) nullif responsetime=BLANKS,
speed char(20) nullif speed=BLANKS,
signalquality char(20) nullif signalquality=BLANKS,
blankcount char(38) nullif blankcount=BLANKS,
isfilevalid char(20) nullif isfilevalid=BLANKS
))
location ('SCfilter_Recognition_dump.nonull.csv') )
REJECT LIMIT UNLIMITED;


with data

i'm getting this error

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 "language": expecting one of:
"double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 17 column 4

Please tell me where the problem is in my query

Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 [message #465499 is a reply to message #465489] Wed, 14 July 2010 06:54 Go to previous messageGo to next message
Littlefoot
Messages: 19633
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which "query"? CREATE TABLE ... well, creates a table (no errors on my 10g (10.2.0.1.0). Is some other "query" involved? If so, which one? Sample data might help too.
Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 [message #465538 is a reply to message #465499] Wed, 14 July 2010 10:40 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since this is an external table it kind of is a query, it's querying the file.
I can't see language in the statement anywhere so presumably it's in the file.
Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 [message #465547 is a reply to message #465538] Wed, 14 July 2010 12:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7988
Registered: November 2002
Location: California, USA
Senior Member
There is a column named "language" in the table. This has special meaning when creating external tables:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/et_params.htm#SUTIL1388

If you rename the "language" column to something else, like "language_col", then it resolves the error. Please see the simplified reproduction and solution below, using only two columns.

-- reproduction:
-- contents of c:\my_oracle_files\SCfilter_Recognition_dump.nonull.csv:
1	lang1
2	lang2

SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY test_xtern_data_dir
  2  AS 'c:\my_oracle_files'
  3  /

Directory created.

SCOTT@orcl_11gR2> create table EXT_SCFILTER_RECOGNITION_DUMP (
  2  responsetime float(126),
  3  language varchar2(20)
  4  )
  5  organization external(
  6  type oracle_loader
  7  default directory test_xtern_data_dir
  8  access parameters
  9  ( records delimited by newline
 10  fields terminated by x'9'
 11  missing field values are null (
 12  responsetime char(126) nullif responsetime=BLANKS,
 13  language char(20) nullif responsetime=BLANKS
 14  ))
 15  location ('SCfilter_Recognition_dump.nonull.csv') )
 16  REJECT LIMIT UNLIMITED
 17  /

Table created.

SCOTT@orcl_11gR2> select * from ext_scfilter_recognition_dump
  2  /
select * from ext_scfilter_recognition_dump
*
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 "language": expecting one of:
"double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 5 column 1


-- solution:
SCOTT@orcl_11gR2> DROP TABLE EXT_SCFILTER_RECOGNITION_DUMP
  2  /

Table dropped.

SCOTT@orcl_11gR2> create table EXT_SCFILTER_RECOGNITION_DUMP (
  2  responsetime float(126),
  3  language_col varchar2(20)
  4  )
  5  organization external(
  6  type oracle_loader
  7  default directory test_xtern_data_dir
  8  access parameters
  9  ( records delimited by newline
 10  fields terminated by x'9'
 11  missing field values are null (
 12  responsetime char(126) nullif responsetime=BLANKS,
 13  language_col char(20) nullif responsetime=BLANKS
 14  ))
 15  location ('SCfilter_Recognition_dump.nonull.csv') )
 16  REJECT LIMIT UNLIMITED
 17  /

Table created.

SCOTT@orcl_11gR2> select * from ext_scfilter_recognition_dump
  2  /

RESPONSETIME LANGUAGE_COL
------------ --------------------
           1 lang1
           2 lang2

SCOTT@orcl_11gR2>

[Updated on: Wed, 14 July 2010 12:34]

Report message to a moderator

Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 [message #465550 is a reply to message #465547] Wed, 14 July 2010 12:21 Go to previous message
Littlefoot
Messages: 19633
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ah! What would we do without Barbara?
Previous Topic: Need to export the schema which contains of more than 100 GB using compressed mode
Next Topic: FTP Dump file Over Network
Goto Forum:
  


Current Time: Mon Sep 22 12:11:13 CDT 2014

Total time taken to generate the page: 0.13007 seconds