Remote DBA Plans
Remote DBA Service

Oracle Ref Cursor tips

Oracle Tips by Burleson Consulting

This section is devoted to cursor handles. A cursor is an Oracle structure in which Oracle stores a parsed statement. If the cursor is opened by a parse call, like the PHP oci_parse()function, a statement handle is then available. A structure can be allocated and a SQL statement tied to it later. That structure can also be used to transfer a result set.

The Oracle name for a result set is a “cursor”. Oracle uses the name of “ref cursor” for such a type. This section will construct an example of a ref cursor. Cursors are opened by calling the oci_new_cursor() function (or by parsing a SQL statement). The syntax is extremely simple. Here it is, straight from the online manual:

DESCRIPTION

resource oci_new_cursor ( resource connection)

oci_new_cursor() allocates a new statement handle on the specified connection.

To create an example, a PL/SQL stored procedure returning a REF CURSOR type must first be created. It is assumed that the reader is familiar with PL/SQL. Those not familiar with PL/SQL can skip the rest of this section with no harm done, because PL/SQL is needed for manipulating cursors in Oracle, and without knowing PL/SQL, cursors cannot be used.

Those who need more information regarding PL/SQL can find that information in both online documentations and Scott Urman’s book named “Oracle9i PL/SQL Programming”. To keep this book as general as possible, relying on PL/SQL will be avoided wherever possible, but in this case, avoidance is not possible.

The following PL/SQL procedure is not part of the PHP/Apache combination, yet is created by the Oracle tool called SQL*Plus. Here is the source code:

Example 17

CREATE OR REPLACE PACKAGE scott.php_demo1
as
type ref_cur is ref cursor;
procedure demo1(job_title in varchar2,emps out ref_cur);
end;
/
CREATE OR REPLACE PACKAGE BODY scott.php_demo1
as
procedure demo1(job_title in varchar2,emps out ref_cur)
is
begin
open emps for select ename,empno,deptno
from emp
where job=job_title;
end;
end;
/

The code above has absolutely nothing to do with PHP. It is pure and unadulterated Oracle PL/SQL. This example will be revisited in Chapter 5.

Essentially, this defines a package PHP_DEMO1 which has a single type definition and a procedure DEMO1. The procedure DEMO1 takes two arguments; one input and one output. The input argument is a job title and the output argument is a statement handle for a SQL statement modified by the first argument (job title).

The following is a PHP script for this occasion of the command line variety:

Example 17 (Cont.)

#!/usr/local/bin/php
<?php
    $title="CLERK";
    $db=oci_new_connect("scott","tiger");
    if (!$db) {
       $err =oci_error();
       die ($err['message']);
    }
    $SQL="begin
          php_demo1.demo1(:title,:csr);
          end;";
    $sth=oci_parse($db,$SQL);
    $csr=oci_new_cursor($db);
    if (!$csr) {
       $err=oci_error();
       die ($err['message']);
     }
    if (!oci_bind_by_name($sth,":title",$title,32)) {
       $err=oci_error($sth);
       die ($err['message']);
    }
    if (!oci_bind_by_name($sth,":csr",$csr,-1,OCI_B_CURSOR)) {
       $err=oci_error($sth);
       die ($err['message']);
    }
    @oci_execute($sth);
    @oci_execute($csr);
    while ($row=oci_fetch_array($csr,OCI_NUM)) {
       foreach ($row as $fld) {
           echo "$fld\t";
       }
     print "\n";
    }
    echo "This statement returned ", oci_num_rows($csr),
         " rows\n";
?>

Except for using cursor descriptors, there is not much new information in this script. The cursor variable $csr is allocated by a call to the new_oci_cursor(). Bind of the $csr variable reflects the fact that cursors need to have the data type specified as an argument to the bind call (“OCI_B_CURSOR”). Also, the size is specified as -1. The $csr variable is bound to an output argument of the DEMO1 procedure, which means that Oracle places a parsed statement into it.

Once a parsed SQL statement is received into $csr, it becomes a normal statement handle, just like $sth. Statement in $csr is just parsed, not executed, so this needs to be done manually, immediately after executing the original handle $sth. As a result, there are two open statement handles in the program. Now, the user simply fetches the result from the $csr handle and prints it on the output. The following is the output of the script:

    $ ./example17.php
    SMITH   7369    20
    ADAMS   7876    20
    JAMES   7900    30
    MILLER 7934    10

This statement returned 4 rows

The output is as expected, entirely unimpressive and does not reflect the awesome power of the combination of PL/SQL and PHP. Where does this awesome power come from? Both PL/SQL and PHP are powerful programming languages which have the full complement of exception handling, modular and object programming features. PL/SQL executes entirely within the database while PHP executes within a web server. This means that the programming tasks can be divided sharply into two parts: database manipulation parts and output parts that take care of the web appearance.

By using a bind the user can communicate all kinds of data between the two parts of the application. In order to write an optimal and appealing application, the application programmer needs to know both PHP and PL/SQL. It is strongly recommended for readers who have not yet acquired programming skills in PL/SQL to do so as soon as possible.

SEE CODE DEPOT FOR FULL SCRIPTS