Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE and creating DIRECTORIES (10.2.0.1)
UTL_FILE and creating DIRECTORIES [message #336229] Fri, 25 July 2008 06:01 Go to next message
pmapc
Messages: 46
Registered: July 2008
Member
hi,

I am new in oracle, sorry if the answer of my question is clear .
In my application I need to read different files in different directories from my server which are about 2000000 files(in 2000 folders) that each of them has been put in different paths.I want to use UTL_FILE to access these files and read them from the server file system, but my problem is accessing files via this command force me to create directories for each of my unique paths (2000 paths).I try different formats of addressing but none of them worked and each time I got this error message:
ORA-29280: invalid directory path

I think in my case it is sensible to create a root directory and then generate my whole address using the directory and remaining part of my address (folders and sub folders):
create or replace directory DIR_TEMP as 'c:';
v_filehandle := UTL_FILE.fopen('DIR_TEMP\Standards\as12309', p_filename, 'r');

I want to know is there something wrong with my command or it is impossible at all in oracle environment.
Does anyone have any suggestion to overcome this problem?

Thanks in advanced,



Re: UTL_FILE and creating DIRECTORIES [message #336234 is a reply to message #336229] Fri, 25 July 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First parameter is an Oracle directory not a mixed value from Oracle directory and OS path.
Create a new directory for the whole stuff.

Regards
Michel
Re: UTL_FILE and creating DIRECTORIES [message #336237 is a reply to message #336229] Fri, 25 July 2008 06:22 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Thanks for reply.
so you mean for all of my unique OS paths (2000 paths) I should create an equivalent oracle path?!
this way is hard for me to manage, I really need something like what mentioned (oracle path+OS path)
any other idea?
Re: UTL_FILE and creating DIRECTORIES [message #336242 is a reply to message #336237] Fri, 25 July 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, this is what I meant.
Note that Oracle is a data manager not an file one.

Regards
Michel
Re: UTL_FILE and creating DIRECTORIES [message #336247 is a reply to message #336242] Fri, 25 July 2008 06:37 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
I don't want Oracle to manage my files, I just want to read them from a path (preferably on the Oracle server) and let users to download them via my application. Is there another way other than using UTL_FILE which help me to remove this obstacle?
I just want to read these files and show them to the users.
Re: UTL_FILE and creating DIRECTORIES [message #336252 is a reply to message #336247] Fri, 25 July 2008 07:25 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
try using a java stored procedure, then wrap pl/sql on it, you should be able to pass different directories on it.
Re: UTL_FILE and creating DIRECTORIES [message #336264 is a reply to message #336247] Fri, 25 July 2008 08:20 Go to previous message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What you want is Oracle acts as a file server which is not.
Anyway, with the help of Java extension you can do what you want. Of course this may be a security hole if you don't really take care of privileges and directories your users will be able to access.

Regards
Michel
Previous Topic: Regular Expression ambiguous
Next Topic: Date range SQL CURSOR
Goto Forum:
  


Current Time: Wed Nov 13 05:49:30 CST 2024