ORA-22288 when DBMS_LOB.FILEOPEN even if server can see the file

From: <thtsang_yh_at_yahoo.com.hk>
Date: 2 Jun 2005 08:45:02 -0700
Message-ID: <1117727102.165809.258880@g47g2000cwa.googlegroups.com>

I have created a BFILE. Most DBMS_LOB functions works. However, FILEOPEN does not work. Any solution?

Same output from SQLPLUS:
SQL> desc x

 Name                    Null?    Type
 ----------------------- -------- ----------------
 Y                                BINARY FILE LOB

(Table x has a BFILE column Y)

  1 declare
  2 xx bfile;

  3  a varchar2(100);
  4  b varchar2(100);
  5  c varchar2(100);

  6 begin
  7 select y into xx from x;
  8 dbms_lob.filegetname(xx,c,b);
  9 select directory_path into a
 10 from all_directories

 11  where directory_name=c;
 12  dbms_output.put_line('Directory: ' || c);
 13  dbms_output.put_line('Full Name: ' || a || '/' || b);
 14  dbms_output.put_line('File Length: ' || dbms_lob.getlength(xx));
 15  dbms_output.put_line('File Exists? ' || dbms_lob.fileexists(xx));

 16 dbms_lob.fileclose(xx);
 17 dbms_lob.fileopen(xx);
 18* end;
SQL> /
Directory: XMLDIR
Full Name: /opt/oracle/ora_dir/xml_dir/glossary.xml File Length: 28420
File Exists? 1
declare
*
ERROR at line 1:

ORA-22288: file or LOB operation FILEOPEN failed
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 17

SQL> !ls -la /opt/oracle/ora_dir/xml_dir/ Total 36

drwxrwxrwx  2 oracle oradba  4096 2005-06-02 23:26 .
drwxr-xr-x  6 oracle oradba  4096 2005-05-31 23:07 ..
-rwxrwxrwx  2 oracle oradba 28420 2002-08-01 09:20 glossary.xml

SQL> grant all on directory xmldir to system   2 /
grant all on directory xmldir to system

                                 *

ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

(Observations:

1. BFILE exists. Oracle is able to detect the file and find its size. It can even close the file but cannot open it. 2. The physical file exists, owned by oracle. Both the file and the directory is world accessible.
3. The code was run by a DBA (system) who is also the owner of directory
4. Tried restart the db, no help
)

Any idea?

Oracle version: Oracle 10g 10.1.0.2.0 on Linux Received on Thu Jun 02 2005 - 10:45:02 CDT