Intermittent ORA-22288 error - command length incorrect

Question

I have an intermittent problem with a web application that frequently has to open a file located on the database server as part of regular operations. For the vast majority of the time, this functions without issue, however, at seemingly random times, the corresponding requests start returning HTTP 400 errors. Bouncing the apache server addresses the issue for a time, but it invariably returns in a day or at most a week.

I added some logging to the relevant pl/sql code (no, don't run away! come back!), which I've listed below for reference:

declare
  bl_blob blob;
  bf_file bfile := bfilename(<directory that totally exists>, <file that totally exists>);
begin
  dbms_lob.createTemporary(bl_blob, true);
  dbms_lob.open(bf_file, dbms_lob.lob_readonly);
  dbms_lob.open(bl_blob, dbms_lob.lob_readwrite);
  dbms_lob.loadfromfile(bl_blob, bf_file, dbms_lob.getLength(bf_file));
  dbms_lob.close(bf_file);
  return bl_blob;
end;

It turns out the 400 errors correspond to the following ORA-22288 error:

file or LOB operation FILEOPEN failed
The program issued a command but the command length is incorrect

My question is, why would the operation suddenly, and repeatedly, start erroring out where previously the same file could be opened without issue? The underlying file is never changed and only ever opened programmatically with read only permissions.

All the forum digging I've done so far has yielded mostly a slew of "turn it off and on" solutions, which...yeah.

Any help is greatly appreciated.

Answer 1

I'm not sure if this is exactly the code you're running, or if you've simplified it for SO. Your bfile is basically a pointer, so I'm not sure what happens if the file is modified on disk after you establish the pointer. I'm also not sure if anonymous blocks like this get parsed and cached in the same way as SQL statements. Long story short, try this:

declare
  bl_blob blob;
  bf_file bfile;
begin
  bf_file := bfilename(<directory that totally exists>, <file that totally exists>);
  dbms_lob.createTemporary(bl_blob, true);
  dbms_lob.open(bf_file, dbms_lob.lob_readonly);
  dbms_lob.open(bl_blob, dbms_lob.lob_readwrite);
  dbms_lob.loadfromfile(bl_blob, bf_file, dbms_lob.getLength(bf_file));
  dbms_lob.close(bf_file);
  return bl_blob;
end;