Home » SQL & PL/SQL » SQL & PL/SQL » Problem accessing external tables over network (Oracle 10g Windows 2003 server Windows XP)
Problem accessing external tables over network [message #327459] Mon, 16 June 2008 08:24 Go to next message
Iain
Messages: 4
Registered: October 2000
Junior Member
Hi,

I'm having trouble with getting data into my database using external tables. I'm working on Oracle 10g running on a Windows 2003 server and the error messages I get are as follows:

ORA-12801: error signalled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04001: error opening file \\xxx.xxx.xxx.xxx\directory\filename.ext (ip address blanked)
ORA-06512: at "sys.oracle_loader", line 52

The code I have used to create the directory and table is as follows:

CREATE OR REPLACE DIRECTORY testupload AS '\\xxx.xxx.xxx.xxx\UPLOAD';

CREATE TABLE testupload
(
FIELD1 VARCHAR2(100BYTE)
FILED2 VARCHAR2(100BYTE)
FILED3 VARCHAR2(100BYTE)
FILED4 VARCHAR2(100BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY testupload
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
FILED TERMINATED BY '@' (FIELD1 CHAR(100), FIELD1 CHAR(100), FIELD1 CHAR(100), FIELD1 CHAR(100))
)
LOCATION (testupload: 'filename.ext')
)
REJECT LIMIT 0
PARALLEL(DEGREE DEFAULT INSTANCES DEFAULT)
NOMONITORING;

When I run the code it successfully creates the directory and the file (filename.ext) is present with correct data in it. The errors are produced when the external table is being accessed to populate the table (testupload) which has been created with the correct name and fields. The table (testupload) is unreadable however I attempt to access it.

The external table (filename.ext) can be accessed using UTL_FILE package and overwritten thus proving that Oracle has access to it. When a local directory is used instead of the networked directory ( i.e. CREATE OR REPLACE DIRECTORY testupload AS 'E:\UPLOAD'; ) the table (testupload) is written successfully and can be accessed to check its contents.

Any help gratefully received as googling on this has left me baffled!

Re: Problem accessing external tables over network [message #327467 is a reply to message #327459] Mon, 16 June 2008 08:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
CREATE OR REPLACE DIRECTORY testupload AS '\\xxx.xxx.xxx.xxx\UPLOAD';

UNC is not supported.
Try mounting the remote share locally (like F:\upload).
The directory should be visible to oracle as a local directory
Re: Problem accessing external tables over network [message #327469 is a reply to message #327459] Mon, 16 June 2008 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle never certified it works with UNC path.
In many cases it doesn't.

Regards
Michel

[Updated on: Mon, 16 June 2008 08:50]

Report message to a moderator

Re: Problem accessing external tables over network [message #327787 is a reply to message #327469] Tue, 17 June 2008 10:46 Go to previous messageGo to next message
Iain
Messages: 4
Registered: October 2000
Junior Member
Hi,

thanks for the quick responses. The only thing is that we had this working using UNC paths then moved the system onto a different network for a demo and when it was put back it no longer worked. Is it likely that it would have broken by moving twice? Is there any way to trace how to make it work again and are there any suggestions as to how to force Oracle to work with UNC addresses? Otherwise the shared drive option is a good one - many thanks.

Regards,

Iain.
Re: Problem accessing external tables over network [message #327790 is a reply to message #327787] Tue, 17 June 2008 11:39 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any way to trace how to make it work again

No.

Quote:
are there any suggestions as to how to force Oracle to work with UNC addresses?

No.

You were lucky it worked.

Regards
Michel
Previous Topic: Compare the value with previous row
Next Topic: Analytical query to get sum of parts
Goto Forum:
  


Current Time: Thu Dec 08 14:36:14 CST 2016

Total time taken to generate the page: 0.19314 seconds