The Oracle + PHP Cookbook: Calling Oracle Stored Procedures with PHP

If you consider the code normally required to manage a many-to-many relationship between tables, performing an update to existing data can often involve three separate queries. By encapsulating that process in a single stored procedure, traffic between the client and database is reduced while an operation that would normally require multiple steps in the client code is reduced to a single database call.

The PHP OCI8 extension provides support for calling stored procedures, allowing you to bind parameters to a procedure statement in the same way as you would to a normal SQL statement, as well as access to result cursors and Oracle collections. This HowTo provides examples of common operations with stored procedures.

Stored Procedure Input and Output

When calling an Oracle stored procedure, all input and output data is passed as arguments to the procedure. This may be confusing as first, if you are used to the semantics of calling a PHP function with some arguments and having it return a value, but is easiest seen by example. Given the following stored procedure signature:

When calling this procedure, the first argument name would contain an input value you supply at call-time, while greeting would be populated by the procedure as a "returned" value for use after the procedure has completed.

Reading the Specification

Although a discussion of PL/SQL programming is beyond the scope of this HowTo, deep knowledge of PL/SQL is unecessary as long as you are roughly familiar with how a stored procedure looks and can read the interface specification.

When looking the source of a stored procedure, it will begin by defining the parameters it accepts, for example:

 
 PROCEDURE edit_entry(
 status_out OUT NUMBER,
 status_msg_out OUT VARCHAR2,
 id_inout IN OUT INTEGER,
 title_in IN VARCHAR2,
 text_out OUT CLOB,
 categories_in IN list_of_numbers
 );
 

The name of this procedure is edit_entry. Within the parentheses is defined the list of arguments you can pass to the procedure, separated by commas. For each parameter in the list, you will see the name used to reference its value within the procedure itself (you do not need to use the same names in your PHP script), the mode of the parameter (see below) and the type of the parameter.

So for the first parameter in this example:

The internal name is status_out, the mode is OUT, and the type is NUMBER, which is a native Oracle data type.

Later in the script you'll see the id_inout parameter.

which has the mode IN OUT and type INTEGER.

At the end of the list is the categories_in parameter:

Here the type is user defined (more on that type later).

Parameter Modes

The mode of the parameter describes the direction of "flow" of data from the caller to the procedure:

• IN—parameters with this mode are supplied by the caller.
• OUT—the parameter may be assigned a value by the procedure and returned to the caller.
• IN OUT—the parameter can be used in both "directions"; that is, the caller may provide a value for this parameter and the procedure may also modify the value of the parameter.

Parameters are Not Optional.

When a procedure is called from PHP, you must bind a PHP variable to all the parameters it defines. You may not have to assign values to the PHP variables, even if they are input parameters—if no value is assigned to a scalar type, Oracle will regard it as a NULL value.

It's worth noting that stored procedures can be "overloaded" in Oracle. In other words, there may be two procedures with the same name but with different parameter signatures. Which one is called will depend on the number and types of the parameters you bind to in PHP.

Complex Types

The parameters used by a stored procedure are not limited only to scalar types such as VARCHAR2 and INTEGER. It's also possible to pass and receive complex data types, such as a list of values or a result cursor corresponding to the set of rows selected from a table.

In general, you will typically receive cursors back from a stored procedure, if there are rows of data to iterate over, while if you need to pass a list of values in, you would typically use a collection. The examples below illustrate these in PHP.

Invoker vs. Definer Rights.

Oracle makes a distinction between "invokers" (a user executing a stored procedure) and "definers" (the user under which the CREATE PROCEDURE statement was issued).

By default, stored procedures are executed with the rights of the definer, even when the invoker is a different user. That means all access to tables, for example, within the procedure will be controller by the rights of the definer so an invoker only needs rights to execute the procedure, not rights to the tables it uses.

This model can be changed with the keywords AUTHID CURRENT_USER as part of the procedure definition. With this directive set, rights required when executing a stored procedure are resolved at runtime against the current user executing the procedure.

One possible use for this approach is to test a procedure that modifies data in a table without actually modifying live data. In this case the invoking user defines a table in their own schema with the same name as a that accessed from within a procedure they want to execute, and the procedure acts against the local table rather than the one available to the definer.

Calling Stored Procedures from PHP

In terms of the SQL statement you would execute from PHP to call a procedure, you will typically nest the call within an Oracle BEGIN ... END; block, known as an anonymous block. For example:

 
 <?php
 // etc.
 
 $sql = 'BEGIN sayHello(:name, :message); END;';
 ?>
 

You then bind the parameters to PHP variables with calls to oci_bind_by_name() .

