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.

Answer 1

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;