Home » SQL & PL/SQL » SQL & PL/SQL » External table query
icon4.gif  External table query [message #340393] Tue, 12 August 2008 10:35 Go to next message
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 Go to previous message
Barbara Boehmer
Messages: 8635
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> 

Previous Topic: Deferred Name Resolution
Next Topic: Accessing a table from a different database
Goto Forum:
  


Current Time: Thu Dec 08 10:48:50 CST 2016

Total time taken to generate the page: 0.05508 seconds