If the sayHello procedure was defined by the following DDL statement:

 
 CREATE OR REPLACE PROCEDURE 
 sayHello (name IN VARCHAR2, greeting OUT VARCHAR2) 
 AS
 BEGIN
 greeting := 'Hello ' || name;
 END;
 /
 

Note that you can run the above statement using the SQL*Plus command line. Save the statement to a file (SAYHELLO.SQL). Next, login with SQL*Plus:

Then create the procedure using the START command:

The following PHP script calls the procedure:

 
 <?php
 $conn = oci_connect('SCOTT','TIGER') or die;
 
 
 $sql = 'BEGIN sayHello(:name, :message); END;';
 
 $stmt = oci_parse($conn,$sql);
 
 // Bind the input parameter
 oci_bind_by_name($stmt,':name',$name,32);
 
 // Bind the output parameter
 oci_bind_by_name($stmt,':message',$message,32);
 
 // Assign a value to the input 
 $name = 'Harry';
 
 oci_execute($stmt);
 
 // $message is now populated with the output value
 print "$message\n";
 ?>
 

Example Package Blog.

To illustrate some of the trickier aspects of calling stored procedures, here you'll use the following package, called blog, which provides an API for fetching and modifying entries in a hypothetical blogging application. Packages are a way to encapsulate procedures, functions, and data inside their own namespace with their own scope, keeping them isolated from other procedures in the global database namespace. When calling a procedure in a package, a period is used to separate package name from procedure name.

The blog package is specified with:

 
 CREATE OR REPLACE PACKAGE blog AS
 
 TYPE cursorType IS REF CURSOR RETURN blogs%ROWTYPE;
 
 /*
 Fetch the latest num_entries_in from the blogs table, populating
 entries_cursor_out with the result
 */
 PROCEDURE latest(
 num_entries_in IN NUMBER,
 entries_cursor_out OUT cursorType
 );
 
 /*
 Edit a blog entry. If id_inout is NULL, results in an INSERT, otherwise
 attempts to UPDATE the existing blog entry. status_out will have the value
 1 on success, otherwise a negative number on failure with status_msg_out
 containing a description
 categories_in is a collection where list_of_numbers is described by
 TYPE list_of_numbers AS VARRAY(50) OF NUMBER;
 */
 PROCEDURE edit_entry(
 status_out OUT NUMBER,
 status_msg_out OUT VARCHAR2,
 id_inout IN OUT INTEGER,
 title_in IN VARCHAR2,
 text_out OUT CLOB,
 categories_in IN list_of_numbers
 );
 
 END blog;
 /
 

The package provides two procedures: blog.latest, which returns a result cursor containing the most recent num_entries blog entries; and blog.edit_entry, which allows new blog entries to be inserted as well as modification of existing blog entries. If a value is provided for the id_inout parameter, the procedure attempts to update the corresponding blog entry with that id. Otherwise it will insert a new blog entry and populate id_inout with the new row's primary key. This procedure also accepts a CLOB object, corresponding to the body of the blog entry and a collection object corresponding to a list of categories the entry should be filed under. The collection type list_of_numbers referenced here is defined by:

The body of the package is shown below. The comments should give you an idea of what it does without needing a deep understanding of PL/SQL:

 
 CREATE OR REPLACE PACKAGE BODY blog AS
 
 /*------------------------------------------------*/
 PROCEDURE latest(
 num_entries_in IN NUMBER,
 entries_cursor_out OUT cursorType
 ) AS
 
 BEGIN
 
 OPEN entries_cursor_out FOR
SELECT * FROM blogs WHERE rownum < num_entries_in
ORDER BY date_published DESC;

END latest;

/*------------------------------------------------*/
PROCEDURE edit_entry(
status_out OUT NUMBER,
status_msg_out OUT VARCHAR2,
id_inout IN OUT INTEGER,
title_in IN VARCHAR2,
text_out OUT CLOB,
categories_in IN list_of_numbers
) AS

ENTRY_NOT_FOUND EXCEPTION;
entry_found INTEGER := 0;

BEGIN

/* Default status to success */
status_out := 1;

/* If id_inout has a value then attempt to UPDATE */
IF id_inout IS NOT NULL THEN

/* Check the id exists - raise ENTRY_NOT_FOUND if not */
SELECT COUNT(*) INTO entry_found
FROM blogs b WHERE b.id = id_inout;
IF entry_found != 1 THEN RAISE ENTRY_NOT_FOUND; END IF;

/* Update the blogs table returning the CLOB field */
UPDATE blogs b SET b.title = title_in, b.text = EMPTY_CLOB()
WHERE b.id = id_inout RETURNING b.text INTO text_out;

/* Remove any existing relationships to categories
- new categories inserted below */
DELETE FROM blogs_to_categories WHERE blog_id = id_inout;

