Retrieve the LOB locater : BFILE « Large Objects « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Large Objects
3. BFILE

SQL>
SQL> CREATE TABLE waterfalls (
  2     falls_name VARCHAR2(80),
  3     falls_photo BLOB,
  4     falls_directions CLOB,
  5     falls_description NCLOB,
  6     falls_web_page BFILE);

Table created.

SQL>
SQL> CREATE DIRECTORY bfile_data AS 'c:\xxx';

Directory created.

SQL>
SQL> DECLARE
  2     web_page BFILE;
  3     html RAW(60);
  4     amount BINARY_INTEGER := 60;
  5     offset INTEGER := 1;
  6  BEGIN
  7
  8     SELECT falls_web_page
  9       INTO web_page
 10       FROM waterfalls
 11      WHERE falls_name='Tannery Falls';
 12
 13     DBMS_LOB.OPEN(web_page);
 14     DBMS_LOB.READ(web_page, amount, offset, html);
 15     DBMS_LOB.CLOSE(web_page);
 16
 17     DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));
 18
 19     DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
 20  END;
 21  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8


SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table waterfalls;

Table dropped.

SQL> drop directory bfile_data;

Directory dropped.

SQL>