Pl SQL reading text file from directory on database

Question

I have to read a text file that I have created on the database. I am not sure what the appropriate path would be for the UTL_FILE.FOPEN directory parameter. I am confused because the path is not on my local pc.

The path where I found this file is something like

\\{something here}\winixdb$\{schema here I think}\dev\data

This is my current code.

Declare

f UTL_FILE.FILE_TYPE;
s VARCHAR2(200);

begin

  f:= UTL_FILE.FOPEN({path_here}, 'certs_file.txt', 'R');
  UTL_FILE.GET_LINE(f,s);
  UTL_FILE.FCLOSE(f);
  dbms_output.put_line(s);

end;

Right now if I put the exact path there it will be an error:

ORA-2928: Invalid Directory Path

I've tried looking at other resources but they talk about a path that is found on your local environment. Also, I don't quite understand the idea of a file on your database.

I want to see this file be outputted to the DBMS.

Maybe the answer to this [question](http://stackoverflow.com/questions/2751113/utl-file-fopen-procedure-not-accepting-path-for-directory) will help you. `directory` is a database object you need to create (just like creating a table or an index). The location is on the db server machine, not on the client. — Glenn, Jul 13 '16 at 18:30
can I acces the location from using toad, navigating to schema, directory and finding it there? edit: I did that and put in the exact directory path but I still seem to get errors — chickenman, Jul 13 '16 at 18:35
I'm guessing that the path we're talking about is a UNC path and that the database is running on Windows (correct me if I'm wrong). Are you putting the full UNC path in the `fopen` call? Is that path in your `utl_file_dir` setting? Or did you create a directory object that points at that UNC path? Does the operating system user running the Oracle service have access to that UNC path? — Justin Cave, Jul 13 '16 at 19:22

J. Chomel · Accepted Answer · 2016-07-18T12:38:56.483

4

You must first declare the directory you want to access {path_here}:

CREATE OR REPLACE DIRECTORY  MY_PATH_ON_DBMS as '{path_here}';

(if it already exists, you should find it with select * from dba_directories;)

; make sure oracle user can read to it on the DBMS;

Then call it by its name in your code:

Declare
  f UTL_FILE.FILE_TYPE;
  s VARCHAR2(200);
begin
  f:= UTL_FILE.FOPEN('MY_PATH_ON_DBMS', 'certs_file.txt', 'R');
  UTL_FILE.GET_LINE(f,s);
  UTL_FILE.FCLOSE(f);
  dbms_output.put_line(s);
end;

edited Jul 18 '16 at 12:38

answered Jul 14 '16 at 07:09

J. Chomel

8,193
15
41
69

Should the oracle user have write permission too or is reading permission sufficient? – RayCW Jul 14 '16 at 07:34
The read only access should be enough. Thanks I updated this. – J. Chomel Jul 14 '16 at 07:39
Do I have to declare it? I am seeing some packages in other schemas in which it is not declared (or declared just like a variable). – chickenman Jul 18 '16 at 12:33
You declare it on the database, not in the package. Check if it already exists with `select * from dba_directories;` – J. Chomel Jul 18 '16 at 12:38
Yes. I see the name of the directory, V_COP, in there. If I create it in my plsql file I get an insufficient priveldges error but that makes no sense since I can navigate to the directory and read/write the files. Furthermore, I see people reading files without declaring the directory. – chickenman Jul 18 '16 at 13:50
What does `V_COP` stands for in your database? It should be `\\{something here}\winixdb$\{schema here I think}\dev\data` or `{schema here I think}\dev\data` or something? – J. Chomel Jul 18 '16 at 14:07
that is the name of the directory and it is reading it now. I didn't have to creap or declare anything though... so dunno but maybe your answer is still correct. I just wasn't capitalizing it. – chickenman Jul 18 '16 at 20:41

Pl SQL reading text file from directory on database

1 Answers1