BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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


The above book excerpt is from  "Easy Oracle PHP: Creating Dynamic Web Pages with Oracle Data". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_2005_2_php_oracle.htm

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter