| copy file from one dir to another in PL/SQL [message #44507] |
Fri, 12 December 2003 07:25  |
amendenh
Messages: 16
Registered: October 2003
|
Junior Member |
|
|
Hello --
do any of you gurus know how to copy a file from one directory to another inside PL/SQL?
My problem is that we have an application through which users pass dirname/filename to a PL/SQL package.
However, the way UTL_FILE works is that you have to basically hard code the directory with which this package works. We do not want to do that. We want to have users specify their home directory - pl/sql package will pick it up and copy the file to the utl_dir.
does anyone know how to do this?
|
|
|
|
| Re: copy file from one dir to another in PL/SQL [message #44508 is a reply to message #44507] |
Fri, 12 December 2003 07:39  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
I'm not sure what you mean when you say that you have to "basically hard code the directory" when you use UTL_FILE. Why wouldn't you just use the directory name parameter passed in to the procedure in the UTL_FILE call? Where is the hardcoding coming from?
|
|
|
|
| Re: copy file from one dir to another in PL/SQL [message #44513 is a reply to message #44507] |
Fri, 12 December 2003 08:41 |
amendenh
Messages: 16
Registered: October 2003
|
Junior Member |
|
|
I was told by our DBA that the way UTL_PACKAGE works is that a directory specified in init.ora has to be used for all operations. So I can use directory name parameter passed to procedure , but this directory will have to be specified in the init.ora file. Is our DBA wrong? We actually do not have this directory specified in the init.ora, so when I tried to use the package to read a file, I got 'SYS.UTL_FILE directory not found' error.
Hence, what I was trying to do is to copy the file from whatever the user specifies as parameter to whatever the directory is specified in init.ora so that UTL_FILE package can do its magic there.
Please tell me if I am totally off course here.. :(
|
|
|
|
| Re: copy file from one dir to another in PL/SQL [message #44516 is a reply to message #44513] |
Fri, 12 December 2003 09:02 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Your DBA is correct - if your utl_file_dir parameter in init.ora is not set to * (not usually recommended), then every directory used by UTL_FILE must be explicitly listed. This is for security reasons.
So, yes, the init.ora file is the one place a directory must be "hard-coded", but certainly not in your PL/SQL.
Also, if you are on 9i, you can use directory objects which are far more flexible and no init.ora setting is required - the DBA would create directory objects and grant read/write access to specific users as needed.
|
|
|
|
|
|
| Re: copy file from one dir to another in PL/SQL [message #44520 is a reply to message #44517] |
Fri, 12 December 2003 09:56 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Both the source and target directories would need to be listed in init.ora if you are going to stick with that method and not use the new directory functionality in 9i.
Since you are on 9i, the copy is trivial since there is a new UTL_FILE function called FCOPY.
I would highly recommend your DBAs become familiar with CREATE DIRECTORY and the associated read/write grants. It really will make their job much easier and the directories more secure (since you can restrict access by user which you cannot do using the init.ora util_file_dir approach).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
