Create_ObjectType_EmpT1S.htm
Objectives : (archived article : a simple preface document of using packages and ref-cursor )

This document was created to supplement (Create_ObjectType_EmpT1.htm) , a simple example of creating object and a table from an object using SQL Developer tool of Oracle data base .

In this document, a stand alone procedure was created in Scott's account, and used PHP script to query the table object in Oracle DB. 

Here an example of using a SYS_REFCURSOR, is cited.

Source code for php : test_objempt2.txt

Pre-created :
---------create an object type---------------
create or replace
TYPE empOBJ AS OBJECT
(
fname VARCHAR(20),
lname VARCHAR(30),
address VARCHAR(100)
);
----- create an instance/object as of table (composite type)--------

Create a procedure and ref system cursor

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;

 

PHP Script:


<?php
//test_objempt2.php
// Pre - create:
// --empt1_proc1.sql
//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;
echo " test_objempt2.php <br/> ";
$conn = oci_connect('scott', 'Son', 'localhost/orcl.gateway.2wire.net');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
echo "connected to db";
$curs = oci_new_cursor($conn);
$stid = oci_parse($conn, "begin empt1_proc1(:curs);end;");
oci_bind_by_name($stid, ":curs", $curs, -1, OCI_B_CURSOR);
oci_execute($stid);
oci_execute($curs); // Execute the REF CURSOR like a normal statement id
echo "<br/> parsed and executing <br/>";
while($row = oci_fetch_row($curs))
{
var_dump($row);
echo "<br/>";
}
oci_free_statement($stid);
oci_free_statement($curs);
oci_close($conn);
?>

var_dump :