Php guide to oracle database operations-I am original and have a summary of my experience.

I have used Oracle databases because of my working relationships. I found that there are not many users here, but I often find that some people have not answered questions about how to operate ORACLE databases using PHP. I have also asked a few questions, however, no response was received, so I decided to dedicate some of my skills and experience accumulated at work, hoping to help those who use the oracle database. I. configuring the environment I use the Oracle database due to my working relationship. I found that there are not many users here, but I often find that my questions regarding PHP operations on the ORACLE database are not answered, I have also asked a few questions, but no response is answered. Therefore, I decided to dedicate some of my skills and experience accumulated at work, hoping to help those who use oracle databases.
1. configure the environment:
Oracle8 Call-Interface (OCI8) is required to access databases above Oracle8 ). This expansion module needs Oracle8 client function library, so you need to connect to the remote Oracle database, but also install Oracle client software-can go to the Oracle site for free download-http://www.oracle.com, this is something that many beginners often ignore. Therefore, if you read this article, you should not mention "Why I cannot connect to the Oracle database" on the forum.
(1) first confirm that the Oracle8i client is installed, and then use net8 assistant (provided by the client software) to create a service name.

The service name is the sid of the oracle database. you can query server_names in the initsid file.
(2) remove the annotation symbol ";" before "extension = php_oci8.dll" in php. ini to enable php to load oracle-supported modules.

. Copy php_oci8.dll to the system32 subdirectory under your windows2000 server installation directory. Such as d: \ winnt \ system32, heavy

Start your machine.
(3) write a test file and try to see if the connection is correct (if the service name sid is test ):
The scott user is built in Oracle and does not need to be created by yourself. you just need to put the following file in your WEB root directory. If

If the data is displayed in the database, the connection is normal. if not, you need to check the errors in the previous steps.
Test. php
$ Dbconn = OCILogon ("scott", "tiger", "test ");
$ SQL = "select * from emp ";
$ Stmt = OCIParse ($ dbconn, $ SQL );
If (! $ Stmt ){
Echo "ERROR-cocould not parse SQL statement .";
Exit;
}
OCIExecute ($ stmt );
While (OCIFetchInto ($ stmt, & $ result_array ))
{
Echo

"EMPno = $ result_array [0]; Ename = $ result_array [1]; JOB = $ result_array [2]; MGR = $ result_array [3]
";
}
?>

2. use PHP to execute the Oracle stored procedure
(1) establish a stored procedure after connecting with sqlplus:
Create or replace procedure inoutdemo (
Par_in IN VARCHAR2,
Par_in_out in out VARCHAR2,
Par_out OUT VARCHAR2)
IS
BEGIN
Par_out: = par_in;
Par_in_out: = par_in | ''| par_in_out;
END;
(2) php file:
Sptest. php
//: In is the input variable;: inout is the input and output variable;: out is the output variable. For more information, see The PL/SQL manual of Oracle.
$ Conn = OCILogon ("scott", "tiger", "test ");
$ Stmt = OCIParse ($ conn, "BEGIN inoutdemo (: in,: inout,: out); END ;");
OCIBindByName ($ stmt, ": in", $ in, 32 );
OCIBindByName ($ stmt, ": inout", $ inout, 32 );
OCIBindByName ($ stmt, ": out", $ out, 32 );

$ In = "Hello ";
$ Inout = "World! ";

OCIExecute ($ stmt );
Echo"

";
Echo "in =". $ in ."
";
Echo "inout =". $ inout ."
";
Echo "out =". $ out ."
";
?>

III. Oracle database paging
Although Oracle does not have limit available in Mysql, it is very convenient, but it also has its own processing method. its special rownum is very important for paging.

. There are many paging methods, the most common of which is minus.
To display n1-n2 records, you can write:
(1) SELECT * FROM tablename WHERE rownum <= n2 minus SELECT * FROM tablename WHERE rownum <n1
Note: This statement cannot use order by; otherwise, an error is returned.
(2) the method for moving the pointer down is as follows:
Where: $ page indicates the current page, and $ pagesize indicates the number of records displayed on each page.
For ($ I = 0; $ I <($ page-1) * $ pagesize; $ I ++)
{
@ Ocifetch ($ stmt );
}
The data retrieved with ocifetch ($ stmt) is the record you want to display.
(3) If complex query statements are sorted by order by, use the following method:
SELECT TABLE_NAME, TABLE_TYPE FROM (select rownum rowseq, X. * FROM (SELECT * FROM CAT ORDER

TABLE_TYPE) X) where rowseq between n1 + 1 AND n2;
I like the third one most. it is very convenient to use it. Haha.
Other methods will not be introduced. it is very troublesome to use something like Oracle cursors, which is not suitable for PHP.

IV. insert special characters
Some characters such as single quotes cannot be processed using addslashes in Oracle, but you can use the CHR function of Oracle or add a single quotation mark.

.
For example, SQL> insert into table values ('it' | chr (39) |'s a test '));
Or SQL> insert into table values ('it's a test '));
Display:
It's a test.

V. PHP and Oracle transaction processing
OCIExecute () function: int OCIExecute (int statement [, int mode])
The second parameter has two modes: OCI_COMMIT_ON_SUCCESS by default, which can be omitted. OCI_DEFAULT indicates transaction)

Submitted. it is not automatically submitted.
If you have two statements in the program that operate the database at the same time and need to be successfully executed at the same time, roll back if one fails, you can write as follows:

