http://sqlquery.com/php_oracle_ref_cursor.htm

Calling an Oracle Stored Procedure that returns a “Ref Cursor” in PHP

By Richard Murnane

 

There is no easier way to develop dynamic (data driven) web applications then PHP.  PHP applications are great for Proof of Concept type applications and other applications where usage would be considered lite.  PHP allows a developer to make OCI Calls to an Oracle database to fetch data via “embedded” queries.  Although embedding queries in PHP applications is available, a more secure and robust option is to build an Oracle PL/SQL Stored procedure that returns a result set (known as a “ref cursor”).  As you can see from the below example, doing this is quite easy.  One of the benefits of doing this is that if your front-end technology should change from PHP to a language like Java, you would only change the “User Inphase” portion and not your “data access” portion of your application.

The PL/SQL stored procedure that returns the result set.

The below sp_return_cursor stored procedure embedded in the PL/SQL package php_cursor_pkg is an example of a PL/SQL stored procedure that returns a “ref cursor”.  It also shows you how to use “bind variables” while using Dynamic SQL statements (note the “execute immediate” statement).  I compile the package into the schema named “rich” for this article.

--php_cursor_pkg.sql

CREATE OR REPLACE PACKAGE php_cursor_pkg

AS

 

TYPE t_RefCur IS REF CURSOR;

PROCEDURE sp_return_cursor(

    p_owner                 all_tables.owner%TYPE,

    p_RefCur         IN OUT t_RefCur);

END php_cursor_pkg;

/

show errors

 

CREATE OR REPLACE PACKAGE BODY php_cursor_pkg AS

 

PROCEDURE sp_return_cursor(

    p_owner                 all_tables.owner%TYPE,

    p_RefCur         IN OUT t_RefCur)

IS

    v_ReturnCursor          t_RefCur;

    v_sql                   VARCHAR2(500);

BEGIN

 

    v_sql := 'SELECT * FROM all_tables';

    v_sql := v_sql||' WHERE owner = :m';

 

    -- Open the cursor variable.

    OPEN v_ReturnCursor FOR v_sql USING p_owner;

    -- return  the cursor variable.

    p_RefCur := v_ReturnCursor;

 

END sp_return_cursor;

 

END php_cursor_pkg;

/

show errors

The PHP page that calls the Oracle stored procedure.

Below you will find the code for 2 PHP pages.  The first named dbinfo.php is a php page that will house your database connection information.  The second is the actual PHP page that invokes the stored procedure.


dbinfo.php

<?php

$db_user = "rich";

$db_pwd = "oracle_password_goes_here";

$db_sid = "oracle_sid_goes_here";

?>

 

php_cursor.php         

<?php  

// the stored procedure php_cursor_pkg.sp_return_cursor returns a ref cursor in :data

 

include ("dbinfo.php");

 

$db_conn = ocilogon( "$db_user", "$db_pwd", "$db_sid" );

$curs = OCINewCursor($db_conn);

$stmt = OCIParse($db_conn,"begin php_cursor_pkg.sp_return_cursor('RICH', :data); end;");

ocibindbyname($stmt,"data",&$curs,-1,OCI_B_CURSOR);

ociexecute($stmt);

ociexecute($curs);

print "<HR>";

print "<TABLE BORDER=\"1\">";

print "<TR>";

print "<TH>OWNER</TH>";

print "<TH>TABLE_NAME</TH>";

print "<TH>TABLESPACE_NAME</TH>";

print "</TR>";

while (OCIFetchInto($curs,&$data,OCI_ASSOC)) {

    print "<TR>";

    $owner  = $data["OWNER"];

    $table_name = $data["TABLE_NAME"];

    $tablespace_name = $data["TABLESPACE_NAME"];

    print "<TD>$owner</TD>";

    print "<TD>$table_name</TD>";

    print "<TD>$tablespace_name</TD>";

    print "</TR>";

}

print "</TABLE>";

print "</TR>";

print "</BODY></HTML>";

OCIFreeStatement($stmt);

OCIFreeCursor($curs);

OCILogoff($db_conn);

?> 

 

 

The Results!

OWNER

TABLE_NAME

TABLESPACE_NAME

RICH

BOM_MASTER

TOOLS

RICH

ERROR_LOG

TOOLS

RICH

PLAN_TABLE

USERS