oci_new_cursor

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

oci_new_cursor — Alloue et retourne un nouveau curseur Oracle

Description

oci_new_cursor(resource $connection): resource|false

Alloue un nouveau curseur Oracle sur la connexion spécifiée.

Liste de paramètres

connection: Un identifiant de connexion Oracle, retourné par la fonction oci_connect() ou la fonction oci_pconnect().

Valeurs de retour

Retourne un nouveau gestionnaire de connexion, ou false si une erreur survient.

Exemples

Exemple #1 Utiliser un REF CURSOR issu d'une procédure enregistrée

<?php
// Prérequis :
//   crée ou remplace la procédure myproc(myrc out sys_refcursor) comme ceci
//   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, -1, OCI_B_CURSOR);
oci_execute($stid);

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

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

Improve This Page

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 3 notes

down

mgumiel at mgait dot com ¶

12 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);
 ?>

down

-3

sixd at php dot net ¶

14 years ago

Oracle 11.2 introduced support for REF CURSOR prefetching

down

-4

sixd at php dot net ¶

16 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

＋add a note