Use dbms_lob.fileopen to open bfile

  
SQL>
SQL> create table bfile_table(
  2        name varchar2(255),
  3        the_file bfile );

Table created.

SQL> insert into bfile_table values ( 'doc 1', bfilename( 'WROX_DIR', 'my_doc.pdf' ) );

1 row created.

SQL>
SQL>      declare
  2        l_bfile bfile;
  3        l_dir_alias varchar2(2000);
  4        l_filename varchar2(2000);
  5      begin
  6        select the_file
  7          into l_bfile
  8          from bfile_table
  9         where name = 'doc 1';
 10       dbms_lob.fileopen( l_bfile, dbms_lob.file_readonly );
 11       if dbms_lob.fileexists( l_bfile ) = 1 then
 12         dbms_lob.filegetname( l_bfile, l_dir_alias, l_filename );
 13         dbms_output.put_line( 'Directory alias: ' || l_dir_alias );
 14         dbms_output.put_line( 'Filename: ' || l_filename );
 15       end if;
 16       if dbms_lob.fileisopen( l_bfile ) = 1 then
 17         dbms_lob.fileclose( l_bfile );
 18       end if;
 19    end;
 20   /
     declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 10


SQL>
SQL> drop table bfile_table;

Table dropped.

SQL>
SQL>
SQL> --

1.	clob data type
2.	Use clob type in PL/SQL
3.	Use dbms_lob.erase to remove value from clob type value
4.	Creating an Internal LOB Table
5.	Copying Internal LOBs
6.	Appending and Writing to LOBs
7.	DBMS_LOB package.
8.	Print out the length of the CLOBs previously INSERTed
9.	Compare clob data
10.	Compare two clobs
11.	Append data to clob
12.	Assign string value to clob type variable
13.	Clob type column
14.	Copy clob data
15.	In() function with clob data
16.	Initialize clob
17.	Read clob type data, DBMS_LOB.READ
18.	Use between ... and with clob data
19.	Use clob to store xml data
20.	clob type data as string
21.	erase clob data
22.	use like operator with clob type data

Use dbms_lob.fileopen to open bfile : Clob « Data Type « Oracle PL / SQL

Related examples in the same category