Oracle PL/SQL

utf_file and directories

In case you are maintaining a pl/sql program developed by someone else and that program writes into a file, then basically that file is written to a folder/directory. And since the directories are saved in codes, you have to find the meaning (or location) of those codes. The code below will help you display the information required.

SELECT * FROM ALL_DIRECTORIES;

The query above will display all the directories accessible to the current user. They are folders in the server where you can write your output files. For some reason, I forgot about this twice already and had to scour the web to find it. The problem is, I also forgot the keywords. So, I'm writing it now.

To register new directories, you can follow the example below.

Example
create or replace directory mydir as 'c:mydirectory';

To use, run the code below.

declare
f utl_file.file_type;
begin
f := utl_file.fopen('MYDIR', 'myfilename.txt', 'w');
utl_file.put_line(f, 'just another line text 1');
utl_file.put_line(f, 'just another line text 2');
utl_file.fclose(f);
end;