Get the filename from the descriptor BFILE PL/SQL procedure

Get the filename from the descriptor BFILE PL/SQL procedure : Oracle : Databases PHP Source Code

PHP Source Code » Databases » Oracle »

Get the filename from the descriptor BFILE PL/SQL procedure

The application can be improved by putting the PL/SQL into a stored procedure. This lets
Oracle parse and optimize the code at creation time.
To create a PL/SQL procedure MyGetFileName() in SQL*Plus:

		SQL> connect hr/hrpwd@//localhost/XE
		SQL> create or replace procedure MyGetFileName
				(fnum_p in number, name_p out varchar2) as
				   bf_l bfile;
				   da_l varchar2(255);
				begin
				   select image into bf_l from FileTest where FileNum = fnum_p;
				   dbms_lob.filegetname(bf_l, da_l, name_p);
				end;
		/

Back in PHP the filename is easily fetched from the database with an anonymous block. The
browser is redirected to the image similar to the previous example:


navioo_getpic.php

<?php

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

    $s = oci_parse($c, "begin MyGetFileName(:fnum, :name); end;");

    $fnum = 1;

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

    oci_bind_by_name($s, ":name", $name, 255, SQLT_CHR);

    oci_execute($s);

    header("Location: http://localhost/tmp/$name");

?>

BFILEs are easy to work with in PL/SQL because the presupplied DBMS_LOB package has a

number of useful functions. For example DBMS_LOB.LOADFROMFILE() reads BFILE data

from the file system into a PL/SQL BLOB or CLOB. This could be loaded into a BLOB table

column, manipulated in PL/SQL, or even returned to PHP using OCI8&#8217;s LOB features.

Another example is DBMS_LOB.FILEEXISTS(), which can be used to check whether the

FileTest table contains references to images that do not exist.

BFILEs are very useful for many purposes including loading images into the database,

but BLOBs may be better in some circumstances. Changes to BFILE locators can be rolled

back or committed but since the files themselves are outside the database, BFILE data does

not participate in transactions. You can have dangling references to BFILEs because Oracle

does not check the validity of BFILEs until the data is explicitly read (this allows you to precreate

BFILEs or to change the physical data on disk). BFILE data files are read-only and

cannot be changed within Oracle. Finally, BFILEs need to be backed up manually. Because

of these points, there might be times you should use BLOBs to store images inside the

database to ensure data and application consistency but BFILEs are there if you want them.

OCI-Lob