PHP 7.0.6 Released

oci_new_cursor

(PHP 5, PHP 7, PECL OCI8 >= 1.1.0)

oci_new_cursorAllocates and returns a new cursor (statement handle)

Description

resource oci_new_cursor ( resource $connection )

Allocates a new statement handle on the specified connection.

Parameters

connection

An Oracle connection identifier, returned by oci_connect() or oci_pconnect().

Return Values

Returns a new statement handle, or FALSE on error.

Examples

Example #1 Binding a REF CURSOR in an Oracle stored procedure call

<?php

// Precreate:
//   create or replace procedure myproc(myrc out sys_refcursor) as
//   begin
//     open myrc for select first_name from employees;
//   end;

$conn oci_connect("hr""hrpwd""localhost/XE");
if (!
$conn) {
    
$m oci_error();
    
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$curs oci_new_cursor($conn);
$stid oci_parse($conn"begin myproc(:cursbv); end;");
oci_bind_by_name($stid":cursbv"$curs, -1OCI_B_CURSOR);
oci_execute($stid);

oci_execute($curs);  // Execute the REF CURSOR like a normal statement id
while (($row oci_fetch_array($cursOCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo 
$row['FIRST_NAME'] . "<br />\n";
}

oci_free_statement($stid);
oci_free_statement($curs);
oci_close($conn);

?>

Notes

Note:

In PHP versions before 5.0.0 you must use ocinewcursor() instead. This name still can be used, it was left as alias of oci_new_cursor() for downwards compatability. This, however, is deprecated and not recommended.

User Contributed Notes

mgumiel at mgait dot com
3 years ago
Some packages in oracle are functions, and that functions returns a cursor.

For example:

CREATE FUNCTION F_Function( p1 char(2), p2 int)
  RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR SELECT * FROM allitems
                           WHERE (cod=p1)
                                      AND (Number=p2);
  RETURN my_cursor;
END F_Function;

Here is the code that allows to obtain data from a function that returns a cursor.

<pre>
<?php
$conn
=oci_connect("server", "user", "pass");
if (!
$conn) {
   
$e = oci_error();
   
trigger_error(htmlentities($e['message']), E_USER_ERROR);
}

   
//You must asign before.
   
$p1 = '03';
   
$p2 = 2012016191;

   
$stid = oci_parse($conn, 'begin :cursor := server.PKG_package.F_Function(:p1,:p2); end;');
   
$p_cursor = oci_new_cursor($conn);

   
//Send parameters variable  value  lenght
   
oci_bind_by_name($stid, ':p1', $p1,2);
   
oci_bind_by_name($stid, ':p2', $p2,10);

   
//Bind Cursor     put -1
   
oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);

   
// Execute Statement
   
oci_execute($stid);
   
oci_execute($p_cursor, OCI_DEFAULT);

   
oci_fetch_all($p_cursor, $cursor, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    echo
'<br>';
   
print_r($cursor);
?>
sixd at php dot net
7 years ago
Because OCI8 uses "prefetching" to greatly improve returning query results, but Oracle doesn't support prefetching for REF CURSORs, application performance using REF CURSORs can be greatly improved by writing a PL/SQL function that pulls data from the REF CURSOR and PIPEs the output. The new function can be queried in a SELECT as if it were a table.  See http://blogs.oracle.com/opal/2008/11/
converting_ref_cursor_to_pipe.html
sixd at php dot net
6 years ago
Oracle 11.2 introduced support for REF CURSOR prefetching
To Top