How PHP manages Oracle LOB data

We all knowVARCHAR2 is also recommended by Oracle. However, VARCHAR2 has a problem: a maximum of 4000 characters can be entered, which is equivalent to 2000 Chinese characters. If the value of a character in your program is greater than 20002 Chinese characters, VARCHAR2 cannot meet the requirements. At this time, you have two options: one is to use multiple VARCHAR2, and the other is to use the LOB field. Here we will look at the second method.

Let's take a general look at the LOB field of Oracle. Oracle LOB types include BLOB, CLOB, and BFILE. CLOB is called the character LOB, and BLOB and BFILE are used to store binary data. The maximum length of CLOB and BLOB is 4 GB. they store the values in the Oracle database. BFILE is similar to BLOB, But it stores data in an External file, so it is also called External BLOB ).

I think we should be familiar with MYSQL. MYSQL also has similar data types, such as TEXT and BLOB. In MYSQL functions of PHP, operations on TEXT/BLOB are direct, just like other types of data. But in Oracle, the situation is different. Oracle treats LOB as a special data type and does not use conventional methods in operations. For example, you cannot INSERT values directly into the LOB field in the INSERT statement, or use LIKE for search.

The following describes how to use PHP's OCI function to insert, retrieve, and query Oracle LOB data through several examples of php lob data management.

Insert

Values cannot be directly inserted into the LOB field using the INSERT statement. Generally, there are the following steps:

1. analyze an INSERT statement and return a LOB descriptor.

2. Use the OCI function to generate a local LOB object

3. Bind the LOB object to the LOB Descriptor

4. Execute the INSERT statement

5. assign a value to the LOB object

6. Release LOB objects and SQL statement handles

