Utl_File not generating file in the server path

Question

I have executed a block of code which generates a simple text file using utl_file package

with a word 'test' and outputs the file to the location in server.

When i run the procedure it compiles successfully but the file is not

generated in the path.

set serveroutput on
declare
l_file utl_file.file_type;
l_dir varchar2(500):='WMS_IFILEOUT';
l_file_name varchar2(500):='test.txt';
begin
l_file :=utl_file.fopen(l_dir,l_file_name,'w',32767);
utl_file.put_line(l_file,'test123');
utl_file.fclose(l_file);
end;

The path and directory are available in the dba_directories

and read and write privileges are available on it.

I noticed that when i print

show parameter utl_file

then no values are displayed alongside to it.

Do I have to set this parameter in order to generate the files in the server path.

If so, can you please tell how to set it.

Thanks

Answer 1

I tried code you posted; the only modification was to rename directory).

SQL> DECLARE
  2     l_file       UTL_FILE.file_type;
  3     l_dir        VARCHAR2 (500) := 'DPDIR';
  4     l_file_name  VARCHAR2 (500) := 'test.txt';
  5  BEGIN
  6     l_file :=
  7        UTL_FILE.fopen (l_dir,
  8                        l_file_name,
  9                        'w',
 10                        32767);
 11     UTL_FILE.put_line (l_file, 'test123');
 12     UTL_FILE.fclose (l_file);
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>

Result: file is here:

So ... no, there's nothing else you should do. Everything you wrote seems to be just fine (from my point of view).

---

You said something about "show parameter utl_file" - what is that, exactly? UTL_FILE is a package, and you have to have EXECUTE privilege on it. You already have it, otherwise procedure wouldn't work at all.