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); ...
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.
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); ...