OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » External Table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

External Table [message #127394]

Mon, 11 July 2005 09:17

xyzaaa
Messages: 2
Registered: July 2005

Junior Member

I'm having trouble reading my external table.

The database is ver 9.2.0.4 on OpenVMS. My file is on a WindowsXP server. I'm running it from my pc to a map drive K: on my pc. K: is a map drive that I can create and delete files on. I've tried this on my c:\ drive as well but I received the same error.

All of the code executes and the fields sizes are correct

Below is my code:

create or replace directory import_dir as 'k:\';

create table external_table
(
term varchar2(6),
id varchar2( 8 ) ,
dept varchar2(4),
code varchar2(4),
amt number(12,2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory import_dir
access parameters
(
records delimited by newline
nobadfile
nologfile
fields terminated by ','
optionally enclosed by '"'
missing field values are null
reject rows with all null fields
)
location ('TEST_DATA.TXT')
)
reject limit unlimited;

Below is a copy of my TEST_DATA.TXT:
200540,00316328,10,00,-147.25
200540,00316328,10,00,-57.00
200540,00316328,STAX,STAX,-13.79
200540,00317810,10,00,-5.59
200540,00317810,STAX,STAX,-.37
200540,00100817,10,00,-70.50
200540,00100817,10,00,-17.50

SQL> select * from external_table;
select * from external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout

I receive this error regardless of whatever I try.

Thank you,

Report message to a moderator

Re: External Table [message #127406 is a reply to message #127394]

Mon, 11 July 2005 09:49

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

works for me.Can't reproduce the error.

Make sure you have created the directory using oracle commands.
The flat file exists in the physical location

  1  create table external_table
  2  (
  3  term varchar2(6),
  4  id varchar2( 8 ) ,
  5  dept varchar2(4),
  6  code varchar2(4),
  7  amt number(12,2)
  8  )
  9  ORGANIZATION EXTERNAL
 10  ( type oracle_loader
 11  default directory external_dir
 12  access parameters
 13  (
 14  records delimited by newline
 15  nobadfile
 16  nologfile
 17  fields terminated by ','
 18  optionally enclosed by '"'
 19  missing field values are null
 20  reject rows with all null fields
 21  )
 22  location ('TEST_DATA.TXT')
 23  )
 24* reject limit unlimited
scott@9i > /

Table created.


TERM   ID       DEPT CODE        AMT
------ -------- ---- ---- ----------
200540 00316328 10   00      -147.25
200540 00316328 10   00          -57
200540 00316328 STAX STAX     -13.79
200540 00317810 10   00        -5.59
200540 00317810 STAX STAX       -.37
200540 00100817 10   00        -70.5
200540 00100817 10   00        -17.5

7 rows selected.

Report message to a moderator

Re: External Table [message #127410 is a reply to message #127394]

Mon, 11 July 2005 10:08

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

And double check that the database server on your openvms can "see" the data file.

Report message to a moderator

Re: External Table [message #127475 is a reply to message #127410]

Mon, 11 July 2005 17:28

xyzaaa
Messages: 2
Registered: July 2005

Junior Member

I doubt the database server can see the data file. I execute SQLPLUS from my pc which has the K:\ mapped.

I thought that by using SQLPLUS on my pc which has the K:\ mapped would allow Oracle, from the APLHA box, see the external_table.

Is this wrong??

Thank You

Report message to a moderator

Re: External Table [message #127569 is a reply to message #127475]

Tue, 12 July 2005 05:10

Maaher
Messages: 7065
Registered: December 2001

Senior Member

yes it is wrong, you must map from the server.

from the docs

A directory object specifies an alias for a directory on the server's file system where external binary file LOBs (BFILEs) and external table data are located.

And why creating yet another duplicate message?

See also here
http://www.orafaq.com/forum/m/127207/43055/#msg_127207

MHE

Report message to a moderator

Previous Topic:	Migration from 8i to 9i:Export of Message Queue Data of 8i version to 9i version
Next Topic:	Unix pipe and SQLLDR

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 08:14:57 CDT 2024