 External table query [message #340393] |
Tue, 12 August 2008 10:35  |
ckb_ORACLE
Messages: 5
Registered: August 2008
|
Junior Member |
|
|
Hi all,
I am new to creation of external tables and as expected am getting loads of error.My code is
CREATE TABLE test_table1
(
empno number(2),
ename char(30)
)
ORGANIzATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext
--abcd is a comment
ACCESS PARAMETERs(records delimited by newline
badfile 'bad_emp'
logfile 'log_emp'
skip 3
FIELDS TERMINATED BY ','(empno number(2),
ename char(30))
)
location ('ext.csv'))
parallel 5
reject limit 100
My errors are
select * from test_table1
*
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: "comma, char, date, defaultif, decima
integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar
zoned"
KUP-01008: the bad identifier was: number
KUP-01007: at line 2 column 34
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
Can anybody pleadse help me on this ?????????
Thankyou.
|
|
|
|
| Re: External table query [message #340401 is a reply to message #340393] |
Tue, 12 August 2008 11:28  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It doesn't recognize "number(2)" in the lower portion, so it thinks it is an "identifier". Use "external integer(2)" instead, as demonstrated below, just as you would with SQL*Loader.
-- c:\oracle11g\ext.csv:
first line to skip
second line to skip
third line to skip
1,name1,
2,name2,
-- Oracle directory object:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY ext AS 'c:\oracle11g'
2 /
Directory created.
-- external table:
SCOTT@orcl_11g> CREATE TABLE test_table1
2 (
3 empno number(2),
4 ename char(30)
5 )
6 ORGANIzATION EXTERNAL
7 (TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY ext
9 --abcd is a comment
10 ACCESS PARAMETERs(records delimited by newline
11 badfile 'bad_emp'
12 logfile 'log_emp'
13 skip 3
14 FIELDS TERMINATED BY ','(empno integer external(2),
15 ename char(30))
16 )
17 location ('ext.csv'))
18 parallel 5
19 reject limit 100
20 /
Table created.
SCOTT@orcl_11g> SELECT * FROM test_table1
2 /
EMPNO ENAME
---------- ------------------------------
1 name1
2 name2
SCOTT@orcl_11g>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
