Reading Text Document from PL/SQL stored procedure

Question

CREATE OR REPLACE PROCEDURE file_trial IS
  V1 VARCHAR2(32767);
  F1 UTL_FILE.FILE_TYPE;
BEGIN
  F1 := UTL_FILE.FOPEN('C:\TEMP','AVI','R',256);
  UTL_FILE.GET_LINE(F1,V1,32767);
 UTL_FILE.FCLOSE(F1);
END file_trial;

This code while executing gives

ORA 29280 : INVALID DIRECTORY PATH"

But file (avi.txt) present in temp folder

possible duplicate of [UTL_FILE.FOPEN() procedure not accepting path for directory?](http://stackoverflow.com/questions/2751113/utl-file-fopen-procedure-not-accepting-path-for-directory) — APC, Aug 20 '11 at 20:19

Klas Lindbäck · Accepted Answer · 2011-08-25T09:08:06.707

3

You need to create a DIRECTORY in Oracle

As SYS:

CREATE DIRECTORY MY_SYMBOLIC_NAME AS 'C:\TEMP';
GRANT READ,WRITE ON DIRECTORY oraload TO my_user;

You can now open files in the directory using the symbolic name and the file name (including file name extension):

F1 := UTL_FILE.FOPEN('MY_SYMBOLIC_NAME','AVI.TXT','R',256);

Note that a DIRECTORY in Oracle points to a file system directory accessible from the database server as pointed out by Sathya.

edited Aug 25 '11 at 09:08

answered Aug 19 '11 at 09:29

Klas Lindbäck

33,105
5
57
82

2

You should mention this will work only if the directory exists on the database server, not at the client end. – Sathyajith Bhat Aug 19 '11 at 09:55

Reading Text Document from PL/SQL stored procedure

1 Answers1