/*
 * find_bfilename.sql
 * Appendix I, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script demonstrates how to query a BFILENAME column value
 * for a physical file name.
 */

-- Unremark for debugging script.
SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 49999
SET SERVEROUTPUT ON SIZE 1000000

CREATE OR REPLACE FUNCTION find_bfilename
( president_id_in IN     NUMBER )
RETURN VARCHAR2 IS

  -- Define a locator.
  locator           BFILE;

  -- Define alias and file name.
  alias             VARCHAR2(255);
  file_name         VARCHAR2(255);

  -- Define a local exception for size violation.
  directory_num EXCEPTION;
  PRAGMA EXCEPTION_INIT(directory_num,-22285);  

BEGIN

  -- A FOR UPDATE makes this a DML transaction.
  FOR i IN (SELECT    photograph
            FROM      president
            WHERE     president_id = president_id_in) LOOP          
    locator := i.photograph;
  END LOOP;

  -- Check for available locator.
  IF locator IS NOT NULL THEN     
    dbms_lob.filegetname(locator,alias,file_name);
  END IF;

  -- Return file name.
  RETURN file_name;

EXCEPTION
  WHEN directory_num THEN
  RETURN NULL;

END find_bfilename;
/
