Reading Text Files using Oracle PL/SQL and UTL_FILE

Set ----alter system set utl_file_dir='/foo/bar/dir1','/foo/baz/dir2','/tmp' scope=spfile

The first step we need to perform is nothing but letting the Oracle database know on which directory we are working (reading or writing files only from that directory). This can be achieved using the following command:

CREATE DIRECTORY sampledata AS 'c:sampledata';

The above command creates a new directory entry into the Oracle database with a logical name to the directory, "SAMPLEDATA" (pointing to the path "c:sampledata"). To execute the above command, you need to have certain privileges. I suggest you log in using "SYSTEM" user (or contact your database administrator on this issue). If you wanted to list all the directories registered, you can use the following command:

SELECT * FROM ALL_DIRECTORIES;

The second step we need to perform is providing necessary permissions to the user to work with that directory. This can be achieved with something like the following:

GRANT read, write ON DIRECTORY sampledata TO PUBLIC;

The above step makes sure that the directory "sampledata" (or "c:sampledata") is accessible to every user (PUBLIC). It is not at all a good idea to provide grants to the PUBLIC. I suggest you provide the grants only to the required users (who really need them).

The third step we need to perform is simply creating a new text file within the folder "c:sampledata". I hope you can do it yourself.

The last and final step is trying to read the file using PL/SQL. Open your SQL*PLUS environment and copy the following code:

declare

f utl_file.file_type;

s varchar2(200);

begin

f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');

utl_file.get_line(f,s);

utl_file.fclose(f);

dbms_output.put_line(s);

end;

/

Created by Suyash application dba HCL-----

Implemented this code.

*************************************************************

DECLARE

TYPE IDCurTyp IS REF CURSOR;

fo UTL_FILE.FILE_TYPE;

varRow VARCHAR2(4000);

cur_output IDCurTyp;

BEGIN

fo := UTL_FILE.FOPEN('C:\BACKUP','TEST.csv', 'W', 2000);

OPEN cur_output FOR

'SELECT ''"'' OID ''",'' DOC_PATH ''"'' FROM IREPS_PURGED_DOC_DETAILS';

LOOP

FETCH cur_output INTO varRow;

EXIT WHEN cur_output%NOTFOUND;

UTL_FILE.putf( fo, '%s\n', varRow );

END LOOP;

CLOSE cur_output;

UTL_FILE.FCLOSE( fo );

END;

****************************************************