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: 64730
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 messageGo to next message
Michel Cadot
Messages: 64730
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
Re: Problem accessing external tables over network [message #658846 is a reply to message #327790] Wed, 28 December 2016 08:09 Go to previous messageGo to next message
rizuane
Messages: 10
Registered: December 2016
Junior Member
Good Day people.


I'm having trouble with getting data into my database using external tables.

Database oracle 9i is on unix server.

The datafile is in windows aplication server:
Path: \\maapps01\Weather Station\SENA_WEATHER_STATION_Daily.dat


How can i create a directory to make work like the scenery above ??

Thanks in Advance
Regards
Re: Problem accessing external tables over network [message #658848 is a reply to message #658846] Wed, 28 December 2016 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64730
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Without impacting the security (that is creating a security hole), no.

[Updated on: Wed, 28 December 2016 08:13]

Report message to a moderator

Re: Problem accessing external tables over network [message #658850 is a reply to message #658848] Wed, 28 December 2016 08:37 Go to previous messageGo to next message
rizuane
Messages: 10
Registered: December 2016
Junior Member
Hi Mr. Cadot,

forgeting security, how can i achieve the solution?

Any help will be gratefully.

Regards
Re: Problem accessing external tables over network [message #658851 is a reply to message #658850] Wed, 28 December 2016 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64730
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Giving the Windows service owner some network privileges.

Re: Problem accessing external tables over network [message #658855 is a reply to message #658851] Wed, 28 December 2016 09:40 Go to previous messageGo to next message
rizuane
Messages: 10
Registered: December 2016
Junior Member
I already create a directory:
CREATE DIRECTORY MAAPPS01 AS '\\maapps01\Weather Station';

Grant permission to select:
GRANT SELECT ON HR.ws_hourly_ext_MAAPPS01 TO public


Also already created external table:
create TABLE HR.ws_hourly_ext_MAAPPS01 
(TIMESTAMP date,
RECORD NUMBER,
AirTC_Avg NUMBER,
RH NUMBER,
WS_ms NUMBER,
WS_ms_Max NUMBER,
WS_ms_TMx date,
BP_mbar_Avg NUMBER,
Rain_mm_Tot NUMBER,
SlrW_Avg NUMBER,
ETrs NUMBER,
Rso NUMBER,
TdC_Avg NUMBER,
TwC_Avg NUMBER,
BattV NUMBER,
SlrMJ_Tot NUMBER)
 ORGANIZATION EXTERNAL
     (TYPE ORACLE_LOADER
   	DEFAULT DIRECTORY MAAPPS01
   	ACCESS PARAMETERS
   	  (RECORDS DELIMITED BY NEWLINE
   	  badfile weather_station:'ws_daily_ext %a_%p.bad'
        logfile weather_station:'ws_daily_ext %a_%p.log'
   	   SKIP 4
   	   fields terminated by ',' 
       optionally enclosed by '"'
   	   MISSING FIELD VALUES ARE NULL
   	   REJECT ROWS WITH ALL NULL 
       FIELDS
("TIMESTAMP" date MASK 'YYYY-MM-DD hh24:mi:ss',
"RECORD",
AirTC_Avg,
RH,
WS_ms,
WS_ms_Max,
WS_ms_TMx date MASK 'YYYY-MM-DD hh24:mi:ss',
BP_mbar_Avg,
Rain_mm_Tot,
SlrW_Avg,
ETrs,
Rso,
TdC_Avg,
TwC_Avg,
BattV,
SlrMJ_Tot)
)
   	 LOCATION ('SENA_WEATHER_STATION_Hourly.dat')
  )
   REJECT LIMIT UNLIMITED;

When try to query:
SELECT * FROM HR.WS_HOURLY_EXT_MAAPPS01;


receiving this error in sql developer:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04001: error opening file \\maapps01\Weather Station\SENA_WEATHER_STATION_Hourly.dat
ORA-06512: at "SYS.ORACLE_LOADER", line 52
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
Re: Problem accessing external tables over network [message #658856 is a reply to message #658855] Wed, 28 December 2016 09:49 Go to previous messageGo to next message
EdStevens
Messages: 754
Registered: September 2013
Senior Member
Just like the original responses in this eight-year-old thread that you revived and hijacked . . . oracle does not work with unc. And no, there is nothing you can do to change that.
Re: Problem accessing external tables over network [message #658860 is a reply to message #658856] Wed, 28 December 2016 11:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2589
Registered: January 2010
Location: Connecticut, USA
Senior Member
EdStevens wrote on Wed, 28 December 2016 10:49
Just like the original responses in this eight-year-old thread that you revived and hijacked . . . oracle does not work with unc. And no, there is nothing you can do to change that.
Really? All you need is to understand how service is started. Database service starts under local system account, so obviously this will not work with UNC:

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 28 11:50:31 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: scott
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE DIRECTORY UNC_TEST AS '\\Downstairs\Movies'
  2  /

Directory created.

SQL> DECLARE
  2      v_file UTL_FILE.FILE_TYPE;
  3  BEGIN
  4      v_file := UTL_FILE.FOPEN('UNC_TEST', 'UNC_TEST.TXT', 'W');
  5      UTL_FILE.PUT_LINE(v_file, 'UNC_TEST');
  6      UTL_FILE.FCLOSE(v_file);
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4


Now I will stop the service, change logon to one of the accounts on my PC and start the service:

SQL> connect scott
Enter password:
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 201 Serial number: 9


Connected.
SQL> DECLARE
  2      v_file UTL_FILE.FILE_TYPE;
  3  BEGIN
  4      v_file := UTL_FILE.FOPEN('UNC_TEST', 'UNC_TEST.TXT', 'W');
  5      UTL_FILE.PUT_LINE(v_file, 'UNC_TEST');
  6      UTL_FILE.FCLOSE(v_file);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      v_file UTL_FILE.FILE_TYPE;
  3      v_line VARCHAR2(20);
  4  BEGIN
  5      v_file := UTL_FILE.FOPEN('UNC_TEST', 'UNC_TEST.TXT', 'R');
  6      UTL_FILE.GET_LINE(v_file,v_line);
  7      DBMS_OUTPUT.PUT_LINE(v_line);
  8      UTL_FILE.FCLOSE(v_file);
  9  END;
 10  /
UNC_TEST

PL/SQL procedure successfully completed.

SQL>


SY.
Re: Problem accessing external tables over network [message #658864 is a reply to message #658856] Wed, 28 December 2016 19:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2589
Registered: January 2010
Location: Connecticut, USA
Senior Member
EdStevens wrote on Wed, 28 December 2016 10:49
Just like the original responses in this eight-year-old thread that you revived and hijacked . . . oracle does not work with unc. And no, there is nothing you can do to change that.

And here is link to ages old MOS document explining how to use UNC path Running utl_file on Windows NT(Doc ID 45172.1).

SY.
Re: Problem accessing external tables over network [message #658874 is a reply to message #658864] Thu, 29 December 2016 05:34 Go to previous messageGo to next message
ThomasG
Messages: 3200
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course, when (as in rizuanes case) the "Database oracle 9i is on unix server.", then the underlying OS hasn't even a clue how to handle UNC path expressions, so Oracle will have no chance to do so either.

The solution there could be to mount the UNC Path over CIFS to a local directory, and then have oracle use that local directory.
Re: Problem accessing external tables over network [message #658908 is a reply to message #327459] Fri, 30 December 2016 00:51 Go to previous message
rizuane
Messages: 10
Registered: December 2016
Junior Member
Thanks All for your response.

ThomasG solution will made work.

regards
Previous Topic: Calculate percentage based on month
Next Topic: selecting the columns dynamically
Goto Forum:
  


Current Time: Sat Apr 29 12:35:34 CDT 2017

Total time taken to generate the page: 0.11375 seconds