Use PHP to manipulate Oracle LOB data

PHP + Oracle (OCI) preliminary describes how to use the OCI function in PHP to connect to the Oracle database, execute some SQL queries, and close the database connection. This article will introduce another difficult issue: using the OCI function of PHP to manipulate the LOB field of Oracle. Reading this article requires the knowledge in the PHP + Oracle (OCI) preliminary article. Anyone who has used Oracle knows how to use the OCI function in PHP to connect to the Oracle database, execute some SQL queries, and close the database connection. This article will introduce another difficult issue: using the OCI function of PHP to manipulate the LOB field of Oracle. Reading this article requires the knowledge in the PHP + Oracle (OCI) preliminary article.

Anyone who has used Oracle knows that Oracle has a data type named VARCHAR2, which is used to indicate a string with an indefinite length. VARCHAR2 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 cannot 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 the php oci function to insert, retrieve, and query LOB data.

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 stores the image files uploaded by users 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.

// 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 Oracle internal function that returns a LOB locator. When inserting a LOB, you can only use this method to generate an empty LOB locator and then operate on it. EMPTY_BLOB () function is for BLOB type, corresponding to CLOB is EMPTY_CLOB (). The second is the part after RETURNING, which returns picture so that php oci functions can handle it.
$ Stmt = OCIParse ($ conn, "insert into pictures (id, description, picture)
VALUES (pic_seq.NEXTVAL, '$ description',' $ lob_upload_type ', EMPTY_BLOB () RETURNING picture INTO: PICTURE ");
// Generate the descriptor of a local LOB object. Note the second parameter of the function: OCI_D_LOB, which indicates generating a LOB object. Other possibilities include OCI_D_FILE and OCI_D_ROWID, which correspond 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 is a file, the LOB object's savefile () method is used directly. Other LOB object methods include save () and load (), which are used to save and retrieve data respectively. However, the BFILE type has only one method: save ()
If ($ lob-> savefile ($ lob_upload )){
OCICommit ($ conn );
Echo "uploaded successfully
";
} Else {
Echo "Upload failed
";
}
// 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

There are two ways to retrieve data from a LOB. 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.

$ Conn = OCILogon ($ user, $ password, $ SID );
$ Stmt = OCIParse ($ conn, "SELECT * from pictures where id = $ pictureid ");
OCIExecute ($ stmt );
// The secret is stored on the third parameter of PCIFetchInfo: OCI_RETURN_LOBS. The third parameter is the FETCH mode. if OCI_RETURN_LOBS is used, the LOB value is directly placed in the result array, instead of the LOB locator, and 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 ):


Query

As mentioned above, the LOB field in Oracle cannot be matched with LIKE. 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:

$ Conn = OCILogon ($ user, $ password, $ SID );
// DBMS_LOB.INSTR is used in the WHERE clause. It has four parameters. The first two are the LOB positioning operators (which can be expressed directly by Fields) and the strings to be searched. the last two are 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 Oracle database 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.