Oracle stored procedures fail

[eluser]Skeleton Man[/eluser]
Hi all,

I have been attemping to run oracle stored procedures as follows:

Code:

$package   = "AOBADM.MYIQ_BUILDING_PKG";
        $procedure = "p_building_fetch";

        $myiq_building = null;
        $group         = null;
        $key           = "";
        $return_code   = -1000;
        $return_text   = "UNDEF";

        // Get a cursor
        $p_buildings_cursor = $this->db->get_cursor();

        // Parameters must be in :<param_name> format
        // Input parameter value should be a string, int, etc
        // Output parameter value should be a reference to a variable

        // Input parameters
        $params[] = array('name'  => ':p_user_id',
                          'value' => $this->user_id,
                          'length' => -1,
                          'type' => SQLT_CHR);

        // Output parameters
        $params[] = array('name'  => ':p_buildings',
                          'value' => &$p_buildings_cursor,
                          'length' => -1,
                          'type'  => OCI_B_CURSOR); // pass by reference, sys_refcursor

        $params[] = array('name'  => ':p_status',
                          'value' => &$return_text, // pass by reference
                          'length' => -1,
                          'type' => SQLT_CHR);

        $params[] = array('name'  => ':p_code',
                          'value' => &$return_code,  // pass by reference
                          'length' => -1,
                          'type' => SQLT_CHR);

        $this->db->stored_procedure($package, $procedure, $params);

At first it was crashing PHP and returning "502 bad gateway", so I did some googling and found this post.

I patched oci8_driver.php and DB_driver.php per the suggestions, and it stopped PHP crashing, but now I get: "ociexecute() [function.ociexecute]: ORA-01008: not all variables bound".

It clearly isn't binding anything, judging by this sql:

begin AOBADM.MYIQ_BUILDING_PKG.p_building_fetch(:p_user_id,:p_buildings,:p_status,:p_code); end;

Can someone suggest how I can fix this ?

Chris

[eluser]Skeleton Man[/eluser]
I ended up rolling my own and bypassing CI's database library for stored procedures with cursors:

Code:

class Db_util
{
    private $conn = null;
    private $stmt = null;
    private $curs = null;

    public function __construct()
    {
        require_once(APPPATH.'config/database.php');
        $this->conn = oci_connect($db['default']['username'],$db['default']['password'],$db['default']['hostname']);
    }

    public function stored_procedure($package,$procedure,$params)
    {
        // Start our query with the package/procedure name
        $sql = "begin {$package}.{$procedure}(";

        // Build a list of parameters
        foreach ($params as $param)
        {
            $sql .= $param['name'].',';
        }

        $sql = rtrim($sql,','); // Strip the trailing comma

        // End our query with the word 'end'
        $sql .= "); end;";

        // Parse our sql
        $this->stmt = oci_parse($this->conn,$sql);

        // Loop through parameters again, binding parameters, and checking if we have a cursor
        foreach ($params as $param)
        {
            if ($param['type'] == OCI_B_CURSOR)
            {
                // Get a cursor
                $this->curs = oci_new_cursor($this->conn);

                // Bind the cursor
                oci_bind_by_name($this->stmt,$param['name'],$this->curs,-1,OCI_B_CURSOR);
            }else{

                // Bind the parameter
                oci_bind_by_name($this->stmt,$param['name'],$param['value'],$param['length'],$param['type']);
            }
        }

        oci_execute($this->stmt);
        oci_execute($this->curs);
    }

    public function cursor_fetch_row()
    {
        return oci_fetch_row($this->curs);
    }

    public function cursor_fetch_object()
    {
        // Lower case is preferable
        $tmp = oci_fetch_object($this->curs);

        if (!is_object($tmp))
        {
            return false;
        }

        $new = new stdClass();

        foreach ($tmp as $key => $val)
        {
            $lower = strtolower($key);
            $new->{$lower} = $val;
        }

        return $new;
    }

    public function free_close()
    {
        oci_free_statement($this->stmt);
        oci_free_statement($this->curs);
        oci_close($conn);
    }
}

This is how it's called:

Code:

$package   = "AOBADM.MYIQ_BUILDING_PKG";
$procedure = "p_building_fetch";

$return_code   = '';
$return_text   = '';

// Parameters must be in :<param_name> format
// Input parameter value should be a string, int, etc
// Output parameter value should be a reference to a variable

// Input parameters
$params[] = array('name'   => ':p_user_id',
                  'value'  => 388,
                  'length' => -1,
                  'type'   => SQLT_CHR);

// Output parameters
$params[] = array('name'   => ':p_buildings',
                  'value'  => null,          // cursors are handled internally
                  'length' => -1,
                  'type'   => OCI_B_CURSOR);

$params[] = array('name'   => ':p_status',
                  'value'  => &$return_text, // pass by reference
                  'length' => 32,
                  'type'   => SQLT_CHR);

$params[] = array('name'   => ':p_code',
                  'value'  => &$return_code,  // pass by reference
                  'length' => 32,
                  'type'   => SQLT_CHR);

$db = new Db_util();
$db->stored_procedure($package,$procedure,$params);

print "{$return_code}\n{$return_text}\n\n";

while ($data = $db->cursor_fetch_object())
{
    print_r($data);
}

$db->free_close();

This removes the need for the calling code to create and pass a cursor, everything is handled by the wrapper. If someone wants to CI-ize my code, feel free - this was just a quick workaround.