Home » SQL & PL/SQL » SQL & PL/SQL » Oracle external tables (Oracle 10g)
Oracle external tables [message #291173] Thu, 03 January 2008 04:50 Go to next message
andreigann
Messages: 2
Registered: January 2008
Location: Romania
Junior Member

Hello. I'm new in oracle and I have a problem at my work. I need to insert data from text files that are located on a shared folder from another computer. I've tried with external tables:
-- connect as sysadmin
create or replace directory xtern_data_dir as '\\myIP\dir\';
grant read,write on directory xtern_data_dir to and;

-- connect as and
DROP TABLE ext_tab;
CREATE TABLE ext_tab (
IDFURNIZOR NUMBER,
DENFURNIZOR VARCHAR2(40),
NRTELFURNIZOR VARCHAR2(14),
EMAILFURNIZOR VARCHAR2(30))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY xtern_data_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('test.txt')
)
PARALLEL
REJECT LIMIT 0;

If I execute
select * from ext_tab;
sql throws the next exception
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXT_TAB_1780_1392.log
OS error The data is invalid.
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Any ideea why?
If you could give me an alternative to external tables if this doesn't work.
The user where the oracle service is running has adminstrative rights and the shared folder have Full Access. Thanks.

(Sorry about my english)
Re: Oracle external tables [message #291182 is a reply to message #291173] Thu, 03 January 2008 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does not support UNC for most of its features.

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Oracle external tables [message #291184 is a reply to message #291173] Thu, 03 January 2008 05:24 Go to previous messageGo to next message
andreigann
Messages: 2
Registered: January 2008
Location: Romania
Junior Member

So the only solution is to copy the text files to the oracle computer?
Re: Oracle external tables [message #291189 is a reply to message #291184] Thu, 03 January 2008 05:37 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Previous Topic: is it possible to use collect function in 9i
Next Topic: Recompiling base object type and ORA-02303
Goto Forum:
  


Current Time: Sat Dec 03 01:13:57 CST 2016

Total time taken to generate the page: 0.05049 seconds