Working with LOBs in Oracle and PHP

Original link: http://www.oracle.com/technetwork/articles/fuecks-lobs-095315.html

Working with LOBs in Oracle and PHP by Harry Fuecks Hitting the 4,000-byte limit? Enter lobs ... Downloads for this article: Oracle DatabaseG Zend Core for Oracle Apache HTTP Server 1.3 and later Working with Oracle types like VARCHAR2 are fine, but "what if" need to being able to store + than its 4,000-byte limit I n one go? For this task, you need one of the Oracle's Long Object (LOB) types, which in turn requires so you learn how to use the PHP API for working with LOBs. That's in itself can is daunting for those unfamiliar with it. In this "oracle+php Cookbook" HowTo, you'll learn the available LOB types and issues related to them, then explore Examp Les of common LOB operations in PHP. Long Objects in Oracle Oracle provides the following LOB types: BLOB, used to store binary data CLOB, used to store character data using the database character set encoding NCLOB, used to store Unicode character data using the national character set. Note that Nclobs is, currently, not supported by the PHP OCI8 extension, and which you'll be using this here. BFILE, used to reference external files under the operating system ' s filesystem A further subcategory of LOB is the temporary LOB, which can being either a BLOB, CLOB, or NCLOB but was stored in the Tempora Ry Tablespace until you free it. Note that older versions of Oracle provided the long and long RAW types for character and binary data, respectively. With Oracle9ithese were deprecated in favor of lobs. LOB storage. For the BLOB, CLOB, and NCLOB types, Oracle Database are capable of storing up to 128TB in a single value, DEP Ending on your database block size and the "chunk" setting, defined for the LOB. A lob itself comprises the elements:the lob content and the LOB locator, which is a "pointer" to the LOB content. This separation are required to allow Oracle to store and manage lobs efficiently and it's reflected in the PHP APIs u SE to inserts, UPDATE, andSELECT lobs (see below). For the internal LOB types (i.e. not bfiles) Oracle would store the content of the LOB "In-line" in the table, with the Res T of the row, if the size of the LOB is less than 4KB. LOBs larger than 4KB is stored "out-of-line," by default in the table ' s tablespace. This approach allows small lobs to being retrieved quickly while, for large lobs, access times would be slower but overall per Formance, when scanning the table, is preserved. There is further options for lobs storage and Access-such as memory caching and Buffering-which may improve performance, D Epending on the specifics of your application. For further information see the LOB performance guidelines and the Oracle Database application Developer ' s Guide-large O Bjects in the Oracle documentation. restrictions on lobs. A number of restrictions apply to the use of LOB types, the most important being their use in SQL statements. You cannot use a LOB type with any of the following queries. SELECT DISTINCT <lob_type>order by <lob_type>group by <lob_col> It is also illegal-a LOB type column for table joins,UNION,intersection, andminusStatements. Further restrictions apply to other aspects of the use of lobs, such as you cannot use LOB as a primary key column. Again, seeoracle Database application Developer ' s Guide-large Objects for details. CLOBs and Character Sets The default character set for your database was defined by the parameter Nls_characterset and text placed in a CLOB is EXPE CTED to is encoded using this character set. Use the this SQL to determine your databases character set encoding: SELECT value from nls_database_parameters WHERE parameter = ' Nls_characterset ' Given lack of want to consider using Unicode encoding as the database character, such a-nclobs in PHP S UTF-8, which can be done (given sufficient privileges) using the statement: ALTER DATABASE CHARACTER SET UTF8 Note:do not attempt this without understanding the impact, especially if you have existing data or application code using A different character set. See the Oracle Globalization Support Guide and a overview on globalizing Oracle PHP applications for more information. Working with LOBs The discussion here's focus on PHP ' s OCI8 extension. It ' s also worth noting that Oracle provides the DBMS_LOB package, containing parallel procedures and functions for working with lobs using PL/SQL. The PHP OCI8 extension registers a PHP class called "Oci-lob" in the global PHP namespace. When you execute a SELECTstatement, for example, where one of the columns is a LOB type, PHP would bind this automatic Ally to an Oci-lob object instance. Once you has a reference to the Oci-lob object, you can then call methods like load () and Save () to Acce SS or modify the contents of the LOB. The available Oci-lob methods would depend on your PHP version, PHP5 in particular have gained methods like read () , Seek () , and append () . The PHP Manual is a little unclear, in this case, on the version numbers so if in doubt you can verify using the Following script. <?phpforeach (Get_class_methods (' Oci-lob ') as $method) { print "Oci-lob:: $method () \ n";}? > On my system, running PHP 5.0.5, I get the following list of methods: Oci-lob::load () Oci-lob::tell () oci-lob::truncate () oci-lob::erase () Oci-lob::flush () oci-lob::setbuffering () Oci-lob::getbuffering () Oci-lob::rewind () Oci-lob::read () oci-lob::eof () Oci-lob::seek () Oci-lob::write () OCI-Lob:: Append () oci-lob::size () Oci-lob::writetofile () oci-lob::writetemporary () Oci-lob::close () Oci-lob::save () OCI-Lob:: SaveFile () Oci-lob::free () In practice, the PHP 4.x OCI8 extension supports reading or writing of all lobs only, which are the most common use CA Se in a Web application. PHP5 extends this to allow reading and writing of ' chunks ' of a lob as well as supporting LOB buffering with the methodssetbuffering ()andgetbuffering (). PHP5 also provides the stand-alone functionsoci_lob_is_equal ()andoci_lob_copy (). The examples here would use the new PHP5 OCI function names (e.g. oci_parse instead of ociparse ). Examples use the following sequence and table: CREATE SEQUENCE mylobs_id_seq nominvalue nomaxvalue nocycle CACHE noorderincrement by 1; CREATE TABLE mylobs ( ID number PRIMARY KEY, mylob CLOB) Note the most of the examples here use clobs but the same logic can is applied almost exactly to BLOBs as well. Inserting a LOB To INSERT an internal lob, your first need to initialize the LOB using the respective Oracle empty_blob o R Empty_clob functions-you cannot update a LOB that contains a NULL value. Once initialized, you then bind the column to a PHP Oci-lob object and update the Lob content via the object ' s Save () /c3> method. The following script provides an example, returning the LOB type from the INSERT query: <?php//connect to DB etc ... $sql = "INSERT into Mylobs (ID, Mylob) VALUES ( Mylobs_id_seq. Nextval,--initialize as an empty CLOB Empty_clob ()) Returning--return the LOB locator Mylob Into:mylob_loc "; $stmt = Oci_parse ($conn, $sql);//Creates an ' empty ' Oci-lob object to bind T o The Locator$mylob = Oci_new_descriptor ($conn, Oci_d_lob);//Bind The returned Oracle LOB locator to the PHP lob Objectoc I_bind_by_name ($stmt, ": Mylob_loc", $myLOB,-1, Oci_b_clob);//Execute the statement using, Oci_default-as a transactio Noci_execute ($stmt, Oci_default) or die ("Unable to execute query\n"); Now save a value to the LOBIF (! $myLOB->save (' INSERT: '. Date (' H:i:s ', Time ()))} {//On error, rollback the Transaction Oci_rollback ($conn); } else {//On success, commit the transaction oci_commit ($conn); }//Free Resourcesoci_free_statEment ($stmt); $myLOB->free ();//disconnect from DB etc.? > Notice How this example uses a transaction, instructingOci_executeWithOci_defaultConstant to wait for anOci_commitor anOci_rollback. This is important as I has a stages taking place in theINSERT-first Create the row and second update the LOB. Note that if I am working with a BLOB type, the needed (assuming a BLOB column) are to the Oci_bind_by_nam E Call: Oci_bind_by_name ($stmt, ": Mylob_loc", $myLOB,-1, Oci_b_blob); Alternatively, you can bind a string directly without specifying a LOB type; <?php//etc. $sql = "INSERT into mylobs ( ID, mylob ) VALUES ( mylobs_id_seq. Nextval, : string ) "; $stmt = Oci_parse ($conn, $sql); $string = ' INSERT: '. Date (' H:i:s ', Time ()); oci_bind_by_ Name ($stmt, ': String ', $string); Oci_execute ($stmt) or Die ("Unable to execute query\n");/etc.? > This approach simplifies the code significantly and was suitable when the data your want to write to the LOB is relatively Small. In contrast, if you wished to stream the contents of the large file into a LOB, you might loop through the contents of the File CallingWrite ()andFlush ()The PHP LOB object to write smaller chunks rather than has the entire file held in memory at a single instance. Selecting a LOB When a SELECT query contains a LOB column, PHP would automatically bind the column to an Oci-lob object. For example: <?php//etc. $sql = "Select * from mylobs ORDER by Id"; $stmt = Oci_parse ($conn, $sql); oci_ Execute ($stmt) or Die ("Unable to execute query\n"), while ($row = Oci_fetch_assoc ($stmt)) { print "ID: {$row [' ID ']}, "; Call the Load () method to get the contents of the LOB print $row [' Mylob ']->load (). " \ n ";} etc.? > This can is further simplified using theOci_return_lobsConstant, used withOci_fetch_array (), instructing it to replace LOB objects with their values: while ($row = Oci_fetch_array ($stmt, oci_assoc+oci_return_lobs)) { print ' ID: {$row [' id ']}, {$row [' mylob ']}\n ';} Updating a LOB to  UPDATE  a LOB, it ' s also possible to use THE&NBSP; "returning"  command in the SQL, a s with the above  INSERT  example, but a simpler approach is to  SELECT ... For UPDATE : <?php//etc. $sql = "Select mylob from mylobs WHERE id = 3 for UPDATE/* Locks the row * *"; $stm t = Oci_parse ($conn, $sql);//Execute the statement using Oci_default (begin a transaction) Oci_execute ($stmt, Oci_default) or Die ("Unable to execute query\n");//Fetch the SELECTed rowif (FALSE = = = ($row = OCI_FETCH_ASSOC ($stmt))) { oci_rollback ($conn); Die ("Unable to fetch row\n");} Discard the existing LOB contentsif (! $row [' Mylob ']->truncate ()) { oci_rollback ($conn); Die ("Failed to truncate lob\n");} Now save a value to the LOBIF (! $row [' Mylob ']->save (' UPDATE: '. Date (' H:i:s ', Time ()) ')} { //On Error, ROLLBAC K The transaction Oci_rollback ($conn); } else { //On success, commit the transaction Oci_commit ($ conn); } Free resourcesoci_free_statement ($stmt); $row [' Mylob ']->free ();/etc.? > As with theINSERT, I need to perform theUPDATEUsing a transaction. An important additional step is thetruncate (). When updating a lobs withSave (), it would replace the contents of the LOB beginning from the "Start up" to the length of the new data. That means older content (if it is longer than the new content) may still being left in the LOB. For PHP 4.x, where truncate () is unavailable, the following alternative solution uses Oracle ' s empty_clob () function to erase any existing contents in the LOB before saving new data to it. $sql = "UPDATE mylobs SET mylob = Empty_clob () WHERE id = 2403 returning Mylob Into:mylob "; $stmt = Ociparse ($conn, $sql); $mylob = Ocinewdescriptor ($conn, Oci_d_lob); Ocibindbyname ($stmt, ': Mylob ', $mylob,-1, OCI_B_CLOB);//Execute the statement using Oci_default (begin a transaction) Ociexecute ($stmt, Oci_default) or Die ("U Nable to execute query\n "); if (! $mylob->save (' UPDATE: '. Date (' H:i:s ', Time ()))} { ocirollback ($conn); Die ("Unable to update lob\n"); } Ocicommit ($conn); $mylob->free (); ocifreestatement ($stmt); Working with Bfiles When using the BFILE type, INSERTs and UPDATEs mean telling Oracle where the file is located within the FileSystem of the database server (which may is not being the same machine as the WEB server), rather than passing the file cont Ent. Using a SELECT statement, you can read the contents of the BFILE through Oracle, should your so desire, or call Fu Nctions and procedures from the Dbms_lobpackage to get information about the file. The main advantage of Bfiles is being able to access the original files directly from the filesystem while still being ABL E to locate files using SQL. This means, for example, images can is served directly by the WEB server while I can keep track of the relationship Betwee n the table containing the bfiles and, say, a "users" table, telling me who uploaded the files. As an example, I first need to update the table schema used above; ALTER TABLE mylobs ADD (mybfile BFILE) Next I need to register a directory alias with Oracle (this requires administrative privileges) and grant permissions to R EAD it: CREATE directory Images_dir as '/home/harryf/public_html/images ' GRANT READ on DIRECTORY Images_dir to Scott I can nowINSERTSome BFILE names like: <?php//etc.//Build An insert for the BFILE names$sql = "INSERT into Mylobs (ID, Mybfile) VALUES (mylobs_id_seq. Nextval,/* Pass the file name using the Oracle directory reference I created called IMAG Es_dir */bfilename (' Images_dir ',: filename)) "; $stmt = Oci_parse ($conn, $sql);//Open the D Irectory$dir = '/home/harryf/public_html/images '; $dh = Opendir ($dir) or Die ("Unable to open $dir");//Loop through the Contents of the Directorywhile (false!== ($entry = Readdir ($DH))) {//Match only files with the extension. jpg ,. gif or. png if (Is_file ($dir. '/'. $entry) && preg_match ('/\. ( jpg|gif|png) ($/', $entry)) {//Bind the filename of the statement oci_bind_by_name ($stmt, ": filename ", $entry); Execute the statement if (Oci_execute ($stmt)) {print "$entry added\n"; } } } If I need to, I can read the BFILE content through Oracle using the same approach as above where I selected CLOBs. Alternatively, if I need to get the filenames back so I can access them directly from the filesystem, I can call theDbms_lob. FilegetnameProcedure like: <?php//etc. $sql = "SELECT id from mylobs WHERE – select only Bfiles which AR E NOT NULL Mybfile was NOT NULL, $stmt 1 = oci_parse ($conn, $sql), Oci_execute ($stmt 1) or Die ("unable to execute Query\n "); $sql =" DECLARE locator BFILE; Diralias VARCHAR2 (30); FileName VARCHAR2 (30); BEGIN SELECT mybfile to locator from Mylobs WHERE id =: ID; --Get The filename from the BFILE dbms_lob. Filegetname (Locator, Diralias, filename); --Assign out params to bind parameters:d Iralias:=diralias; : Filename:=filename; END; "; $stmt 2 = Oci_parse ($conn, $sql), while ($row = Oci_fetch_assoc ($stmt 1)) {Oci_bind_by_name ($stmt 2, ": id", $row [' I D ']); Oci_bind_by_name ($stmt 2, ":d Iralias", $diralias, 30); Oci_bind_by_name ($stmt 2, ": FileName", $filename,30); Oci_execute ($stmt 2); Print "{$row [' ID ']}: $diralias/$filename \ n"; }//etc.? > Furthermore, you can use theDbms_lob. FileExistsfunction to discover which files has been deleted via the operating system but is still referenced in the database. Conclusion In this HowTo you has been introduced to the different types of lobs available in Oracle Databaseg and Hopefu Lly now understand their role in allowing large data entities to being stored efficiently in the database. You have also learned how to work with LOBs using PHP's OCI8 API, covering the common use cases you'll encounter while D Eveloping with Oracle and PHP.

Working with LOBs in Oracle and PHP