Use PHP to manipulate Oracle LOB data

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 not be unfamiliar 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 an extraordinary 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
);

If you want to automatically add ids, create another 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
// Pay attention to 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. Pay attention to 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 <br> ";
} Else {
Echo "Upload failed <br> ";
}
// Release the LOB object