DBMS_LOB.FileOpen Error ORA-22285 in case

[arthae]

I have the following:

Select all

 
  FUNCTION get_local_ascii_data(p_dir  IN VARCHAR2 
                               ,p_file IN VARCHAR2) RETURN CLOB IS 
    l_bfile BFILE; 
    l_data CLOB; 
  BEGIN 
    dbms_lob.createtemporary(lob_loc => l_data, cache => TRUE, dur => dbms_lob.call); 
 
    l_bfile := bfilename(p_dir, p_file); 
    dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly); 
 
    IF dbms_lob.getlength(l_bfile) > 0 THEN 
      dbms_lob.loadfromfile(l_data, l_bfile, dbms_lob.getlength(l_bfile)); 
    END IF; 
 
    dbms_lob.fileclose(l_bfile); 
 
    RETURN l_data; 
  END; 

The function returns a clob from the file that I am passing in parameter.
The function is being called as follows:

Select all

 
DECLARE 
  i CLOB; 
  l_bfile BFILE; 
  l_data CLOB; 
BEGIN 
 
  i := get_local_ascii_data(p_dir => 'DIR_TESTE', 
                                       p_file => 'teste_directory.txt'); 
 
END; 

Where dir_Teste is a Directory created in Oracle.
The question is as follows, when I perform the original call it is returning "ORA-22285: NON-Existent Directory or File for FileOpen Operation".
But if I run as follows in a SQL Window it works perfectly!

Select all

 
DECLARE 
  p_dir VARCHAR2(100) := 'DIR_TESTE'; 
  p_file VARCHAR2(100) := 'teste_directory.txt'; 
  l_bfile BFILE; 
  l_data CLOB; 
BEGIN 
  dbms_lob.createtemporary(lob_loc => l_data, cache => TRUE, dur => dbms_lob.call); 
   
  l_bfile := bfilename(p_dir , p_file); 
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly); 
 
  IF dbms_lob.getlength(l_bfile) > 0 THEN 
    dbms_lob.loadfromfile(l_data, l_bfile, dbms_lob.getlength(l_bfile)); 
  END IF; 
 
  dbms_lob.fileclose(l_bfile); 
 
END; 

I already checked if it was Grants or if there really is Directory on the bench, and it's not either!

Does anyone have any tips?

[dr_gori]

You quoted SQL Window, I imagine you're using PLSQL Developer.
Good, when this kind of crazy error happens, I usually run the programs in SQL * Plus, because you never know if PLSQL Developer is with some bug or even if it's some problem related to Oracle customer.

Try this right on the base, just to test.
Just to confirm, are you using the same user to do everything right?

[arthae]

Sorry for the delay to answer, is that the problem fell into oblivion.

I did the same thing in SQL * Plus and the same problem happened.
I believe being base / server configuration I'm just not finding where it is.

[arthae]

They made a copy of a production bank to another test server.
This problem is occurring only that now I can debug.
I verified that bfilename() is not finding the file when calling within the function.

[arthae]

I found the problem, it's really grant in the production base.
What was happening is that in the approval base, when created the Development User Directory the Bank assigns Grants to the user who was creating and SYS.
But in production everything is deploying with the SYS user. Soon he did not liberate Automatic Grants.

I hit to find the problem because the deployment is made equal to what is in the tests, then as we performed the same command I thought would be another problem.
But anyway, thank you: