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 :
|
|
|
|
|
|