status_msg_out := 'Blog entry ' || id_inout || ' updated';

/* id_inout was null so INSERT new record */
ELSE

INSERT INTO blogs b ( b.id, b.title, b.date_published, b.text )
VALUES ( blog_id_seq.nextval, title_in, SYSDATE, EMPTY_CLOB() )
RETURNING b.id, b.text INTO id_inout, text_out;

status_msg_out := 'Blog entry ' || id_inout || ' inserted';

END IF;

/* Now handle assignment to categories.
Loop over the categories_in collection,
inserting the new category assignments */
FOR i IN 1 .. categories_in.count
LOOP
INSERT INTO blogs_to_categories (blog_id,category_id)
VALUES (id_inout,categories_in(i));
END LOOP;

status_msg_out := status_msg_out || ' - added to '
|| categories_in.count || ' categories';

EXCEPTION
/* Catch the exception when id_inout not found */
WHEN ENTRY_NOT_FOUND THEN
status_out := -1001;
status_msg_out := 'No entry found in table blogs with id = '
|| id_inout;
/* Catch any other exceptions raised by Oracle */
WHEN OTHERS THEN
status_out := -1;
status_msg_out := 'Error: ' || TO_CHAR (SQLCODE) || SQLERRM;

END edit_entry;

END blog;
/
 

The underlying table structure the procedures are using is:

 
 
 CREATE SEQUENCE blog_id_seq
 INCREMENT BY 1;
 /
 CREATE TABLE blogs (
 id NUMBER PRIMARY KEY,
 title VARCHAR2(200),
 date_published DATE,
 text CLOB
 );
 /
 CREATE SEQUENCE category_id_seq
 INCREMENT BY 1;
 
 CREATE TABLE categories (
 id NUMBER PRIMARY KEY,
 name VARCHAR2(30) UNIQUE
 );
 /
 CREATE TABLE blogs_to_categories (
 blog_id INTEGER NOT NULL
 REFERENCES blogs(id),
 category_id INTEGER NOT NULL
 REFERENCES categories(id),
 PRIMARY KEY (blog_id, category_id)
 );
 /
 

Stored Procedures and Reference Cursors

Looking at the blog.latest procedure, you'll see it returns a reference cursor for iterating over the row in my blogs table.

To work with a cursor in PHP two additional steps are required, as compared to accessing rows directly from a SELECT statement. The first step is preparing a cursor resource in PHP, using the oci_new_cursor() function, which you then use to bind to the appropriate parameter. The second step, after you have executed the SQL statement, is calling oci_execute() on the cursor resource.

The following PHP script illustrates this procedure:

 
 <?php
 $conn = oci_connect('SCOTT','TIGER') or die;
 
 
 $sql = 'BEGIN blog.latest(:num_entries, :blog_entries); END;';
 
 $stmt = oci_parse($conn, $sql);
 
 // Bind the input num_entries argument to the $max_entries PHP variable
 $max_entries = 5;
 oci_bind_by_name($stmt,":num_entries",$max_entries,32);
 
 // Create a new cursor resource
 $blog_entries = oci_new_cursor($conn);
 
 // Bind the cursor resource to the Oracle argument
 oci_bind_by_name($stmt,":blog_entries",$blog_entries,-1,OCI_B_CURSOR);
 
 // Execute the statement
 oci_execute($stmt);
 
 // Execute the cursor
 oci_execute($blog_entries);
 
 print "The $max_entries most recent blog entries\n";
 
 // Use OCIFetchinto in the same way as you would with SELECT
 while ($entry = oci_fetch_assoc($blog_entries, OCI_RETURN_LOBS )) {
 print_r($entry);
 }
 ?>
 

Stored Procedures and LOBs

Oracle long objects can be passed to and from stored procedures in pretty much the same way as you would with native SQL.

