Home » SQL & PL/SQL » SQL & PL/SQL » regarding external tables
regarding external tables [message #346673] Tue, 09 September 2008 05:52 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi all,

I am trying to read data from a .txt file into externel table.
And i am getting the error when i query the external table.

below is wat i hav done,I have created the Directory ravi_dir also.

file.txt contains

1,one,first
2,two,second
3,three,third
4,four,fourth


SQL> create table ravi_test5(
2 i number,
3 n varchar2(10),
4 m varchar2(10))
5 organization external (
6 type oracle_loader
7 default directory ravi_dir
8 access parameters (
9 records delimited by newline
10 fields delimited by ','
11 missing field values are null
12 )
13 location('file.txt')
14 )
15 reject limit unlimited;

Table created.

SQL> select * from ravi_test5;
select * from ravi_test5
*
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 "delimited": expecting one of: "column,
enclosed, exit, (, ltrim, lrtrim, ldrtrim, missing, notrim, optionally, rtrim,
reject, terminated"
KUP-01007: at line 2 column 8
ORA-06512: at "SYS.ORACLE_LOADER", line 19
Re: regarding external tables [message #346686 is a reply to message #346673] Tue, 09 September 2008 06:26 Go to previous messageGo to next message
milind.khadloya
Messages: 4
Registered: September 2008
Location: India
Junior Member

Can you please make sure that your file.txt exist in same directory as specified in ravi_dir directory.

Just a suggestion while working on external file it is better to use varchar2 data type.
Re: regarding external tables [message #346688 is a reply to message #346673] Tue, 09 September 2008 06:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, make sure this file is located on a SERVER (not your client computer, in a case you connect to the server remotely).

Moreover, see whether line 10 change from
fields delimited by ','
to
fields TERMINATED by ','
does any good.
Re: regarding external tables [message #346689 is a reply to message #346673] Tue, 09 September 2008 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel
Re: regarding external tables [message #346690 is a reply to message #346686] Tue, 09 September 2008 06:42 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Yes milind the File the file is in same path,and the problem still persist
Re: regarding external tables [message #346691 is a reply to message #346688] Tue, 09 September 2008 06:45 Go to previous message
panyam
Messages: 146
Registered: May 2008
Senior Member
Thanks a lot Littlefoot wen i replace "delimited by" with
"terminated by" it's working. Smile Smile
Previous Topic: Updating columns of one table based on other table
Next Topic: Question on Exchange partition
Goto Forum:
  


Current Time: Sat Dec 10 16:45:09 CST 2016

Total time taken to generate the page: 0.08926 seconds