[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;
