Introduction
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 Extension
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";
$conn??=??oci_connect($dbuser,$dbpassword,$db);
if (!$conn){
echo "Connection error";
exit;
}
$sql = "SELECT??*??FROM??EMPLOYEE_TABLE";
$stmt??=??oci_parse($conn,??$sql);
if??(!$stmt)??{
echo "Error in preparing the statement";
exit;
}
oci_execute($stmt,??OCI_DEFAULT);
while($result =oci_fetch_array($stmt)) {
echo $result[??EMPLOYEE_NAME??] . "<br>";
}
oci_close($conn); # optional
?>
Code walkthrough
$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:
CONN_STRING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(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.
