Zend_Db Oracle Functions/Procedures

For some reason, this was a nightmare to figure out. All I wanted to do was call an oracle function from the Zend Framework using PDO_OCI as my database adapter. After a FULL DAY of research and testing, I finally found some code that works!

The Database Adapter

I use a configuration file for my database adapter settings. As you can see, the adapter is using the PDO_OCI driver.

//all this is inside a config.ini file

db.myoracledatabase.adapter = PDO_OCI
db.myoracledatabase.params.host = mydomain.com
db.myoracledatabase.params.username = mydatabaseusername
db.myoracledatabase.params.password = mydatabasepassword
db.myoracledatabase.params.dbname = oraclesid
db.myoracledatabase.params.port = 1024
...

I can then initialize the database with the following code:

//initialize configuration and pass to driver factory
$config = new Zend_Config_Ini(DIR_CONFIG . '/config.ini'
$mydatabase = Zend_Db::factory($config->db->myoracledatabase);
...

Stored Procedure

The following code is what I was using to call stored procedures. I was using the Zend_Db_Table classes for my database abstraction layer and had created a bunch of classes that extended Zend_Db_Table_Abstract. That doesn’t matter much here, but I just wanted to point it out.

Oracle Procedure Code

...
create or replace procedure mydatabase.dosomething
(
in_parameter1 in varchar2,
in_parameter2 in varchar2,
out_value out number
)
is
begin
  ...do some code
  ...do more code
  return 5;
end dosomething;
...

Zend Framework Code

...
$sql = 'call onlinedatabase.mystoredprocedure( :in_param1, :_in_param2, :get_param1)';
$parameters = array(':in_param1'=>'foo',':in_param2'=>'bar');

$returndata = null;
$statement = $mydatabase->query($sql,$parameters);
$statement->bindParam(':get_param1',$returndata,PDO::PARAM_INT,12);
$result = $statement->execute();

//the $returndata variable contains the returned value (5)
Zend_Debug::dump($returndata);
...

The parameters listed about (e.g. :in_param1, :in_param2) can be named anything, but need to start with the colon symbol.

Stored Function

The difference between procedures and functions is that functions can only return one value and don’t have “out” variables.

Zend Framework Code

...
//if you are returning a string value from the function you need
//to make sure the string is big enough to hold the returned value
//meaning that you have to pre-populate it; this is setup to 
//accept a string of 4100 characters from the function
$result = '';
$result = str_pad($result, 4100, ' ', STR_PAD_LEFT);

$params = array();
//note that $result has to have an ampersand
$params['result_param'] = &$result;
$params['in_param1'] = 'value 1'; //could be an integer, string, etc.
$params['in_param2'] = 0; //could be an integer, string, etc. 

$sql = 'begin :result_param := onlinedatabase.mystoredfunction( :in_param1, :_in_param2)';
$statement = $mydatabase->prepare($sql);
$statement->execute($params);

//the $returndata variable contains the returned value
Zend_Debug::dump($result);
...