Executing PL/SQL code in Zend Framework
Posted by: miquel in Database, PHP, SQL Developer, Zend Framework
In the Oracle world, a lot of the code of an application is stored in procedures inside the database. This gives the best possible performance since the data never leaves the database avoiding data to be sent to and from the client. Code in stored procedures in Oracle is written using the PL/SQL language.
On the other hand, writing web applications is best done in scripting languages like PHP or Python. In addition, it is always recommended to use some type of framework. In PHP you can use CakePHP, Symfony, Codeigniter or, the one we are currently using: Zend Framework.
Calling PL/SQL code from PHP can be tricky sometimes, specially when the PL/SQL procedure has input and output parameters. In this posting I will show you how to call a procedure from the PHP using Zend Framework. I will assume you have some experience using Zend Framework, specially the Database module (Zend_db).
Lets start by creating in the database a simple PL/SQL procedure. Log into sqlplus or sqldeveloper and create the following package and package body:
/* Return a greeting message. */
procedure say_hello(
name IN VARCHAR2,
greeting OUT NOCOPY VARCHAR2
);
end test_pk;
/
CREATE OR REPLACE
package body test_pk AS
/* Return a greeting message. */
procedure say_hello(
name IN VARCHAR2,
greeting OUT NOCOPY VARCHAR2)
IS
lResult VARCHAR2(40) := ”;
begin
lResult := ‘Hello, ‘ || name;
greeting := lResult;
end;
end test_pk;
This code will create a procedure say_hello which receives two parameters: as input, name and an output variable, greeting. The procedure will write in the out parameter ‘Greeting, XXXXX‘ where XXXXX will be replaced by the name input parameter.
Now lets see how we can call the PL/SQL code from PHP. Take a look at the following code:
// SQL which will call the PL/SQL code.
$sql = ‘begin test_pk.say_hello(:p1, :p2); end;’;
// we create a statement with the DB connection
// and the SQL code.
$statement = new Zend_Db_Statement_Oracle( $db, $sql );
// create output variable. Reserve enough space for answer
$greeting = sprintf( "%20s", ” );
// write params (notice the ‘&’!)
$params = array(
‘p1′=> ‘Cesar’,
‘p2′=> &$greeting
);
// execute the PL/SQL code
$statement->execute( $params );
// display results
var_dump( $params );
To execute the code you need a database connection called $db. When you run this PHP code you will get:
array(2) {
["p1"]=>
string(5) "Cesar"
["p2"]=>
&string(12) "Hello, Cesar"
}
It is important to note a couple of things:
- In Zend Framework you must use named parameters since positional parameters are not supported when using the Oracle adapter.
- You must reserve space for our output. The best way is to create a string with enough blank spaces for the longest possible answer. You will get the following message if its too short: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- When you add output variable you must do so by reference (notice the ‘&’ sign in the $params array).
Hope this helps someone!
Sometimes when programming, you have to debug your application. Yes, even when using 
As we have often said, we are very fond of vim here at Easytech. Even though it is an incredible editor I sometimes miss some features of editors like 
Graphic designers, when creating websites, will often give programmers the Photoshop (PSD) files of the screens and, if they have enough experience designing for the web, they will ’slice’ the design. Basically what they’ll do is mark a rectangular region (a ’slice’) of the image that will then be used in the HTML of the page. Often a single page is composed of many such slices. When the designer is happy with his work he will save in one shot all the slices and provide the programmer with the slices and PSD files in case he needs to modify anything later on. If you buy templates from templating sites such as 
Entries (RSS)