Use of Oracle UTL_FILE

One disadvantage of using dbms_output is that the result is output to the screen only after the entire process is completed, in this way, the execution status cannot be monitored at any time through dbms_output output during a long running process.
Therefore, you can use the utl_file package to output the file.

Utl_file.fopen Open File
Utl_file.put_line output file
Utl_file.get_line
Utl_file.fclose close the file
Utl_file.fflush force output buffer
The first parameter of utl_file.fopen specifies the directory where the file is located. This directory must be included in the directory list specified by the utl_file_dir parameter, or specify a directory object. Otherwise, an error is returned for fopen.

1) utl_file_dir
This parameter must be set to restart the database. multiple directories can be specified (separated by commas ). If it is specified as *, it indicates any directory.
Alter system set utl_file_dir = '\ u01 \ Oracle', '\ u02 \ oracle' scope = spfile;

2) directory
Create a directory object and grant all users read and write permissions
Create or replace directory logfile_target as '/u01/oracle ';
Grant read, write on directory logfile_target to public;
View existing directory objects
Select * from dba_directories;

3) use the utl_file package to output the file
DECLARE
L_file utl_file.file_type;
BEGIN
L_file: = utl_file.fopen ('logfile _ target', 'SQL. log', 'w ');
For row in (select * from user_tables)
Loop
Utl_file.put_line (l_file, dbms_metadata.get_ddl ('table', row. table_name ));
End loop;
Utl_file.fclose (l_file );
END;
* Utl_file.fopen ('/u01/oracle', 'SQL. log', 'w') can also be written here ');
* The third parameter OPEN_MODE of fopen () can be specified
R -- read text
W -- write text
A -- append text
Rb -- read byte mode
Wb -- write byte mode
AB -- append byte mode
If 'A' or 'AB' is specified but the file does not exist, it is created first.