Home » RDBMS Server » Server Utilities » How to Access local file residing at client from DB Server (Oracle 9i/10g/11g)
How to Access local file residing at client from DB Server [message #532545] Wed, 23 November 2011 05:41 Go to next message
skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi
Junior Member
Hi,

I have a requirement to read flat text file(around 15000 lines) residing at a client location from DB server and write into a table in One cell.

I tried UTL_FILE and DBMS_LOB but, i am not able to access client location to read the file as it reads path from Oracle Directory.

eg.
my client path is 198.168.1.1 and my DB server is in unix say 192.168.1.10.
file location is: \\192.168.1.1\share\abc.txt
So I created One Oracle directory as MY_DIR having DIRECTORY_PATH as '\\192.168.1.1\share\'.
But both UTL_FILE and DBMS_LOB is not able to access the file.

Error Message:
-------------
Unable to process CLOB -22288 ~ ORA-22288: file or LOB operation FILEOPEN
failed
No such file or directory

Few Details for reference:
-------------------------
File Location: \\192.168.1.1\share\abc.txt
Unix DB Server location: 192.168.1.10
Table : Test (filename varchar2(30), Content CLOB)
Oracle Dir: MYDIR
Directory_Path: \\192.168.1.1\share\

Please provide some solution.

Regards,
Sujit
Re: How to Access local file residing at client from DB Server [message #532546 is a reply to message #532545] Wed, 23 November 2011 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot do it in PL/SQL.
Oracle server has only access to local files.

Regards
Michel
Re: How to Access local file residing at client from DB Server [message #532548 is a reply to message #532546] Wed, 23 November 2011 07:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
Please provide some solution.


FTP/SCPthe file to unix server and you will be able to do what you need.
Re: How to Access local file residing at client from DB Server [message #532673 is a reply to message #532548] Wed, 23 November 2011 22:49 Go to previous messageGo to next message
skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi
Junior Member
Well....I can't use FTP as FTP or sftp or any third party file transfer utility as ftp is blocked.
Re: How to Access local file residing at client from DB Server [message #532674 is a reply to message #532548] Wed, 23 November 2011 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have a requirement to read flat text file(around 15000 lines) residing at a client location from DB server and write into a table
post CREATE TABLE statement for table mentioned above

consider using sql*Loader (sqlldr) to load text file into Oracle DB;
if Oracle client software has been loaded onto system at client location
Re: How to Access local file residing at client from DB Server [message #532692 is a reply to message #532674] Thu, 24 November 2011 01:18 Go to previous messageGo to next message
skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi
Junior Member
if i try with sqlldr..how do i write the script so that say 15000 or more lines can be stored in one cell of a table.
Re: How to Access local file residing at client from DB Server [message #532699 is a reply to message #532692] Thu, 24 November 2011 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Utilities
Part II SQL*Loader

Regards
Michel
Re: How to Access local file residing at client from DB Server [message #532811 is a reply to message #532699] Thu, 24 November 2011 21:41 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>if i try with sqlldr..how do i write the script so that say 15000 or more lines can be stored in one cell of a table.
I did not know that tables had cells.
To what exactly do you refer?
Previous Topic: Oracle 9i
Next Topic: ora-39183
Goto Forum:
  


Current Time: Thu Mar 28 16:34:00 CDT 2024