Abstracting Oracle Connectivity with PHP/OCI8 - Page 3
November 25, 2002
How Do I Use This?
Let's say you have a PL/SQL stored procedure in Oracle that fetches a mailing address by reading in two IN varchar values and then returns 4 OUT varchar2 values. Your procedure is defined as follows:
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 its IN variables, and fetch the OUT variables into bound PHP variables. We'll ensure that all of the OUT variables are stored in a PHP array and returned to the calling function. The PHP function will be defined as follows:
Now, here's what our PHP function body will look like:
Where the Magic Happened
In case you missed it, the magic happened in the one line that reads:
You'll notice that that line is 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.
Those Pretty OCIBindByName Arguments
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:
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 going to 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.