storedproc_empt1_proc1.htm
Objectives:
  • Use of Oracle  SYS_REFCURSOR as a parameter of stored procedure.
  • This procedure is a stand-alone and not enveloped in a package, ( I would compare this document with PostgreSQL  ).
  • Special Notes:
    • // get coulmn heading
      $sql1 = "SELECT e.eid eid,e.info.fname fname,e.info.lname lname,
      e.info.address addr FROM empt1 e";
      $stmt1 =oci_parse($conn, $sql1);
      oci_execute($stmt1);
    • $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);
  • Intranet link: RunAS

    obtype_empt1_proc1.txt

Please refer these web-pages:
  • Create_ObjectType_EmpT1.htm
  • preface_4. htm

 

SQL* Plus information of the table "empt1"

php script

<?php
//obtype_empt1_proc1.php
$conn = oci_connect('scott', 'Son', 'localhost/orcl.gateway.2wire.net');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
echo" DB connected: querying a table <br/>";
// Excute the call to the PL/SQL stored procedure
// get coulmn heading
$sql1 = "SELECT e.eid eid,e.info.fname fname,e.info.lname lname,
e.info.address addr FROM empt1 e";
$stmt1 =oci_parse($conn, $sql1);
oci_execute($stmt1);
echo "uses oci_bind_name </br/>". "call emp_proc1(:rc);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);
$nrows = "";
//
$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";
echo " no of rows : ". $nrows;
oci_free_statement($stmt);
oci_close($conn);
echo "<br/> Oracle db connection closed";

?>

Runtime view:

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