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 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
– Glenn, Commented Jul 13, 2016 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
– chickenman, Commented Jul 13, 2016 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
– Justin Cave, Commented Jul 13, 2016 at 19:22

J. Chomel · Accepted Answer · 2016-07-18 12:38:56Z

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, 2016 at 12:38

answered Jul 14, 2016 at 7:09

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

RayCW Over a year ago

Should the oracle user have write permission too or is reading permission sufficient?

J. Chomel Over a year ago

The read only access should be enough. Thanks I updated this.

chickenman Over a year ago

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).

J. Chomel Over a year ago

You declare it on the database, not in the package. Check if it already exists with select * from dba_directories;

chickenman Over a year ago

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.

|

Collectives™ on Stack Overflow

Pl SQL reading text file from directory on database

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related