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

I've eventually found out the cause: Oracle does not support hard link!
(Is this a bug or work as expected?) When I remove the hard link and
make a copy of the file, the procedure works!

This is the first time hard link works differently as normal file in my
linux history... Dare not test what happen if symbolic link is used :P