The following example of using PHP to manage Oracle LOB data is to store user-uploaded image files in BLOB (or BFILE with slightly different operations. First, create a table with the following structure:

 
 

  
  
CREATE TABLE PICTURES (  
  
  
ID NUMBER,  
  
  
DESCRIPTION VARCHAR2(100),  
  
  
MIME VARCHAR2(128),  
  
  
PICTURE BLOB  
  
  
); 
 
 

To automatically add IDs, create a SEQUENCE:

Create sequence PIC_SEQ;

Then the PHP code is used to process data.

 
 

  
  
<? Php
  
  
// Establish an Oracle database connection
  
  
$ Conn = OCILogon ($ user, $ password, $ SID );
  
  
// Submit the SQL statement to Oracle
  
  
// Note the following two points: first, use the EMPTY_BLOB () function. This is an internal Oracle function,
Returns a LOB identifier. When inserting a LOB, you can only use this method to generate an empty LOB
Operator, and then perform operations on the operator. EMPTY_BLOB () function is for blob type,
The CLOB corresponds to EMPTY_CLOB (). The second is the part after RETURNING.
Php oci function.
  
  
$ Stmt = OCIParse ($ conn, "insert into pictures (id, description, picture)
  
  
VALUES (pic_seq.NEXTVAL, '$ description',' $ lob_upload_type'
, EMPTY_BLOB () RETURNING picture into icture ");
  
  
// Generate the descriptor of a local LOB object. Note the second parameter of the function: OCI_D_LOB,
Generate a LOB object. Other possibilities include OCI_D_FILE and OCI_D_ROWID,
It corresponds to BFILE and ROWID respectively.
  
  
$ Lob = OCINewDescriptor ($ conn, OCI_D_LOB );
  
  
// Bind the generated LOB object to the locator returned by the preceding SQL statement.
  
  
OCIBindByName ($ stmt, ': PICTURE', & $ lob,-1, OCI_ B _BLOB );
  
  
OCIExecute ($ stmt );
  
  
// Store data into the LOB object. Because the source data here is a file, you can directly use LOB
The savefile () method of the object. Other LOB object methods include save () and load (),
It is used to save and retrieve data respectively. However, the BFILE type has only one method: save ()
  
  
If ($ lob-> savefile ($ lob_upload )){
  
  
OCICommit ($ conn );
  
  
Echo "upload successful <br> ";
  
  
} Else {
  
  
Echo "Upload Failed <br> ";
  
  
}
  
  
// Release the LOB object
  
  
OCIFreeDesc ($ lob );
  
  
OCIFreeStatement ($ stmt );
  
  
OCILogoff ($ conn );
  
  
?>
 
 

Note that the value of the LOB field must contain at least one character. Therefore, before saving () or savefile (), make sure that the value cannot be blank. Otherwise, an error occurs in Oracle.

Retrieve

PHP manages Oracle LOB data to retrieve data from a LOB. There are two methods. First, generate a LOB object, bind it to the locator returned by a SELECT statement, and then use the load () method of the LOB object to retrieve data; the second is to directly use the OCIFetch *** function of PHP. The first method is much more troublesome than the second method, so I will talk about the second method directly.

Or use the following table.

 
 

  
  
<? Php
  
  
$ Conn = OCILogon ($ user, $ password, $ SID );
  
  
$ Stmt = OCIParse ($ conn, "SELECT *
From pictures where id = $ pictureid ");
  
  
OCIExecute ($ stmt );
  
  
// The secret is on the third parameter of PCIFetchInfo:
OCI_RETURN_LOBS. The third parameter is the FETCH mode,
If OCI_RETURN_LOBS, put the value of the LOB directly to the end.
If the LOB operator is not in the array, the load () method of the LOB object is not used.
  
  
If (OCIFetchInto ($ stmt, $ result, OCI_ASSOC + OCI_RETURN_LOBS ))
  
  
{
  
  
Echo "Content-type:". StripSlashes ($ result [MIME]);
  
  
Echo StripSlashes ($ result [PICTURE]);
  
  
}
  
  
OCIFreeStatement ($ stmt );
  
  
OCILogoff ($ conn );
  
  
?>
 
 

This program is used to display data (images) placed in LOB ). Call method (assuming the Script Name Is getpicture. php ):

 
 

  
  
ALT = "Images placed in Oracle LOB">
 
 

Query

As mentioned above, the LOB field in Oracle cannot be matched with LIKE when PHP manages Oracle LOB data. What should we do? In fact, it is not complicated. Oracle has an anonymous package called DBMS_LOB, which contains some processes required to operate LOB.

Suppose there is a table like this:

 
 

  
  
CREATE TABLE ARTICLES (  
  
  
ID NUMBER,  
  
  
TITLE VARCHAR2(100),  
  
  
CONTENT CLOB  
  
  
); 
 
 

The CONTENT of the article is placed in the CONTENT field.

Now we need to find an article that contains "PHP Chinese users". You can do this:

 
 

  
  
<? Php
  
  
$ Conn = OCILogon ($ user, $ password, $ SID );
  
  
// DBMS_LOB.INSTR is used in the WHERE clause. It has four parameters,
The first two are the LOB operators (which can be expressed directly by fields)
And the string to be searched. The latter two indicate the start offset and the number of occurrences respectively.
Note that the return value must be determined, that is, it must be greater than 0.
  
  
$ Stmt = OCIParse ($ conn, "SELECT * FROM ARTICLES
WHERE DBMS_LOB.INSTR (CONTENT, 'php Chinese user', 1, 1)> 0 ″);
  
  
OCIExecute ($ stmt );
  
  
If (OCIFetchInto ($ stmt, $ result, OCI_ASSOC + OCI_RETURN_LOBS ))
  
  
{
  
  
...
  
  
}
  
  
OCIFreeStatement ($ stmt );
  
  
OCILogoff ($ conn );
  
  
?>
 
 

 

Oracle also provides many LOB data operations, such as LENGTH and SUBSTR. For their detailed usage, you can consider the Oracle development manual.

There are so many operations on PHP to manage Oracle LOB data. Since I have not been familiar with Oracle for a long time, there may be errors in this article. Thank you for your criticism.