OraFAQ Forum: Server Administration » UTL_FILE

Home » RDBMS Server » Server Administration » UTL_FILE_DIR in 10g

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

UTL_FILE_DIR in 10g [message #167078]

Tue, 11 April 2006 04:24

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Hi,

I tried setting the UTL_FILE_DIR in init.ora file under pfile directory under 10g oracle.

But when trying with the utl_file.fopen it is giving invalid directory path, while checking the value for utl_file_dir in v$parameter it is giving null value.

Any idea how to set it
Thanks and Regards,

nirmal

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167118 is a reply to message #167078]

Tue, 11 April 2006 07:46

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

UTL_FILE_DIR is depreciated long back.
You are supposed to use CREATE DIRECTORY.
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14093

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167119 is a reply to message #167078]

Tue, 11 April 2006 07:47

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

Are you using a PFILE or SPFILE? Try:

SQL> alter system set UTL_FILE_DIR = '/tmp' scope=spfile;

As suggested above, you should rather use database directories.

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167130 is a reply to message #167078]

Tue, 11 April 2006 08:14

sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata

Senior Member

Hi Nirmalnarayan,
You have to create a pfile from the spfile if u r using Oracle 10g.
First shutdown the Database.Then create a pfile by giving the command
CREATE PFILE='PFILE-NAME' FROM SPFILE='SPFILE-NAME';
Then change the parameter of UTL_FILE_DIR and set the directory path.
Now create the SPFILE from the PFILE by giving the command
CREATE SPFILE='SPFILE-NAME' FROM PFILE='PFILE-NAME';

Now startup the Database.
IF u now query ur v$Parameter view then u will find that UTL_FILE_DIR had been set to ur specified path.
Now u will have no trouble to run utl_file.fopen.
Try it out.

Thanks & Regards,
Sanka

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167145 is a reply to message #167130]

Tue, 11 April 2006 09:37

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

@sanka yanka,
did you even read Frank's reply. That is how you alter an spfile parameter, NOT your method.

Regards
Jim

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167228 is a reply to message #167145]

Wed, 12 April 2006 00:06

sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata

Senior Member

hello JSI2001,
Yea, I read Frank's Reply. But from that method which I wrote also works fine. you can also do it by this method.
Regards,
Sanka

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167259 is a reply to message #167228]

Wed, 12 April 2006 03:22

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Quote:

You have to create a pfile from the spfile if u r using Oracle 10g

No you don't.

Quote:

First shutdown the Database

Shutdown 1.

Quote:

Then create a pfile by giving the command
CREATE PFILE='PFILE-NAME' FROM SPFILE='SPFILE-NAME';

And how do you do that? The database is shut down.

Quote:

Now create the SPFILE from the PFILE by giving the command

And how do you do that? The database is shut down.

Quote:

Now startup the Database.

This entire method would not work at all. Have you followed your own steps?

If you insist on using this method (which is far more convoluted than is necessary then it would be:

1. Create pfile from spfile.
2. Shutdown the db.
3. Edit the pfile to include the utl_file_dir parameter
4. startup the database to point at the pfile:
startup pfile= pfile loc
5. create spfile from pfile.
6. Shutdown the database
7. startup the database.

Or you could issue

1. alter system set UTL_FILE_DIR = '/tmp' scope=spfile;
2. shutdown immediate
3. startup.

I know which one is the only sensible option.

Jim

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #489194 is a reply to message #167259]

Thu, 13 January 2011 13:50

jaimitop
Messages: 2
Registered: January 2011

Junior Member

YOUR procedure does not work Mad

: database says that utl_file_dir is not a modifiable parameter when i issue "alter system.....".

PFILE/SPFILE procedure does work Surprised

; only minor adjustments:

1. create pfile='pfile_file' from spfile;
2. modifiy pfile_file
3. create spfile='spfile_file' from pfile='pfile_file';
4. shutdown database
5. replace default spfile (%oracle_home%\database\spfileDBNAME.ora) with spfile_file
6. startup database
7. show parametrer utl_file_dir is ok.

Bye

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #489202 is a reply to message #489194]

Thu, 13 January 2011 14:09

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

NEVER ever use UTL_FILE_DIR in the current releases.
This is a very bad practice and a BIG security hole.

Regards
Michel

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #489329 is a reply to message #489202]

Fri, 14 January 2011 09:16

jaimitop
Messages: 2
Registered: January 2011

Junior Member

i know it's not intended to be used any more ...but... it's a legacy app compatibility issue... Confused

Report message to a moderator

Previous Topic:	>startup open after shutdown throws ORA-12514 error
Next Topic:	database10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 20:54:34 CDT 2024