Objective: creating stored function and cursor
  • how to call a function "begin :rc:=empfunc1(:ejob);end;"

http://manas8x/BareBone_PHP/cursor_stored_function1.php

A PL/SQL function that returns a REF CURSOR can be called in an anonymous block like used previously, or be used as a query column.

For example:
create or replace function selectme(p1 VARCHAR2) return sys_refcursor is
rc_l sys_refcursor;

CODE :

create or replace
FUNCTION empfunc1(p1 IN Varchar2) RETURN
SYS_REFCURSOR AS
rc SYS_REFCURSOR;
BEGIN
OPEN rc FOR SELECT * FROM emp WHERE job !=p1;
RETURN rc;
END;
 
WEB-PAGE SCRIPT:

<html>
<head>
<title> cursor_stored function1.php</title>
<style type="text/css">
html, body { height: 100%; }
.TB2 { border:2px solid navy; color:green; }
.TB1 { border:2px solid green;color: navy;
vertical-align:text-top;width:300px; height:100px;}
.sp1 { width: 450px; height:100px; overflow:auto;color:navy;}
.td1 { width: 65px; height: 10px;background-color:#b0c4de;}
.td2 { width: 65px; height: 10px; font-size:10px;}
</style>
<body>
<?php
echo "Oracle stored procedure and SYS_REFCURSOR <br/>";
//
$conn = oci_connect('scott', 'Son', 'localhost/orcl.gateway.2wire.net');
// Excute the call to the PL/SQL stored procedure
$sql = "select * from emp";
$stmt1 =oci_parse($conn, $sql);
oci_execute($stmt1);
echo "uses oci_bind_name </br/>". "begin :rc:=empfunc1(:ejob);end;";
// HEADER TABLE
echo "<table border=\"1\">";
echo "<tr>";
$ncols = oci_num_fields($stmt1);
for ($i = 1; $i <= $ncols; $i++) {
$column_name = oci_field_name($stmt1, $i);
echo "<td class='td1'>$column_name</td>";
}
//
echo "</tr></table>\n";
oci_free_statement($stmt1);
//

$refcur = oci_new_cursor($conn);
$stmt = oci_parse($conn, "begin :rc:=empfunc1(:ejob);end;");
oci_bind_by_name($stmt, ":rc",$refcur, -1, OCI_B_CURSOR);
$ejob = 'PRESIDENT';// WATCH spelling
oci_bind_by_name($stmt,'ejob',$ejob);
oci_execute($stmt);
// Execute and fetch from the cursor
oci_execute($refcur); // treat the returned cursor as an OCI8 statement resource
//report
$nrows ="";
echo "<table border='1'>\n";
while($row = oci_fetch_array($refcur, OCI_ASSOC+OCI_RETURN_NULLS)) {
//retun nulls was needed to avoid slide over
echo "<tr>";$nrows++;
foreach ($row as $item) {
echo "<td class='td2'>". htmlentities($item) . "</td>";
}
echo "</tr>\n";
}
echo "</table>\n";
//
echo " no of rows : ". $nrows;
oci_free_statement($stmt);
oci_close($conn);
echo "<br/> Oracle db connection closed";
//
?>
</body>
</html>
 
Runtime display