Abstracting Oracle Connectivity with PHP/OCI8 - How Do I Use This?

(Page 4 of 7 )

Let's say your have a PL/SQL stored procedure in oracle that fetches a mailing address by reading in two IN varchar values and returns 4 OUT varchar2 values.

Your procedure is defined as follows:

PROCEDURE get_mailing_addr
(
in_comp_code IN VARCHAR2,
in_cust_code IN VARCHAR2,
line1 OUT VARCHAR2,
line2 OUT VARCHAR2,
csz OUT VARCHAR2,
zipcode OUT VARCHAR2
);

We can write PHP code that will act as a wrapper for this PL/SQL procedure. The PHP code will connect to the database, send it's IN variables, and fetch the OUT variables into bound PHP variables. We'll ensure that all those OUT variables are stored in a PHP array and returned to the calling function. The PHP function will be defined as follows:

/** PUBLIC
* Return Array containing "LINE1", "LINE2", "CSZ", and "ZIPCODE" as keys
* upon success.
* Returns false if database error.
*/
function get_mailing_addr ($comp_code, $cust_code) { ... }

Now, here's what our PHP function body will look like...


//-----------------------------------------
/** PUBLIC
* Return Array containing "LINE1", "LINE2", "CSZ", and "ZIPCODE" as keys
* upon success.
* Returns false if database error.
*/
function get_mailing_addr ($comp_code, $cust_code)
{

// build the query we'll be sending in...
$sql = sprintf("
BEGIN
get_mailing_addr (
:IN_COMP_CODE,
:IN_CUST_CODE,
:LINE1,
:LINE2,
:CSZ,
:ZIPCODE);
END;
");

// Set up our Bind args...
$bargs = array();
array_push($bargs, array("IN_COMP_CODE", $comp_code, -1));
array_push($bargs, array("IN_PREM_CODE", $cust_code, -1));
array_push($bargs, array("LINE1", "", 64));
array_push($bargs, array("LINE2", "", 64));
array_push($bargs, array("CSZ", "", 128));
array_push($bargs, array("ZIPCODE", "", 32));

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

// tidy up Line3 into CITY and STATE
unset($bargs["IN_COMP_CODE"]);
unset($bargs["IN_CUST_CODE"]);

// return the bargs results...
return($bargs);
}
//-------------------------------------

Where the Magic Happened
In case you missed it, the magic happened in the one line where it reads:

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

You'll notice that that line called '$this->query'. Yes, that's right. The 'get_mailing_addr' function is inside another class which EXTENDS OCI8Hook! In fact, this is probably the easiest way to get this connectivity.

Any time you want to create a library of PHP calls which WRAP some Oracle calls, just build a class to encapsulate all the functions into a single location, and make that class extend OCI8Hook.

Suddenly you can build and run Oracle queries by simply recreating these functions. The connect, logon, bind, parse, and execute pieces of the queries are all handled for you.

Next: Those Pretty OCIBindByName Arguments >>
 

More PHP Articles
More By Dante Lorenso

Comments On: Abstracting Oracle Connectivity with PHP/OCI8

>>> Be the FIRST to comment on this article!