Prerequisites:
- The environment variable
ORACLE_HOME
must be set so that PHP can find the client libraries. - Windows users need the
php.ini
directiveextension=php_oci8***.dll
. - PHP requires read access to the client libraries.
The oci_connect()
tries to establish a connection to the server. The order of the parameters is first username and password, then the name of the service (that has been configured using the configuration assistant or is part of the tnsnames.ora
file).
Connecting to Oracle:
<?php $conn = oci_connect('username', 'password', '//localhost/db_service'); if (!$conn) { $error = oci_error(); echo $error['code'] . '<br>'; echo $error['message']; exit; } echo 'Connected'; oci_close($conn);
Sending SQL to Oracle
<?php $conn = oci_connect('username', 'password', '//localhost/db_service'); if (!$conn) { $error = oci_error(); echo $error['code'] . '<br>'; echo $error['message']; exit; } $stmt = oci_parse($conn, 'INSERT INTO user_table (name, email) VALUES (:name, :email)'); oci_bind_by_name($stmt, ':name', $_POST['name']); oci_bind_by_name($stmt, ':email', $_POST['email']); oci_execute($stmt); echo 'Record saved.'; oci_close($conn);
To send SQL to Oracle, two steps are required. First, a call to oci_parse()
parses an SQL string and returns a resource that can then be executed using oci_execute()
.
The preceding code uses the prepared statements to escape the dangerous characters in the SQL string, prepared statements are a must but are also very easy to implement. For this, the SQL statement must contain placeholders that start with a colon:
<?php //... $stmt = oci_parse($conn, 'INSERT INTO user_table (name, email) VALUES (:name, :email)');
Then, these placeholders have to be filled with values. For this, oci_bind_by_name()
must be used:
<?php //... oci_bind_by_name($stmt, ':name', $_POST['name']);
The preceding code sends some form data to the database. No worries about special characters because oci_bind_by_name
takes care of that.
Retrieving Results of a Query to Oracle
You have several ways to access the return values of an SQL query, but the following functions are used most often in practice:
oci_fetch_assoc()
returns the current row in the resultset as an associative array.oci_fetch_object()
returns the current row in the resultset as an object.oci_fetch_row()
returns the current row in the resultset as a numeric array.oci_fetch_all()
returns the complete resultset as an array of associative arrays. However, five parameters are required: the statement object fromoci_parse()
, the array that is used for the return data, the number of lines to skip, the maximum number of rows to be returned (-1
means infinite), and whether to return a numeric (OCI_NUM
) or associative (OCI_ASSOC
) array. See https://php.net/manual/function.oci-fetch-all.php.
The listing in This uses a while
loop and oci_fetch_object()
to retrieve all data in the table.
Retrieving Data from Oracle
<?php $conn = oci_connect('username', 'password', '//localhost/db_service'); if (!$conn) { $error = oci_error(); echo $error['code'] . '<br>'; echo $error['message']; exit; } $stmt = oci_parse($conn, 'SELECT * FROM quotes'); oci_execute($stmt); while ($row = oci_fetch_object($stmt)) { echo $row->ID.', '.$row->NAME.', '.$row->EMAIL.'<br>'; } oci_close($db);
Oracle always returns column names in uppercase. Therefore, you have to use uppercase object properties or uppercase associative array keys when accessing the return values of an SQL query.
Working with Databases: