Working with BFILEs

Working with BFILEs : Oracle : Databases PHP Source Code

PHP Source Code » Databases » Oracle »

Working with BFILEs

A BFILE is an Oracle large object (LOB) datatype for files stored outside the database.
BFILEs are a handy way for using relatively static, externally created content. They are also
useful for loading text or binary data into Oracle tables.
In SQL and PL/SQL, a BFILE is accessed via a locator, which is simply a pointer to the
external file. There are numerous pre-supplied functions that operate on BFILE locators.
To show how BFILEs work in PHP this section creates a sample application that accesses
and displays a single image. The image will not be loaded into the database but the picture
description is loaded so it can be queried. The BFILE allows the image to be related to the
description. Also the application could be extended in future to use PL/SQL packages to read
and manipulate the image.
In this example, the image data is not loaded and printed in PHP. Instead, the browser is
redirected to the image URL of the external file. This significantly reduces the amount of data
that needs to be handled by the application.
To allow Apache to serve the image, edit httpd.conf and map a URL to the directory
containing the file. For example if the file is /tmp/cj.jpg add:

Alias /tmp/ "/tmp/"
<Directory "/tmp/">
Options None
AllowOverride None
Order allow,deny
Allow from all
</Directory>

Using /tmp like this is not recommended for anything except testing!
Restart Apache and use a browser to check that http://localhost/tmp/cj.jpg loads the
picture in /tmp/cj.jpg.
In Oracle, create a DIRECTORY alias for /tmp. This is Oracle’s pointer to the operating
system and forms part of each BFILE. The directory must be on the same machine that the
database server runs on. In SQL*Plus run:

	SQL> connect system/systempwd@//localhost/XE
	SQL> create directory TestDir AS '/tmp';
	SQL> grant read on directory TestDir to hr;
	SQL> connect hr/hrpwd@//localhost/XE
	SQL> create table FileTest (
				FileNum number primary key,
				FileDesc varchar2(30),
				Image bfile);

This gives the hr user access to the /tmp directory and creates a table FileTest containing a
file number identifier, a text description of the file, and the BFILE itself. The image data is
not loaded into this table; the BFILE in the table is a pointer to the file on your filesystem.
PHP code to insert the image name into the FileTest table looks like:


navioo_ bfilequery1.php

<?php



        $c = oci_connect("hr", "hrpwd", "//localhost/XE");

        $s = oci_parse($c,

        "select Image from FileTest where FileNum = :fnum");

        $fnum = 1;

        oci_bind_by_name($s, ":fnum", $fnum);

        oci_execute($s);

        $row = oci_fetch_assoc($s);

        $bf = $row['IMAGE']; // This is a BFILE descriptor

        echo "<pre>"; var_dump($bf); echo "</pre>";

?>





For simplicity, the query condition is the file number of the new record. In real life it might

use a regular expression on the FileDesc field like:

select Image from FileTest

where regexp_like(FileDesc, 'somepattern')

The PHP script returns a BFILE descriptor.

OCI-Lob