Home » SQL & PL/SQL » SQL & PL/SQL » Creating External table Using remote system folder (Windows XP/2000)
Creating External table Using remote system folder [message #387579] Thu, 19 February 2009 23:22 Go to next message
suman.ch
Messages: 1
Registered: February 2009
Location: Bangalore India
Junior Member
Hi,

I am new to oracle, I would like to create an external table using a remote system folder.
I have done the following steps to achive above one.

1) I have a machine1 where Oracle is installed and in another machine say machine2 to my application is running there I shared a directory (IMPORT_DIR).

2) I login into Oracle as sys dba, and I executed the following command.

CREATE OR REPLACE DIRECTORY EXT_DIR AS '\\IPADDRESS_OF_REMOTE_MACHINE:\IMPORT_DIR';

GRANT READ, WRITE ON DIRECTORY EXT_DIR TO SCOTT;

3) Now I login into oracle as a scoott user created the external table as below.

CREATE TABLE EMP_TRY_1
(
PROD_ID VARCHAR2(50 BYTE),
CUST_NAME VARCHAR2(50 BYTE),
LEAF_VALUE VARCHAR2(50 BYTE),
SAL SAL
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'error_log.log'
BADFILE 'bad_record.bad'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION (EXT_DIR:'emp.csv')
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;


4) select * from EMP_TRY_1
it is giving following error.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file error_log.log
OS error The data is invalid.
ORA-06512: at "SYS.ORACLE_LOADER", line 19



5) I have the proper data in the csv file, when I pointing to local directory where oracle is installed it is loading

6) I tried using mapping drive in windows, but no luck


Could you please anyone suggest me the solution for this problem as early as posible??.



Re: Creating External table Using remote system folder [message #387582 is a reply to message #387579] Thu, 19 February 2009 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Do not cross/multi-post
Re: Creating External table Using remote system folder [message #387607 is a reply to message #387579] Fri, 20 February 2009 00:45 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
KUP-04063: unable to open log file error_log.log

UNC and Windows network drive are mostly not supported in Oracle.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Use SQL*Plus and copy and paste your session instead of describing it.

Regards
Michel

[Updated on: Fri, 20 February 2009 00:45]

Report message to a moderator

Previous Topic: using nvl in decode function
Next Topic: Converting columns into rows
Goto Forum:
  


Current Time: Sun Dec 04 00:34:33 CST 2016

Total time taken to generate the page: 0.10828 seconds