Executing PL/SQL code in Zend Framework

ZF
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:

CREATE OR REPLACE package test_pk AS
  /* 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:

<?php

  // 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:

  1. In Zend Framework you must use named parameters since positional parameters are not supported when using the Oracle adapter.
  2. 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
  3. When you add output variable you must do so by reference (notice the ‘&’ sign in the $params array).

Hope this helps someone!

Stop SOPA!

SOPA breaks our internet freedom!
Any site can be shut down whether or not we've done anything wrong.

Stop SOPA!