WRITE TO A FILE USING UTL_FILE

• Create a Table

 Create Table xx_parameters (

 param_name VARCHAR2(100) ,

 param_value NUMBER );

• Input some parameters
• Create a Directory 

CREATE OR REPLACE DIRECTORY DIR_MYTEMP AS '/home/oracle/ebsutl12/';

• Create the Procedure

CREATE OR REPLACE PROCEDURE xxtest_procedure_utl

AS

   CURSOR c_cur1

   IS

      SELECT param_name, param_value

        FROM xx_parameters;

   l_file   UTL_FILE.file_type;

   l_dir    VARCHAR2 (50)      := 'DIR_MYTEMP';

   l_name   VARCHAR2 (20)      := 'mydatafile.txt';

BEGIN

   l_file := UTL_FILE.fopen (l_dir, l_name, 'W');

   FOR cur_rec IN c_cur1

   LOOP

      UTL_FILE.put_line (l_file,

                         cur_rec.param_name || ',' || cur_rec.param_value

                        );

   END LOOP;

   UTL_FILE.fclose (l_file);

EXCEPTION

   WHEN UTL_FILE.invalid_path

   THEN

      DBMS_OUTPUT.put_line (‘invalid_path’);

      RAISE;

   WHEN UTL_FILE.invalid_mode

   THEN

      DBMS_OUTPUT.put_line (‘invalid_mode’);

      RAISE;

   WHEN UTL_FILE.invalid_filehandle

   THEN

      DBMS_OUTPUT.put_line (‘invalid_filehandle’);

      RAISE;

   WHEN UTL_FILE.invalid_operation

   THEN

      DBMS_OUTPUT.put_line (‘invalid_operation’);

      RAISE;

   WHEN UTL_FILE.read_error

   THEN

      DBMS_OUTPUT.put_line (‘read_error’);

      RAISE;

   WHEN UTL_FILE.write_error

   THEN

      DBMS_OUTPUT.put_line (‘write_error’);

      RAISE;

   WHEN UTL_FILE.internal_error

   THEN

      DBMS_OUTPUT.put_line (‘internal_error’);

      RAISE;

END xxtest_procedure_utl;

/

• Executing the procedure will create a file named mydatafile.txt in directory /home/oracle/ebsutl12/