This article is for the developer who wants to use the PHP scripting language with an Oracle database to develop their application. PHP is one of the most commonly used scripting languages, as it is open source and has procedural as well as object-oriented capabilities. Oracle is a popular RDBMS and that's the reason this combination is very useful for web-based application development.
There are different methods of using PHP with Oracle, but this document focuses on using OCI8 and the ADOdb abstraction library to connect to Oracle. ADOdb in turn uses the OCI8 oracle extension to connect to the Oracle database. There are multiple tutorials and articles available which explain different methods of using PHP and Oracle, but this one is written considering a simple and efficient way of implementation.
The first question that comes to mind is why we should use the ADODB library if we are already using the OCI8 extension--and ADOdb interacts using OCI8 internally. The answer is "it hides all database dependent complexity and can be more easily implemented and understood by a new developer. Additionally, it adds portability to the application.
OCI8 is the most popular PHP extension for Oracle, and is recommended for maximum performance. It effectively shows the potential of Oracle's features in the application and provides stability. The PHP OCI8 extension is included in PHP version 3 onwards.
Connection Methods for OCI8 extension
OCI8 provides different connection methods to connect to Oracle database, as shown below.
oci_connect() : It is used in the case of non-persistent connection. If this function is called more than once in the script, then the same connection is used, provided the connection is not yet closed.
oci_pconnect() : It is used in the case of persistent connection. Persistent connections do not get closed even after the completion of the script. It improves the performance as it reduces the overhead of opening and closing the database connection.
oci_new_connection() : It always creates a new connection to the database even if there is an existing connection. Generally this is not used except in some cases where application needs two separate connections.
Closing Oracle connection
Oci_close() : The connection is opened through the calls oci_connect(). oci_new_connection() gets closed automatically but we can explicitly close them using this function.
Note: a connection is opened using method oci_pconnect() cannot be closed using oci_close().
Sample example to use OCI8
Now we will show you how to connect to the Oracle database using OCI8 oracle extension and we will execute a select statement which select some fields and we will display it on the web page.
$dbuser = "dbuser";
$dbpassword = "dbpassword";
$db = "oracle_sid";
$dbuser : The Oracle user name
$dbpassword : The Oracle password
$db : the database connection string or the alias;
the connection string can be stored in the tnsname.ora file.
For example, in the tnsname.ora file, we can include
the sample connection string as shown below:
(ADDRESS = (PROTOCOL = TCP)(HOST = <host_name>)(PORT = 1521))
(SID = CONN_STRING)
$conn = oci_connect($dbuser,$dbpassword,$db);
Passing the username, password and SID or connection string from the tnsname.ora file to the oci_connect() method. $conn variable store the Returned connection identifier. As per the above explanation we can use the connection string directly or we can use an alias.
$conn = oci_connect($dbuser,$dbpassword, CONN_STRING);
$sql = "SELECT * FROM EMPLOYEE_TABLE";
Here $sql stores the sql statement which is to be executed:
$stmt = oci_parse($conn, $sql);
oci_parse() function prepares the statement for execution. It takes the connection identifier and the sql query as a parameter and returns a statement handler.
oci_execute() function executes the parsed statement. This function can have two parameters. The first one is the valid OCI statement identifier and the second one is the mode (option parameter). By default OCI_COMMIT_ON_SUCCESS.
Note: If you don't want statements to be committed automatically, you should specify OCI_DEFAULT as mode.
The statement above stores the result of oci_fetch_array($stmt) in the variable $result. oci_fetch_array() returns an array. The second parameter is optional for this function and can have values like OCI_BOTH, OCI_ASSOC, OCI_NUM depending on the type of array that is required. Once the array is returned and stored in the variable $result, we display the Employee name from the selected columns.
This function is used to close the connection to the Oracle database.
This completes the explanation of the sample code that is used to connect and perform operations on the Oracle database using PHPs ADOdb abstract library.
Join us next week when we delve into the ADOdb library and continue our discussion on using Oracle and PHP!