| Problem accessing external tables over network [message #327459] |
Mon, 16 June 2008 08:24  |
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 #327787 is a reply to message #327469] |
Tue, 17 June 2008 10:46  |
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 #658855 is a reply to message #658851] |
Wed, 28 December 2016 09:40 |
 |
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 #658860 is a reply to message #658856] |
Wed, 28 December 2016 11:12 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
EdStevens wrote on Wed, 28 December 2016 10:49Just 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 #658874 is a reply to message #658864] |
Thu, 29 December 2016 05:34 |
ThomasG
Messages: 3211
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
