Home » SQL & PL/SQL » SQL & PL/SQL » tul_file_open and OpenVMS
tul_file_open and OpenVMS [message #8623] Thu, 04 September 2003 13:38 Go to next message
gary gladstone
Messages: 2
Registered: September 2003
Junior Member
I have the following sql procedure and I am looking to write the file to a specific directory. No matter what I do, it will only write it ot the ora_utl_dir. I have different users who will use this procedure and I want them to write the files to their own directory.

Create or replace PROCEDURE test
as
-- ndir varchar2(256) :='DKD103:[[ORACLE_PUBLIC]]';
file_name VARCHAR2(256) := 'c.txt';
file_text VARCHAR2(256) := 'Hi there';
file_id UTL_FILE.FILE_TYPE;
BEGIN
dbms_output.put_line(file_text);
-- dbms_output.put_line('file location is ' || ndir);
file_id := UTL_FILE.FOPEN( 'ORA_UTL_DIR:', file_name, 'W');
Re: tul_file_open and OpenVMS [message #8624 is a reply to message #8623] Thu, 04 September 2003 13:43 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If on 8i, have all the directories been explicitly listed in the UTL_FILE_DIR initialization parameter?

If on 9i, be sure to use directory objects instead of explicit directory paths in code. No INIT.ORA settings required.
Re: utl_file_open and OpenVMS [message #8625 is a reply to message #8624] Thu, 04 September 2003 14:01 Go to previous messageGo to next message
gary gladstone
Messages: 2
Registered: September 2003
Junior Member
Todd, thankyou. we are on 9i. I am not sure what you mean. I am the sys adm trying to help the DBA's who are not VMS savy. We do have a utl_file_dir in the init.ora file.

What we are trying to do is develop a procedure that can be used by more than one person and will put the output in different dir's so we can keep the users from getting confused on whose file is whose.

Gary
Re: utl_file_open and OpenVMS [message #8626 is a reply to message #8625] Thu, 04 September 2003 15:24 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The use of UTL_FILE_DIR in 9i is deprecated (this is a good thing). Directory objects are much more flexible with configuration and security and do not require restarting the instance everytime you add a new directory destination.

Instead of referencing the explicit literal directory paths in your code (as in the first parameter to UTL_FILE.FOPEN for example), you first create directory objects that point to the actual directories. Privileges (read/write) to these directory objects are then granted to specific users.

So, now, user A can read directory 'abc' and write to directory 'bcd', while user B can only read directory 'xyz'. This type of flexibility was unavailable in 8i.

Also, if your directory path changes, you simply replace the definition of the directory object - no PL/SQL code has to change.

Old way:

f := utl_file.fopen('/somedir/xyz', 'test.txt', 'r');


New way:

SYSTEM>create or replace directory xyz as '/somedir/xyz';
 
SYSTEM>grant read on directory xyz to a;
 
f := utl_file.fopen('xyz', 'test.txt', 'r');
Previous Topic: Group By
Next Topic: List all tables in Database? How?
Goto Forum:
  


Current Time: Thu Mar 28 11:35:02 CDT 2024