Planet CakePHP

Our testing with Cake is almost complete. We have been able to read and write data correctly in our non UTF-8 Oracle database, managed to communicate with SAP, played enough with AJAX, so our users will not complain when dealing with tree like data and even managed to get authenticated by our MS-Windows 2003 active directory servers. So the last question left was how does one call an Oracle PL/SQL stored procedure or function from CakePHP ?

A little bit of googling and a little bit of digging into the CakePHP code revealed the following: The most common approach to calling stored procedures and functions is to create a member function in your model class and set up the call from there. In the simple case of calling a stored procedure with IN parameters only, the Model's query() method can be used to perform the actual call via the CALL PROC_NAME( ... ) SQL statement. The usual approach is to create a model method like this :

class MyModel extends AppModel {
    var $name = "MyModel";

    ...
    function callStoredProc( $param1, $param2)
    {
        $this->query("CALL my_stored_proc( $param1, $param2");
    }
    ...
}

If however you need to get data in and out of Oracle then you have to get your hands dirty and set up the call using low level oci_* functions. A simple example will clarify everything.

Let us suppose that you library users require that you display the average number of pages of the books stored in your library. A simple PL/SQL function to return this would probably be something like this:
/p>

CREATE OR REPLACE FUNCTION average_book_pages RETURN NUMBER
IS
   page_avg NUMBER;   
BEGIN
   SELECT avg( num_pages) 
      INTO page_avg
      FROM books;
   RETURN page_avg;   
END AVERAGE_BOOK_PAGES;

The next thing to do would be to create a getAvergaeBookPages() function in our Book model class :

<?php
class Book extends AppModel {
    var $name = 'Book';
    var $belongsTo = ...
    var $validate = array( ...

    function getAverageBookPages()
    {
        // every model has a datasource
        $dataSource = $this->getDataSource();
        // and every datasource has a  database connection handle
        $connection = $dataSource->connection;
        if (!$connection)
            return -1;

        // from now you need this Oracle specific approach 
        $statement = oci_parse( $connection,
                "begin :ret := AVERAGE_BOOK_PAGES(); end;");
        oci_bind_by_name($statement, ':ret', $r, 100);
        oci_execute($statement);
        return $r;
    }
}
?>

The last parameter of oci_bind_by_name is the number of bytes to allocate for returning the string representation of the bind variable. Just make sure that you allocate enough memory for that. My test data yield an average of 762,666666666666666666666666666666666667 pages per book (Thank you Mr. Minasi) and so oci_execute kept giving me ORA-06502: PL/SQL: numeric or value error: character string buffer too small until I raised the value to 100.

So that does it. Now calling this from you controller code is as easy as :

<?php
class BooksController extends AppController {
    var $name = 'Books';

    ...

    function index()
    {
        $this->Book->recursive = 0;
        $this->set('books', $this->paginate());
        $this->set('averagePages', $this->Book->getAverageBookPages());
    }

    ...
}