Read an ORACLE cursor. How?

[eluser]duartix[/eluser]
I've managed to get all of the transactional (inserts, updates and deletes) bit of my ORACLE application to execute on the database through stored procedure executions on packages, called by CI's models. This is very important because it relegates data access permissions to the roles that grant those packages' execution rights.

However, my queries are still running directly on the tables via direct SELECTs through CI's models. Basically I don't know how to read an ORACLE cursor into a CodeIgniter's model, but I'm willing to push all those SELECT's to the DB because it will make the application more consistent, easier to manage and easier to maintain (the rest of the team is a lot more proficient on PL/SQL than PHP)

Does anyone have a light on how to do this?
The DB part would be along these lines:

Code:

CREATE OR REPLACE PACKAGE test_pack IS
   TYPE dataCursor IS REF CURSOR;

   PROCEDURE retrieve_DATA (p_cursor IN OUT dataCursor);
END test_pack;


CREATE OR REPLACE PACKAGE BODY test_pack IS
PROCEDURE retrieve_DATA ( p_cursor IN OUT dataCursor) IS
BEGIN
    OPEN p_cursor FOR
        select * from my_table;
END retrieve_DATA;

END test_pack;

Thank you in advance.

[eluser]duartix[/eluser]
If no one knows a way to do it, do you know of any framework/lib/package that automates this?

[eluser]duartix[/eluser]
Well, yesterday I managed to build a proof of concept, and I'm very happy to say that with very little OO programming, I managed to solve the problem and I spent the rest of the day moving all my SELECTS from the models to Stored Procedures in the DB that return cursors.

Since this can help someone else, I'm publishing it here in condensed form.

Here's what I had to do. First thing I needed, was to create a subclass that extends CI's CI_Model (the code is stripped of exception handling for size and clarity) :

Code:

<?php

/**
* This class extends CI_Model to allow it to read ORACLE cursors
* from STORED PROCEDURES in the DB
*/

class OracleModel extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
    
    public Function readCursor($storedProcedure, $binds) {

        //
        // This function needs two parameters:
        //
        // $storedProcedure - the name of the stored procedure to call a chamar. Ex:
        //  my_schema.my_package.my_proc(:param)
        //  
        // $binds - receives an array of associative arrays with: parameter names,
        // values and sizes
        //
        // WARNING: The first parameter must be consistent with the second one
        
            $conn = oci_connect('scott', 'tiger', '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP ........');

            if ($conn) {
                
                // Create the statement and bind the variables (parameter, value, size)
                $stid = oci_parse($conn, 'begin :cursor := ' . $storedProcedure . '; end;');
                foreach ($binds as $variable)
                    oci_bind_by_name($stid, $variable["parameter"], $variable["value"], $variable["size"]);

                // Create the cursor and bind it
                $p_cursor = oci_new_cursor($conn);
                oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);

                // Execute the Statement and fetch the data
                oci_execute($stid);
                oci_execute($p_cursor, OCI_DEFAULT);
                oci_fetch_all($p_cursor, $data, null, null, OCI_FETCHSTATEMENT_BY_ROW);
                
                // Return the data
                return $data;
            }
      }
}
?>

Then your models will now look like this:

Code:

<?php
class myModel extends OracleModel {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function getData($param1, $param2) {
        try {
            $variables[0] = array("parameter" => "p1", "value" => $param1, "size" => 100);
            $variables[1] = array("parameter" => "p2", "value" => $param2, "size" => 100);
            return $this->readCursor("myschema.mypackage.getData(:p1, :p2)", $variables);
        }
    }
}
?>

Finally, your ORACLE DB will have a package with a function that returns a cursor with the data (I'll put just the body):

Code:

CREATE OR REPLACE PACKAGE BODY myschema.mypackage
IS

   FUNCTION getData (p1 in varchar2, p2 in varchar2)
      RETURN SYS_REFCURSOR
   AS
      theCursor   SYS_REFCURSOR;
   BEGIN
         OPEN theCursor FOR
              SELECT   *
                FROM   myTable t
               WHERE  t.id1 = p1 AND t.id2 = p2;

      RETURN theCursor;
   END getData;

END mypackage;