Source: IT Computer Tutorial
Anyone who has used Oracle knows that Oracle has a data type called VARCHAR2, which is used to represent strings of variable length. VARCHAR2 is also the type recommended by Oracle. But there is a problem with using VARCHAR2: it can only represent a maximum of 4000 characters, which is equivalent to 2000 Chinese characters. If the value of a certain character in your program is greater than 20,002 Chinese characters, VARCHAR2 cannot meet the requirements. At this time, you have two options, one is to use multiple VARCHAR2 to represent it, and the other is to use LOB fields. Here we take a look at the second method.
First, let’s take a general look at Oracle’s LOB fields. Oracle's LOB types are divided into three types: BLOB, CLOB and BFILE. CLOB is called character LOB, BLOB and BFILE are used to store binary data. The maximum length of CLOB and BLOB is 4GB, and they store values in the Oracle database. BFILE is similar to BLOB, but it places data in an external file, so it is also called external BLOB (External BLOB).
I think we are all familiar with MYSQL. There are similar data types in MYSQL, such as TEXT and BLOB. In PHP's MYSQL function, operations on TEXT/BLOB are direct, just like other types of data. But in Oracle, the situation is different. Oracle treats LOBs as a special data type and cannot use conventional methods in operations. For example, you cannot directly insert values into LOB fields in the INSERT statement, nor can you use LIKE to search.
Here are a few examples to illustrate how to use PHP's OCI function to insert, retrieve and query LOB data.
Insertion
cannot directly use the INSERT statement to insert values into LOB fields. Generally, there are the following steps:
1. First 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 INSERT statement
5. Assign a value to the LOB object
6. Release the LOB object and SQL statement handle.
The following example stores the image file uploaded by the user into a BLOB (or BFILE, the operation is slightly different). 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 realize the automatic increase of ID, create another SEQUENCE:
CREATE SEQUENCE PIC_SEQ;
and then the PHP program code used to process the data.
<?php
//Establish an Oracle database connection
$conn = OCILogon($user, $password, $SID);
//Submit SQL statements to Oracle
//Two points to note here: First, use the EMPTY_BLOB() function. This is an internal function of Oracle that returns a LOB locator. When inserting a LOB, you can only use this method to first generate an empty LOB locator, and then operate on this locator. The EMPTY_BLOB() function is for the BLOB type, and the corresponding one for CLOB is EMPTY_CLOB(). The second is the part after RETURNING, which returns the picture so that PHP's OCI function 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 a descriptor of a local LOB object. Note the second parameter of the function: OCI_D_LOB, which means Generate a LOB object. Other possibilities are OCI_D_FILE and OCI_D_ROWID, which correspond to BFILE and ROWID objects respectively.
$lob = OCINewDescriptor($conn, OCI_D_LOB);
//Bind the generated LOB object to the locator returned by the previous SQL statement. on.
OCIBindByName($stmt, ':PICTURE', &$lob, -1, OCI_B_BLOB);
OCIExecute($stmt);
//Save data into the LOB object. Because the source data here is a file, use the savefile() method of the LOB object directly. Other methods of LOB objects include save() and load(), which are used to save and retrieve data respectively. But the BFILE type has only one method, which is 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);
?＞
There is another thing to note: the value of the LOB field must be at least 1 character, so before save() or savefile(), make sure the value cannot be empty. Otherwise, Oracle will make an error.
There are two ways toretrieve
data from a LOB. One is to generate a LOB object, then bind it to the locator returned by a SELECT statement, and then use the load() method of the LOB object to retrieve the data; the other is to directly use PHP's OCIFetch*** function. The first method is much more troublesome than the second method, so I will talk about the second method directly.
Still use the table above.
<?php
$conn = OCILogon($user, $password, $SID);
$stmt = OCIParse($conn,"SELECT * FROM PICTURES WHERE ID=$pictureid");
OCIExecute($stmt);
//The secret lies in the third parameter of PCIFetchInfo: OCI_RETURN_LOBS. The third parameter is the FETCH mode. If OCI_RETURN_LOBS, the LOB value is directly put into the result array instead of the LOB locator, so the load() method of the LOB object is not needed.
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 (pictures) placed in LOB. Calling method (assuming the script name is getpicture.php):
<IMG SRC="getpicture.php?pictureid=99" ALT="Picture placed in Oracle LOB">
The query
has been mentioned before, and the LOB field of Oracle is LIKE cannot be used for matching. What to do? In fact, it is not complicated. Oracle has an anonymous package called DBMS_LOB, which contains all the processes required to operate LOB.
Suppose you have 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 want to find all the articles containing "PHP Chinese users" in the content. We can do it like this:
<?php
$conn = OCILogon($user, $password, $SID);
//The DBMS_LOB.INSTR procedure is used in the WHERE clause. It has four parameters. The first two represent the LOB locator (can be directly represented by a field) and the string to be searched for; the latter two represent the starting offset and the number of occurrences. It should be noted that its return value must be judged, 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 procedures for operating LOB data, such as LENGTH, SUBSTR, etc. As for their detailed usage, you can consider Oracle's development manual.
That’s all about the operations on LOB type data in the Oracle database. Since I haven't been in contact with Oracle for a long time, there may be errors in this article. Everyone is welcome to criticize and correct me.