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  |
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 #465547 is a reply to message #465538] |
Wed, 14 July 2010 12:09  |
 |
Barbara Boehmer
Messages: 9101
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 |
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 14:34:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
