CODEWITHSUNDEEP
databaseoracle-databaseutl-file
Gautam S
Gautam S

Reputation: 41

Utl_File not generating file in the server path

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

Upvotes: 0

Views: 800

Answers (1)

Littlefoot
Littlefoot

Reputation: 143053

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:

enter image description 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.

Upvotes: 0

Related Questions