Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE invalid directory problem (SunOS 5.8, Oracle 8.1.7)
icon9.gif  UTL_FILE invalid directory problem [message #423472] Thu, 24 September 2009 09:57 Go to next message
l_majid
Messages: 8
Registered: August 2009
Location: Paris
Junior Member
Hello,

I'm trying to write output into a file using UTL_FILE. I keep getting the INVALID_PATH error.

create or replace directory MIG_395_DIR as '&1';
...
DECLARE
	LOG_FILE utl_file.file_type;
BEGIN
    LOG_FILE:=UTL_FILE.FOPEN('MIG_395_DIR', 'test.txt', 'w');
EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.put_line('Cannot open file INVALID_PATH ' ||SQLCODE||' : '||SQLERRM);      
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Cannot open file OTHERS '||SQLCODE||' : '||SQLERRM);
END;
...


The output is something like

old   1: create or replace directory MIG_395_DIR as '&1'
new   1: create or replace directory MIG_395_DIR as '/app/bscs01/users/uat/MIGGE/LOG/'
Directory created.
Cannot open file INVALID_PATH 1 : User-Defined Exception


I checked the following :
1 - The directory MIG_395_DIR has been created successfully :
OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
-----------------
SYS                            FICHIERS_OUT   /tmp/
SYS                            TEST /tmp
SYS                            MIG_395_DIR /app/bscs01/users/uat/MIGGE/LOG/ 

2 - The directory /app/bscs01/users/uat/MIGGE/LOG/ exists and has mode drwxrwxrwx
3 - select * from v$parameter where name = 'utl_file_dir'; gives the following result:
 440	utl_file_dir	2	/tmp	FALSE	FALSE	FALSE	FALSE	FALSE	utl_file accessible directories list

4 - No entry for 'utl_file' in init.ora :
/opt/oracle/product/8.1.7/dbs> grep -i UTL init.ora
/opt/oracle/product/8.1.7/dbs>
returns no data
5 - No disk space problem.


I tried the following, but never had any chance to open my file (allways same exception)

1 - I tried this :
UTL_FILE.FOPEN('/app/bscs01/users/uat/MIGGE/LOG/', 'test.txt', 'w');

2 - Then :
UTL_FILE.FOPEN('TEST', 'test.txt', 'w');

3 - then :
UTL_FILE.FOPEN('/tmp/', 'test.txt', 'w');

4 - then :
UTL_FILE.FOPEN('/tmp', 'test.txt', 'w');


Any help wold be appreciated.





Re: UTL_FILE invalid directory problem [message #423474 is a reply to message #423472] Thu, 24 September 2009 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Part of your problem may be due to the fact that your are using a version that has not been supported this century.

IIRC, UTL_FILE placement was controlled by init_SID.ora parameter utl_file_dir
Re: UTL_FILE invalid directory problem [message #423475 is a reply to message #423472] Thu, 24 September 2009 10:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Your post states the version of Oracle is 8.1.7
For versions below 9i, you are supposed to use UTL_FILE_DIR.
Quote:
create or replace directory MIG_395_DIR as '&1';
...
DECLARE
	

What does that ... indicate here?
You are not telling us the whole story. Even in 9i and above,
after creating the directory you need to give appropriate privileges to the user.
Re: UTL_FILE invalid directory problem [message #423476 is a reply to message #423474] Thu, 24 September 2009 10:18 Go to previous messageGo to next message
l_majid
Messages: 8
Registered: August 2009
Location: Paris
Junior Member
Thanks you for the reply.

We are migrating to a newer version of Oracle (10g probably), we need to run these scripts on the oracle 8, prior to the migration.
I have no control over the oracle version we are using.

And yes you remember correctly, I found the parameter : utl_file_dir=/tmp
in /opt/oracle/product/8.1.7/dbs/initIAM70T05.ora

Any other suggestions please ?
I have no clue what could be wrong.
Re: UTL_FILE invalid directory problem [message #423477 is a reply to message #423476] Thu, 24 September 2009 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>And yes you remember correctly, I found the parameter : utl_file_dir=/tmp
>in /opt/oracle/product/8.1.7/dbs/initIAM70T05.ora
If DB was not started using this file then it is not relevant.

>Any other suggestions please ?
>I have no clue what could be wrong.
parameter utl_file_dir must be set to any directory to be used by UTL_FILE prior to starting the DB.
Re: UTL_FILE invalid directory problem [message #423478 is a reply to message #423476] Thu, 24 September 2009 10:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does this return - the values in v$parameter are the ones that matter:
select * from v$parameter where name = 'utl_file_dir'
Re: UTL_FILE invalid directory problem [message #423479 is a reply to message #423475] Thu, 24 September 2009 10:27 Go to previous messageGo to next message
l_majid
Messages: 8
Registered: August 2009
Location: Paris
Junior Member
Mahesh Rajendran wrote on Thu, 24 September 2009 10:12
Your post states the version of Oracle is 8.1.7
For versions below 9i, you are supposed to use UTL_FILE_DIR.
Quote:
create or replace directory MIG_395_DIR as '&1';
...
DECLARE
	

What does that ... indicate here?
You are not telling us the whole story. Even in 9i and above,
after creating the directory you need to give appropriate privileges to the user.


Thank you for you reply.

I created the directory MIG_395_DIR so i can write in it. That's the way i'm used to use UTL_FILES Confused

As suggested by BlackSwan, I checked initSID.ora and UTL_FILE_DIR has the value '/tmp'
but i've also tried UTL_FILE.FOPEN('/tmp', 'test.txt', 'w'); and UTL_FILE.FOPEN('/tmp/', 'test.txt', 'w'); without better luck.

"..." contains irrelevant code :
WHENEVER SQLERROR EXIT 1 ROLLBACK;
SET LINESIZE 90
SET serveroutput on SIZE 1000000
SET ECHO OFF

Re: UTL_FILE invalid directory problem [message #423481 is a reply to message #423472] Thu, 24 September 2009 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Just for drill issue following command & post results back here.

ls -ltr /tmp/test*
Re: UTL_FILE invalid directory problem [message #423482 is a reply to message #423478] Thu, 24 September 2009 10:32 Go to previous messageGo to next message
l_majid
Messages: 8
Registered: August 2009
Location: Paris
Junior Member
JRowbottom wrote on Thu, 24 September 2009 10:24
What does this return - the values in v$parameter are the ones that matter:
select * from v$parameter where name = 'utl_file_dir'


the answer was in the original post Smile

l_majid wrote on Thu, 24 September 2009 09:57

3 - select * from v$parameter where name = 'utl_file_dir'; gives the following result:
440	utl_file_dir	2	/tmp	FALSE	FALSE	FALSE	FALSE	FALSE	utl_file accessible directories list

Re: UTL_FILE invalid directory problem [message #423483 is a reply to message #423479] Thu, 24 September 2009 10:38 Go to previous messageGo to next message
l_majid
Messages: 8
Registered: August 2009
Location: Paris
Junior Member
That's odd Shocked

preprodv7@uat:IAM70T01[/opt/oracle]$ls -ltr /tmp/test*
-rw-r--r--   1 ora817   dba            0 Sep 24 14:29 /tmp/test


i wonder why i kept getting the UTL_FILE.INVALID_PATH exception ?
need to run another test ...


Re: UTL_FILE invalid directory problem [message #423486 is a reply to message #423483] Thu, 24 September 2009 10:48 Go to previous message
l_majid
Messages: 8
Registered: August 2009
Location: Paris
Junior Member
Thank you everybody for you replies.

BlackSwan : thank you very much, i don't know how i got the invalid directory exception earlier, but now i'm not getting it anymore.. seems to work now. I guess i need to change my glasses Shocked or maybe to get more sleep.

Mahesh Rajendran : you're right, no need to create a directory, utl_file_dir variable is enough. Thank you.
Previous Topic: Upload Word document; view contents of Word document
Next Topic: query
Goto Forum:
  


Current Time: Sun Dec 11 02:19:31 CST 2016

Total time taken to generate the page: 0.14084 seconds