$ Conn = OCILogon ($ username, $ password, $ sid );
// First sentence
$ SQL = "insert into tablename values ()";
$ Stmt = OCIParse ($ conn, $ SQL );
$ Result = OCIExecute ($ stmt, OCI_DEFAULT );
If (! $ Result ){
OCIRollback ($ conn); // rollback if the call fails
OCIFreeStatement ($ stmt); // release resources
OCILogoff ($ conn );
}

// Second sentence
$ SQL = "update tablename set ..";
$ Stmt = OCIParse ($ conn, $ SQL );
$ Result = OCIExecute ($ stmt, OCI_DEFAULT );
If (! $ Result ){
OCIRollback ($ conn); // rollback if the call fails
OCIFreeStatement ($ stmt); // release resources
OCILogoff ($ conn );
}


OCICommit ($ conn); // submit if both are successful
OCIFreeStatement ($ stmt); // release resources
OCILogoff ($ conn );



6. use PHP to manipulate Oracle LOB data (including image storage and display processing)
For PHP programmers, the biggest headache for Oracle is to use LOB to process images.
1. PHP operations BLOB:
Create a table to save the image. User-uploaded image files are stored in BLOB
Create table pictures (
Id number,
IMGTYPE, VARCHAR2 (60 ),
DESCRIPTION VARCHAR2 (100 ),
PICTURE BLOB
);
To automatically add ids, create a SEQUENCE:
Create sequence PIC_SEQ;

PHP program-insert part:
$ Conn = OCILogon ($ username, $ password, $ sid );
// Note the following two points: first, use the EMPTY_BLOB () function. This is an internal letter from Oracle.
// Number, returns a LOB operator. When inserting a LOB, you can only use this method to create one.
// Empty LOB operator, and then operate on the operator. EMPTY_BLOB () function is a needle
// For BLOB type, which corresponds to CLOB is EMPTY_CLOB (). Second,
// Returns picture so that php oci functions can process it.
$ Stmt = OCIParse ($ conn, "insert into pictures (id, imgtype, description, picture) VALUES

(PIC_SEQ.NEXTVAL, '$ imgtype', '$ 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,
// Generate a LOB object. Other possibilities include OCI_D_FILE and OCI_D_ROWID.
// Do not correspond to BFILE and ROWID objects.
$ 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 );

// Method 1: store data into the LOB object. Because the source data is a file, the LOB object's savefile () method is used directly. LOB

Other methods of the object 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 <br> 〉";
} Else {
Echo "Upload failed <br> 〉";
}

// Method 2: use the SAVE method to SAVE
// $ Fp = fopen ($ lob_upload, "r ");
// $ File-> save (fread ($ fp, filesize ($ lob_upload )));
// Fclose ($ fp );

// Release the LOB object
OCIFreeDesc ($ lob );
OCIFreeStatement ($ stmt );
OCILogoff ($ conn );
?>
Tips: use select dbms_lob.getlength (picture) from pictures in sqlplus to check whether the file has been stored in data

Library or use the strlen () function in the PHP program.

PHP program-Display part (getpicture. php ):
$ Conn = OCILogon ($ username, $ password, $ sid );
$ Stmt = OCIParse ($ conn, "SELECT imgtype, picture from pictures where id = $ pictureid ");
If (OCIFetchInto ($ stmt, $ result ))
{
Header ("Content-type:". $ result [0]);
Echo $ result [1]-> load ();
}
// Use strlen ($ result [1]-> load () to view the image size to check whether the image is properly stored in the database.

?>

To display an image, you only need:


The image is displayed.
Some online articles are written using the LOB value returned instead of the descriptor method. I did not try it. you can try it.
The code is as follows:
If (OCIFetchInto ($ stmt, $ result, OCI_ASSOC + OCI_RETURN_LOBS ))
{
Echo "Content-type:". StripSlashes ($ result [imgtype]);
Echo StripSlashes ($ result [picture]);
}

2. PHP operations CLOB:
Oracle has a data type named VARCHAR2, which is used to indicate an indefinite string. VARCHAR2 is also recommended by Oracle. However

There is a problem with VARCHAR2: Up to 4000 characters are allowed, which is equivalent to 2000 Chinese characters. If the length of a string in your program

If the degree is greater than 2000 Chinese characters, VARCHAR2 cannot meet the requirements. In this case, you can try CLOB. Maximum CLOB and BLOB length

It is 4 GB.

The following is an example (refer to the PHP English Manual ):
// The text to save
$ Clobtext = "different dr2 ";

// Db connection
$ Conn = OCIlogon ("user", "pw", "TNS ");

// Here, the original example uses a stored procedure. you can also use the BLOB operation method above.
// For example: $ stmt = OCIParse ($ conn, "insert into table (id, clobtext) VALUES (text. NEXTVAL ,,

EMPTY_CLOB () RETURNING clobtext INTO: clob ");


$ SQL = "begin tempclobtest_package.saveclob (: clob); end ;";

$ Clob = OCINewDescriptor ($ conn, OCI_D_LOB );

$ Stmt = OCIParse ($ conn, $ SQL );

OCIBindByName ($ stmt, ': clob', & $ clob,-1, OCI_ B _CLOB );

If (! OCIExecute ($ stmt, OCI_DEFAULT) {print_r (OCIError ($ stmt ));}
Else {echo "submitted successfully ";}

If ($ clob-> save ($ clobtext ))
{
OCICommit ($ conn );
Echo "submitted successfully ";
}
Else
{
Print_r (OCIError ($ stmt ));
}

// Release resources
$ Clob-> free ();
OCIFreeStatement ($ stmt );
?>