Print Friendly, PDF & Email

 
REQUEST

I want to read the content of a file through PL/SQL block

 
RELATED

 
TESTCASE

This post is an extension of WRITING A OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK. In the previous post I created files through PL/SQL block and now I’m going to read those files.

oracle@dbpilot.net: ls -lh /tmp/utl_file_dir/ --time-style='+'
total 8.0K
-rw-r--r-- 1 oracle oinstall 55  my_file.txt
-rw-r--r-- 1 oracle oinstall 96  utl_user.txt

oracle@dbpilot.net: cat /tmp/utl_file_dir/my_file.txt
A test of utl_file_dir parameter and UTL_FILE package.

oracle@dbpilot.net: ls -lh /tmp/directory_object/ --time-style='+'
total 8.0K
-rw-r--r-- 1 oracle oinstall 56  my_file.txt
-rw-r--r-- 1 oracle oinstall 92  utl_user.txt

oracle@dbpilot.net: cat /tmp/directory_object/my_file.txt
A test of the UTL_FILE package with a directory object.

 

UTL_FILE_DIR

A PL/SQL block is pretty simple

SET SERVEROUTPUT ON 

DECLARE
    FILE_DIR     VARCHAR2(100);
    FILE_NAME    VARCHAR2(100); 
    FILE_CONTENT VARCHAR2(4000);
    FILE         UTL_FILE.FILE_TYPE;
BEGIN
    FILE_DIR  := '/tmp/utl_file_dir';
    FILE_NAME := 'my_file.txt';
	
    BEGIN
       FILE := UTL_FILE.FOPEN(FILE_DIR, FILE_NAME, 'R', 32767);
    EXCEPTION 
       WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Open_file_error : Unable to open the file.');
    END; 
	
    BEGIN
       LOOP 
          BEGIN 
             UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
             DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);
          -- End of the file or Others error
          EXCEPTION 
             WHEN OTHERS THEN EXIT;
          END;
       END LOOP;
    EXCEPTION 
       WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Read_file_error : Unable to read the file.');	
    END;

    UTL_FILE.FCLOSE(FILE);	
END;
/

A test of utl_file_dir parameter and UTL_FILE package.

PL/SQL procedure successfully completed.

 

DATABASE DIRECTORY OBJECT

SET SERVEROUTPUT ON 

DECLARE
    FILE_NAME    VARCHAR2(100); 
    FILE_CONTENT VARCHAR2(4000);
    FILE         UTL_FILE.FILE_TYPE;
BEGIN
    FILE_NAME := 'my_file.txt';
	
    BEGIN
       FILE := UTL_FILE.FOPEN('DROPZONE', FILE_NAME, 'R', 32767);
    EXCEPTION 
       WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Open_file_error : Unable to open the file.');
    END; 
	
    BEGIN
       LOOP 
          BEGIN 
             UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
             DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);
          -- End of the file or Others error
          EXCEPTION 
             WHEN OTHERS THEN EXIT;
          END;
       END LOOP;
    EXCEPTION 
       WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Read_file_error : Unable to read the file.');	
    END;

    UTL_FILE.FCLOSE(FILE);	
END;
/

A test of the UTL_FILE package with a directory object.

PL/SQL procedure successfully completed.

As you see the outputs of PL/SQL blocks are exactly the same as the content of the files.

 

READ and WRITE priveliges

What happens if I try to do the same under another user? Will the PL/SQL block execute successfully or not?

SQL> CONNECT UTL_USER/UTL_USER
Connected.

SQL> SHOW USER
USER is "UTL_USER"

SET SERVEROUTPUT ON 

DECLARE
    FILE_DIR     VARCHAR2(100);
    FILE_NAME    VARCHAR2(100); 
    FILE_CONTENT VARCHAR2(4000);
    FILE         UTL_FILE.FILE_TYPE;
BEGIN
    FILE_DIR  := '/tmp/utl_file_dir';
    FILE_NAME  := 'my_file.txt';
	
    BEGIN
       FILE := UTL_FILE.FOPEN(FILE_DIR, FILE_NAME, 'R', 32767);
    EXCEPTION 
       WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Open_file_error : Unable to open the file.');
    END; 
	
    BEGIN
       LOOP 
          BEGIN 
             UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
             DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);			
          -- End of the file or Others error
          EXCEPTION 
             WHEN OTHERS THEN EXIT;
          END;
       END LOOP;
    EXCEPTION 
       WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Read_file_error : Unable to read the file.');	
    END;

    UTL_FILE.FCLOSE(FILE);	
END;
/

A test of utl_file_dir parameter and UTL_FILE package.

PL/SQL procedure successfully completed.

The UTL_FILE_DIR does not require any special privileges to read the file. And what about the database directory objects?

SET SERVEROUTPUT ON 

DECLARE
    FILE_NAME    VARCHAR2(100); 
    FILE_CONTENT VARCHAR2(4000);
    FILE         UTL_FILE.FILE_TYPE;
BEGIN
    FILE_NAME := 'my_file.txt';
	
    BEGIN
       FILE := UTL_FILE.FOPEN('DROPZONE', FILE_NAME, 'R', 32767);
    END; 
	
    BEGIN
       LOOP 
          BEGIN 
             UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
             DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);			
          -- End of the file or Others error
          EXCEPTION 
             WHEN OTHERS THEN EXIT;
          END;
       END LOOP;
    END;

    UTL_FILE.FCLOSE(FILE);	
END;
/

(*) Open_file_error : Unable to open the file.

PL/SQL procedure successfully completed.

Here an exception raised. If I remove all exceptions, I will get exact error

SET SERVEROUTPUT ON 

DECLARE
    FILE_NAME    VARCHAR2(100); 
    FILE_CONTENT VARCHAR2(4000);
    FILE         UTL_FILE.FILE_TYPE;
BEGIN
    FILE_NAME := 'my_file.txt';
	
    BEGIN
       FILE := UTL_FILE.FOPEN('DROPZONE', FILE_NAME, 'R', 32767);
    END; 
	
    BEGIN
       LOOP 
          BEGIN 
             UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
             DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);			
          -- End of the file or Others error
          EXCEPTION 
             WHEN OTHERS THEN EXIT;
          END;
       END LOOP;
    END;

    UTL_FILE.FCLOSE(FILE);	
END;
/

DECLARE
*
ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 9

The solution is simple, grant appropriate permissions under SYS user and try again. The owner is always SYS user

SQL> GRANT READ ON DIRECTORY SYS.DROPZONE TO UTL_USER;

Grant succeeded.

SQL> CONNECT UTL_USER/UTL_USER
Connected.

SET SERVEROUTPUT ON 

DECLARE
    FILE_NAME    VARCHAR2(100); 
    FILE_CONTENT VARCHAR2(4000);
    FILE         UTL_FILE.FILE_TYPE;
BEGIN
    FILE_NAME := 'my_file.txt';
	
    BEGIN
       FILE := UTL_FILE.FOPEN('DROPZONE', FILE_NAME, 'R', 32767);
    END; 
	
    BEGIN
       LOOP 
          BEGIN 
             UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
             DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);			
          -- End of the file or Others error
          EXCEPTION 
             WHEN OTHERS THEN EXIT;
          END;
       END LOOP;
    END;

    UTL_FILE.FCLOSE(FILE);	
END;
/

A test of the UTL_FILE package with a directory object.

PL/SQL procedure successfully completed.

For more details refer to the official documentation.

 
 

Version  : 14:02 18.01.2018
Platform : Red Hat 5.11
Database : Oracle database 12.1.0.1.0