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"$name255SQLT_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    
        


HTML code for linking to this page:

Follow Navioo On Twitter

PHP Source Code

 Navioo Databases
» Oracle