Re: UTL_FILE

From: <manoj67_at_hotmail.com>
Date: 1998/03/30
Message-ID: <6fpr0s$q0s$1@nnrp1.dejanews.com>#1/1

Hi,

 I ran the exact same code which you mentioned, except for the fact that it was run on Windows NT, so we used 'C:\temp\'. It ran fine and created a file called venk.txt.

 Our UTL_FILE_DIR was set to c:\temp on server in the INIT file. I restarted the instance so that the parameter is in effect.

Check the following:--

SQL> select value from v$parameter

     where name = 'utl_file_dir';

VALUE

---

c:\temp

Make sure that this query returns you the proper value as you mentioned you have set in the INITsid.ora file.

Also look for the UNIX directory permissions where the file will be written.

Suggestions: Try using /tmp as the UTL_FILE_DIR and restart the instance. Then see if it works. If it works and creates the file then you can find out the username who created it. Then you should login as that user and check the permission on the directory which you were using before using /tmp.

Hope this helps,

Let me know if it worked and how it worked.

=====Test run summary=====
SQL> r
  1 create or replace procedure
  2 ajfile as the_file utl_file.file_type;   3 BEGIN

  4   the_file := utl_file.fopen('c:\temp','venk.txt','W');
  5   utl_file.put_line(the_file,'Hello world');
  6   utl_file.fclose(the_file);

  7 EXCEPTION
  8 when utl_file.INVALID_PATH then
  9 raise_application_error(-20000, 'UTL_FILE: Invalid path');  10* END; Procedure created.

SQL> execute ajfile

PL/SQL procedure successfully completed.

good luck,
Manoj Jain

Oracle DBA

In article <6fpf3l$f6a$1_at_nnrp1.dejanews.com>,   ajayraina_at_hotmail.com wrote:
>
> I have written the following Oracle PL/SQL procedure to create a small file.
>
> create or replace procedure ajfile as
> the_file utl_file.file_type;
> BEGIN
> the_file := utl_file.fopen('/proj/hedging','venk.txt','W');
> utl_file.put_line(the_file,'Hello world');
> utl_file.fclose(the_file);
> EXCEPTION
> when utl_file.INVALID_PATH then
> raise_application_error(-20000, 'UTL_FILE: Invalid path');
> END;
>
> I am getting the following error on execution :
>
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 85
> ORA-06512: at "SYS.UTL_FILE", line 120
> ORA-06512: at "RAINAA.AJFILE", line 4
> ORA-06512: at line 1
>
> The parameter UTL_FILE_DIR with the path used in the program
> has been entered in the init<SID>.ora file which is used in the
> PL/SQL program. Still, this error occurs. Can anybody out there help me?
>
> Best regards,
>
> Ajay.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Mar 30 1998 - 00:00:00 CST