Abstracting Oracle Connectivity with PHP/OCI8 - Page 3

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:

    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 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:

    /** 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 that reads:

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

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:

• 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 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.