Sorry, but I've decided to be totally blunt with when others from now on....


I hate your code:

exception
when OTHERS then
dbms_output.put_line('Error : ' || SQLERRM );
end; 

stop doing that right now, that is the worst programming practice on the planet, there is no reason for it, none.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1270688200346183091

As for the problem at hand here, let's see

we don't know what directory you are trying to read
we don't know what directory you created
we don't know if that directory exists on the SERVER (it has too)



So, what did your create directory look like? The CASE is important... Identifiers are folded to upper case. Maybe it looks like this:

ops$tkyte%ORA9IR2> create or replace directory my_dir as '/tmp'
  2  /

Directory created.

so the directory is really MY_DIR, not my_dir...


so, if you have a procedure like:

ops$tkyte%ORA9IR2> create or replace procedure readfile(FilePath in varchar2,FileName in varchar2)
  2  as
  3          l_bfile    BFILE;
  4  begin
  5          l_bfile := BFILENAME( FilePath, FileName);
  6          dbms_lob.fileopen(l_bfile );
  7  end;
  8  /

Procedure created.

and a file you know exists IN THAT DIRECTORY ON THAT SERVER - I am no sqlnet'ed in - I'm on the server itself:

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> !touch /tmp/test.dat

you would expect this:

ops$tkyte%ORA9IR2> exec readFile( 'my_dir', 'test.dat' );
BEGIN readFile( 'my_dir', 'test.dat' ); END;

*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "OPS$TKYTE.READFILE", line 6
ORA-06512: at line 1

if you used lower case and this:

ops$tkyte%ORA9IR2> exec readFile( 'MY_DIR', 'test.dat' );

PL/SQL procedure successfully completed.

otherwise (but hey, if you leave that really bad WHEN OTHERS in there, you might just see "PL/SQL procedure successfully completed." for both!!!! so stop it)


So, work through a more simple example - and stop the when others, pretend the word OTHERS does not exist in plsql.