The following example demonstrates a call to the blog.edit_entry procedure using a CLOB. In this example no value is assigned to the id parameter so it will correspond to inserting a new blog entry:

 
 
 <?php
 $conn = oci_connect('SCOTT','TIGER') or die;
 
 $sql = 'BEGIN blog.edit_entry(:status, :status_msg, :id, :title, :text, :categories); END;';
 
 $stmt = oci_parse($conn,$sql);
 
 $title = 'This is a test entry';
 
 oci_bind_by_name($stmt,":status",$status,32);
 oci_bind_by_name($stmt,":status_msg",$status_msg,500);
 oci_bind_by_name($stmt,":id",$id,32);
 oci_bind_by_name($stmt,":title",$title,200);
 
 // Explained in the next example... (use an empty value for now)
 $Categories = oci_new_collection($conn,'LIST_OF_NUMBERS');
 oci_bind_by_name($stmt,':categories',$Categories,32,OCI_B_SQLT_NTY);
 
 // Create a new lob descriptor object
 $textLob = oci_new_descriptor($conn, OCI_D_LOB);
 
 // Bind it to the parameter
 oci_bind_by_name($stmt, ":text", $textLob, -1, OCI_B_CLOB);
 
 // Execute the statement but do not commit
 oci_execute($stmt, OCI_DEFAULT);
 
 // The status parameter will be negative if the procedure encountered a problem
 if ( !$status ) {
 // Rollback the procedure
 oci_rollback($conn);
 die ("$status_msg\n");
 }
 
 // Save the body of the blog entry to the CLOB
 if ( !$textLob->save('This is the body of the test entry') ) {
 // Rollback the procedure
 oci_rollback($conn);
 die ("Error saving lob\n");
 }
 
 // Everything OK so commit
 oci_commit($conn);
 print $status_msg."\n";
 ?>
 

As this script illustrates, a key issue is how transactions should be handled when dealing with LOBs. Here you have chosen to delegate all transaction handling to the PHP script, as updating a LOB is a two-stage process.

Note that, by default, Oracle allows you to have only one transaction running at a time in any given session. That means commit or rollback statements issued within a procedure you are calling from PHP will override calls you make to oci_commit() or oci_rollback() . You can change this behavior using autonomous transactions, enabled with the pragma PRAGMA AUTONOMOUS_TRANSACTION placed inside a procedure definition. You might use autonomous transactions, for example, as part of a logging package that you call from other procedures; this approach would enable you to log information about stored procedure calls without interfering with transactions running within a session.

Stored Procedures and Collections

Collections provide a mechanism to pass a complex data type into a stored procedure. In your blogging application, a blog entry can be filed under multiple categories, corresponding to the many-to-many relationship between the "blogs" and "categories" tables.

A collection type in Oracle must be defined globally in the database, and in this example, you are using the following definition:

which allows you to assign a blog entry to a maximum of 50 categories in one go, by passing an instance of this type to the blog.edit_entry procedure.

In PHP, a collection is represented by the pre-defined PHP class OCI-Collection. Instances of this class are created by calling the oci_new_collection() function. An OCI-Collection object provides the following methods:

• append : pushes an element onto the end of the collection
• assign : adds an element to a collection from an existing collection
• free : free up an resources associated to the collection handle
• getElem : retrieves an element from a particular index location in the collection
• max : returns the maximum number of elements in the collection
• size : returns the current size of the collection
• trim : removes a number of elements from the end of the collection

 
 <?php
 $conn = oci_connect('SCOTT','TIGER') or die;
 
 
 $sql = 'BEGIN blog.edit_entry(:status, :status_msg, :id, :title, :text, :categories); END;';
 
 $stmt = oci_parse($conn, $sql);
 
 $id = 1; // ID of the new entry
 $title = 'This is a test entry (v2)';
 
 oci_bind_by_name($stmt,":status",$status,32);
 oci_bind_by_name($stmt,":status_msg",$status_msg,500);
 oci_bind_by_name($stmt,":id",$id,32);
 oci_bind_by_name($stmt,":title",$title,200);
 $textLob = oci_new_descriptor($conn, OCI_D_LOB);
 oci_bind_by_name($stmt, ":text", $textLob, -1, OCI_B_CLOB);
 
 // Create an OCI-Collection object
 $Categories = oci_new_collection($conn,'LIST_OF_NUMBERS');
 
 // Append some category IDs to the collection;
 $Categories->append(2);
 $Categories->append(4);
 $Categories->append(5);
 
 // Bind the collection to the parameter
 oci_bind_by_name($stmt,':categories',$Categories,-1,OCI_B_SQLT_NTY);
 
 oci_execute($stmt, OCI_DEFAULT);
 
 if ( !$status ) {
 oci_rollback($conn);
 die ("$status_msg\n");
 }
 
 if ( !$textLob->save('This is the body of the test entry [v2]') ) {
 oci_rollback($conn);
 die ("Error saving lob\n");
 }
 
 oci_commit($conn);
 print $status_msg."\n";
 ?>
 
 

Conclusion

You have now seen examples of how to call stored procedures from PHP, both with simple procedures involving only scalar data types and more complex procedures using LOBs, cursors, and collections. You should also have enough understanding of stored procedure definitions to be able to read their PL/SQL specification, enabling you to call them correctly from PHP and bind the correct types.

Harry Fuecks [ http://www.phppatterns.com] is a well known PHP developer and writer, since discovering PHP in 1999. He has published numerous introductory and intermediate PHP articles via the Sitepoint. Web developer's network, as well as writing The PHP Anthology (SitePoint).