OraFAQ Forum: SQL & PL/SQL » utl_file

Home » SQL & PL/SQL » SQL & PL/SQL » utl_file_dir from init.ora

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

utl_file_dir from init.ora [message #48088]

Sun, 17 October 2004 00:11

p_mukund
Messages: 16
Registered: August 2004

Junior Member

Hi

i set the utl_file_dir parameter to * from init.ora. (9i oracle)

utl_file_dir=*

init.ora is located in ora_homeora92admindbnamepfile.��

but when i tried after shut down and restart of database.

with�� select value from v$parameter where name='utl_file_dir';

it is showing null value.

So any one can help me on this.

�

Thanks

P_mukund

Report message to a moderator

Re: utl_file_dir from init.ora [message #48089 is a reply to message #48088]

Sun, 17 October 2004 14:49

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

The utl_file_dir parameter was used in Oracle 8i and earlier. In Oracle 9i, you must instead create a directory object and reference it in upper case. Example:

scott@ORA92> create or replace directory test_dir as 'c:oracle'
  2  /

Directory created.

scott@ORA92> declare
  2    v_filehandle UTL_FILE.FILE_TYPE;
  3  begin
  4    v_filehandle := UTL_FILE.FOPEN ('TEST_DIR', 'testing.txt', 'w');
  5    utl_file.put_line (v_filehandle, 'testing utl_file');
  6    utl_file.fclose (v_filehandle);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Report message to a moderator

Re: utl_file_dir from init.ora [message #48100 is a reply to message #48089]

Tue, 19 October 2004 03:13

ritz
Messages: 4
Registered: July 2004

Junior Member

Dosent this pose a problem?...suppose my procedure needs to write to another file in another directory, and i dont have permissions to create a directory object....i cant write!!
If this is to increase security and keep me from writing to undesired file...then this shud be the OS's problem...
I would still prefer the utl_file_dir parameter

Report message to a moderator

Re: utl_file_dir from init.ora [message #48113 is a reply to message #48100]

Tue, 19 October 2004 08:26

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

You don't have the privilege to create a directory object, but you do have the rights to edit the init.ora file to add to the utl_file_dir list and bounce the instance?

I have been using directory objects for some time now and would never go back to utl_file_dir settings - what a pain for many reasons.

Report message to a moderator

Re: utl_file_dir from init.ora [message #48123 is a reply to message #48113]

Tue, 19 October 2004 23:30

Thomas G
Messages: 58
Registered: April 2003

Member

Well, if there is a utl_file_dir=* in the init.ora you don't need any privilege to create objects, you can just create an output file with utl_file.fopen in any directory you want from a SQL script.

Good I stumbled over this thread, because I use SQL scripts to generate ASCII files for interfaces in many different directories, and we plan to migrate to 9.X in a few month.

Some of the directories are even named, for example, with the current date coded in, so I would have to create and drop the directory in the script everytime it runs, correct ?

Report message to a moderator

Re: utl_file_dir from init.ora [message #48129 is a reply to message #48123]

Wed, 20 October 2004 09:10

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

No one has to use directory objects for UTL_FILE operations. Most directory structures are fairly static - directory objects are great for that (define once, use many times, if directory name changes you only need to alter the one object, not any code). You have a very dynamic directory structure that suggests possibly not using directory objects.

Keep in mind that newer features such as external tables require directory objects.

Report message to a moderator

Previous Topic:	Checking for Bad Data or Special Characters
Next Topic:	application context performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 25 21:55:55 CDT 2025