Home arrow PHP arrow Page 5 - Abstracting Oracle Connectivity with PHP/OCI8

Abstracting Oracle Connectivity with PHP/OCI8

In this article Lorenzo explains a neat utility to simplify connecting to an Oracle database using PHP/OCI8. This is sure to make DB operations between PHP and Oracle much easier and faster.

Author Info:
By: Dante Lorenso
Rating: 4 stars4 stars4 stars4 stars4 stars / 19
January 08, 2003
  1. · Abstracting Oracle Connectivity with PHP/OCI8
  2. · Oracle How-To The Hard Way
  3. · Wrapping it up into a PHP Class
  4. · How Do I Use This?
  5. · Those Pretty OCIBindByName Arguments
  6. · Switching Between Development, QA and Production Environments
  7. · Conclusion

print this article

Abstracting Oracle Connectivity with PHP/OCI8 - Those Pretty OCIBindByName Arguments
(Page 5 of 7 )

Ok, so you aren't gonna let me slide that neat trick in there without an explanation, eh?

I created an array of bind variables called 'bargs' (short for bind arguments). The 'bargs' array is an array of arrays where each sub array contains:
  • 0) the bind argument name,
  • 1) the current value,
  • 2) the length of the variable.
For IN variables, you only need to define a bind argument as array("IN_NAME", $value, -1). The -1 means that the length of the variable is not gonna change, so who cares.

For OUT variables, you need to define the bind args as array("OUT_NAME", "", 128) where the empty string "" is the current value, and the number 128 is the allocated space for your return data.

Remember to make this a large enough value, or you won't get your output.

The cool thing about using BindByName variables is that you no longer have to do those yuck hacks like escaping quotes and things for your Oracle inputs.

Also, you can now write fun little PL/SQL chunks that will do SELECT INTO and fetch your data that way.

Examples - A Simple SELECT statement
In this example, I execute a simple SELECT statement. The input is a customer's login name, and I select out some made-up columns named COLA, COLB, and COLC from a made up table SAMPLETABLE.

A nice feature of this function is that I might get a return code of false. A 'FALSE' return code means that something failed. A failure might occur in the connect, bind, parse, execute...etc.

Well, if I really cared, I can look at the $OCI8Hook->ERROR string to read that error message.

Most of the time I DON'T care, though...so I give a happy error message to the user and read the Apache log files later.

* Will select a two dimensional array containing COLA then COLB
* which should have access from this login. $data[COLA][COLB]...
* Return:
* false - System Error
* array - Success.
function simple_select_example($login)

$sql = sprintf("

// Set up our Bind args...
$bargs = array();
array_push($bargs, array("IN_LOGIN", $login, -1));

// run the query...
$stmt = $this->query("DBXYZ", $sql, $bargs);
if (! $stmt) return(false);

// loop through the returned rows and convert to a PHP array
$data = array();
while (@OCIFetchInto($stmt, $row, OCI_ASSOC | OCI_RETURN_NULLS))
$data[$row["COLA"]][$row["COLB"]] = 1;

// return the data that we just fetched...
return ($data);

Example - An INSERT statement with error checking
A lot of Oracle programmers want to have certainty that an insert or update operation was performed successfully.

Here is an example where I wrap an INSERT statement in a chunk of PL/SQL so that I'll get a clear success code of '7' when the insert works.

Remember that our OCI8Hook class always returns 'false' on error. So, if we get a FALSE from this function, we still assume error. I need an error code which is NOT 0 or false or "" in order to ensure success. Here is my solution:

* Return
* 0 - System Error
* 7 - Success
function sample_insert($value1, $value2, $value3)

// build the query we'll be sending in...
$sql = sprintf("
INSERT INTO sampletable
(cola, colb, colc)

// Set up our Bind args...
$bargs = array();
array_push($bargs, array("IN_VALUE1", $value1, -1));
array_push($bargs, array("IN_VALUE2", $value2, -1));
array_push($bargs, array("IN_VALUE3", $value3, -1));
array_push($bargs, array("RETURN_CODE", "", 32));

// run the query...
$stmt = $this->query("DBXYZ", $sql, $bargs);
if (! $stmt) return(false);

// return the return code (if it's there)...
if (array_key_exists("RETURN_CODE", $bargs)) {
return ($bargs["RETURN_CODE"]);

// it doesn't seem to be working...


blog comments powered by Disqus

- Removing Singletons in PHP
- Singletons in PHP
- Implement Facebook Javascript SDK with PHP
- Making Usage Statistics in PHP
- Installing PHP under Windows: Further Config...
- File Version Management in PHP
- Statistical View of Data in a Clustered Bar ...
- Creating a Multi-File Upload Script in PHP
- Executing Microsoft SQL Server Stored Proced...
- Code 10x More Efficiently Using Data Access ...
- A Few Tips for Speeding Up PHP Code
- The Modular Web Page
- Quick E-Commerce with PHP and PayPal
- Regression Testing With JMeter
- Building an Iterator with PHP

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials