empt1func_sysrefcursor1

empt1func_sysrefcursor1.htm :: obtype_empt1_proc1.php

Objective :

Creating user defined Type, inherited as a table.
Differences between a stored FUNCTION and a PROCEDURE
- A function uses return clause to return records to a client.
Creating a Function which returned a SYS_REFCURSOR
Creating a Stored Procedure which OUTPUT a SYS_REFCURSOR parameter.

Intranet: http://manas8x/BareBone_PHP/oop/obtype_empt1_proc1.php

PHP Source Scipt: obtype_empt1_proc1.txt, obtype_empt1func.txt

Pre-created

Creating User defined Type : object

Creating a table from the object defined earlier.

Insert some rows

INSERT INTO empT1
VALUES (10,empOBJ( 'Anita','Das', 'Address 1'));
INSERT INTO empT1
VALUES (11, empOBJ('Frank','Delon', 'Address 2'));
INSERT INTO empT1
VALUES (12, empOBJ('Juno','Rastogi', 'Address 3'));
INSERT INTO empT1
VALUES (13, empOBJ('Sunil','Pandey', 'Address 4'));
COMMIT;

Start Coding

Create a stand alone function : which returns a SYS_REFCURSOR

create or replace
PROCEDURE empt1_proc1
(PO_REF_CURSOR IN OUT SYS_REFCURSOR) AS
BEGIN
OPEN PO_REF_CURSOR FOR -- Opens ref cursor for query
SELECT e.eid, e.info.fname,
e.info.lname, e.info.address FROM empt1 e;
END;

Create a stand alone stored procedure under Scott's account, with SYS_REFCURSOR as a parameter.

create or replace
PROCEDURE empt1_proc1
(PO_REF_CURSOR IN OUT SYS_REFCURSOR) AS
BEGIN
OPEN PO_REF_CURSOR FOR -- Opens ref cursor for query
SELECT e.eid, e.info.fname,
e.info.lname, e.info.address FROM empt1 e;
END;

Runtime View : Stored function

PHP Script that fetched the stored function "empt1func" which returned a SYS_REFCURSOR

$nrows = "";
//
$stmt = oci_parse($conn, "begin :rc:= empt1func();end;");
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stmt, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stmt);
// Execute and fetch from the cursor
oci_execute($refcur); // treat as a statement resource
echo "<table border='1'>\n";
while($row = oci_fetch_array($refcur, OCI_ASSOC)) {
echo "<tr>";
$nrows++;
foreach ($row as $conn) {
echo "<td>$conn</td>";
}
echo "</tr>\n";
}
echo "</table>\n";
echo " no of rows : ". $nrows;

Runtime view: Stored procedure

PHP

//Calli
$stmt = oci_parse($conn, "call empt1_proc1(:rc)");
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stmt, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stmt);
// Execute and fetch from the cursor
oci_execute($refcur); // treat as a statement resource
echo "<table border='1'>\n";
while($row = oci_fetch_array($refcur, OCI_ASSOC)) {
echo "<tr>";
$nrows++;
foreach ($row as $conn) {
echo "<td>$conn</td>";
}
echo "</tr>\n";
}
echo "</table>\n";