Home » SQL & PL/SQL » SQL & PL/SQL » Error while accessing External tables
Error while accessing External tables [message #188544] Sat, 19 August 2006 04:36 Go to next message
rekha.battula
Messages: 7
Registered: January 2006
Junior Member
I created external table with a sampleff.txt file. but when I am trying to access the table (select * from ext_table_test) I am getting some errors like:

ORA-29913: error in executing ODCIEXTTABLEOPEN call out
ORA-29400: unable to open log file EXT_TABLE_TEST_2183202.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Can you please help me out?

Here is how I created and oracle directory and external table

Created a directory called gas_Ext_dir in the path with the command as shown
below from (DFPROT uer)
CREATE or REPLACE DIRECTORY GAS_EXT_DIR as '/u11/app/dfprot/gas_data';

Then gave grants to read and write to the user dfprotd on the directory
gas_ext_dir
grant read, write on directory gas_ext_dir to dfprotd;

I placed a sample flat file named sampleff.txt in the directory gas_ext_dir
( '/u11/app/dfprot/gas_data/gas_Ext_dir/sampleff.txt' )

Data in sampleff.txt is
1,'one'
2,'two'
3,'three'

Created an external table as below.

create table ext_table_test (
col1 number(2),
col2 varchar2(20)
)
Organization external(
type oracle_loader
default directory gas_ext_dir
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('sampleff.txt')

Table was created with no errors.

All the permissions and grants are correct, but still getting some errors.
Any help would be very usefull.

Regards,
Rekha
Re: Error while accessing External tables [message #188556 is a reply to message #188544] Sat, 19 August 2006 11:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Does the oracle-owner also have sufficient grants at OS-level?
Re: Error while accessing External tables [message #188589 is a reply to message #188556] Sun, 20 August 2006 06:26 Go to previous messageGo to next message
rekha.battula
Messages: 7
Registered: January 2006
Junior Member
Yes the oracle owner has sufficient grants on the create directory.
Re: Error while accessing External tables [message #188596 is a reply to message #188589] Sun, 20 August 2006 08:56 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You created the directory object as:

'/u11/app/dfprot/gas_data'

yet you say you put the sample file in a different directory:

'/u11/app/dfprot/gas_data/gas_Ext_dir/sampleff.txt'
Previous Topic: Pass type of an Array to a procedure
Next Topic: how to use sql query in VB6
Goto Forum:
  


Current Time: Sun Dec 11 02:34:26 CST 2016

Total time taken to generate the page: 0.07524 seconds