UTL_FILE in oracle

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> CREATE DIRECTORY CDD_FILES AS 'D:\Oracle_2019\CDD';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY CDD_FILES TO SCOTT;

Grant succeeded.

SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='CDD_FILES';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ----------------------------
SYS                            CDD_FILES                      D:\Oracle_2019\CDD

SQL> GRANT EXECUTE,DEBUG ON UTL_FILE TO SCOTT;

Grant succeeded.

SQL>

SQL> conn scott/tiger
Connected.
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l_handler     UTL_FILE.file_type;
  3     l_file_name   VARCHAR2 (100)
  4        :=    LOWER (SYS_CONTEXT ('USERENV', 'DB_NAME'))
  5           || '_'
  6           || TO_CHAR (SYSDATE, 'YYYYMMDD')
  7           || '.log';
  8  BEGIN
  9     -- open the file for input
 10     l_handler := UTL_FILE.fopen ('CDD_FILES', l_file_name, 'w');
 11     -- write a line into file
 12     UTL_FILE.put_line (l_handler, 'Hello...');
 13     UTL_FILE.put_line (l_handler, 'Rajasekhar...');
 14     -- close the file
 15     UTL_FILE.fclose (l_handler);
 16  EXCEPTION
 17     WHEN OTHERS
 18     THEN
 19        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
 20        UTL_FILE.fclose (l_handler);
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL>

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l_handler     UTL_FILE.file_type;
  3     l_read_line   VARCHAR2 (32765);
  4  BEGIN
  5     -- open the file for output
  6     l_handler := UTL_FILE.fopen ('CDD_FILES', 'xe_20190915.log', 'r');
  7
  8     LOOP
  9        -- read a line from file
 10        UTL_FILE.get_line (l_handler, l_read_line);
 11        DBMS_OUTPUT.PUT_LINE (l_read_line);
 12     END LOOP;
 13  EXCEPTION
 14     WHEN NO_DATA_FOUND
 15     THEN
 16        -- close the file
 17        UTL_FILE.fclose (l_handler);
 18     WHEN OTHERS
 19     THEN
 20        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
 21        UTL_FILE.fclose (l_handler);
 22  END;
 23  /
Hello...
Rajasekhar...

PL/SQL procedure successfully completed.

SQL>


SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l_handler     UTL_FILE.file_type;
  3     l_file_name   VARCHAR2 (100)
  4        :=    LOWER (SYS_CONTEXT ('USERENV', 'DB_NAME'))
  5           || '_'
  6           || TO_CHAR (SYSDATE, 'YYYYMMDD')
  7           || '.log';
  8  BEGIN
  9     -- open the existing file for input
 10     l_handler := UTL_FILE.fopen ('CDD_FILES', l_file_name, 'a');
 11     -- write a line into file
 12     UTL_FILE.put_line (l_handler, 'How are you...?');
 13     -- close the file
 14     UTL_FILE.fclose (l_handler);
 15  EXCEPTION
 16     WHEN OTHERS
 17     THEN
 18        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
 19        UTL_FILE.fclose (l_handler);
 20  END;
 21  /

PL/SQL procedure successfully completed.

SQL>

File output should be:

Hello...

Rajasekhar...

How are you...?

SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
  2     -- remove an existing file from the directory
  3     UTL_FILE.fremove (location=>'CDD_FILES',filename=>'xe_20190915.log');
  4  EXCEPTION
  5     WHEN OTHERS
  6     THEN
  7        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>


SET SERVEROUTPUT ON

DECLARE
   l_handler    UTL_FILE.file_type;
   l_handler1   UTL_FILE.file_type;
BEGIN
   l_handler := UTL_FILE.fopen ('CDD_FILES', 'xe_20190915.log', 'R');
   l_handler1 := UTL_FILE.fopen ('CDD_FILES', 'xe_20190916.log', 'W');

   --IF UTL_FILE.is_open (l_handler)
   IF UTL_FILE.is_open (l_handler) = TRUE
   THEN
      DBMS_OUTPUT.put_line ('l_handler is open and in read mode');
   END IF;

   --IF UTL_FILE.is_open (l_handler1)
   IF UTL_FILE.is_open (l_handler1) = TRUE
   THEN
      DBMS_OUTPUT.put_line ('l_handler1 is open and in write mode');
   END IF;

   -- closes all open file handles for the session
   UTL_FILE.fclose_all;
   -- UTL_FILE.fclose (l_handler);
   -- UTL_FILE.fclose (l_handler1);

   IF (UTL_FILE.is_open (l_handler1) OR UTL_FILE.is_open (l_handler1))
   THEN
      DBMS_OUTPUT.put_line ('l_handler or l_handler is still open');
   ELSE
      DBMS_OUTPUT.put_line ('both l_handler and l_handler1 are closed');
   END IF;
END;
/