Abstracting Oracle Connectivity with PHP/OCI8

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 could 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("
            SELECT  COLA, COLB, COLC
            FROM    SAMPLETABLE
            WHERE   LOGIN = LOWER(:IN_LOGIN)
        ");

        // 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 an 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("
        BEGIN
            :RETURN_CODE := 0;
            INSERT INTO sampletable
                    (cola, colb, colc)
                VALUES (LOWER(:IN_VALUE1), UPPER(:IN_VALUE2), LOWER(:IN_VALUE3));
            :RETURN_CODE := 7;
        EXCEPTION
            WHEN OTHERS THEN
                :RETURN_CODE := 0;
        END;
        ");

        // 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...
        return(false);
    }

    //----------------------------------------------------------------------