Returning Recordset From Oracle Function Under Package Using PHP

Brief

This post shows example of returning recordset from oracle function. The oracle function is stored under Packages folder.
Oracle used version is 11g Express Edition.
This is a simple example. I have a ItemMaster table and then return its records by oracle function and view it on web page.

Example

ItemMaster Table Creation SQL:

CREATE TABLE "HR"."ITEMMASTER" 
   (	"ITEMCODE" VARCHAR2(15 BYTE), 
	"ITEMDESC" VARCHAR2(150 BYTE), 
	"ITEMGROUP" VARCHAR2(15 BYTE), 
	"ITEMTYPE" VARCHAR2(2 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

I used default HR sample schema.

Create a package in oracle:

create or replace PACKAGE PCKG_MASTER AS 
FUNCTION LIST_ITEMMASTER RETURN SYS_REFCURSOR;
END PCKG_MASTER;


Package body:

create or replace PACKAGE BODY PCKG_MASTER AS
 FUNCTION LIST_ITEMMASTER RETURN SYS_REFCURSOR
  AS
  rs_cursor SYS_REFCURSOR;
  BEGIN
    OPEN rs_cursor FOR
        SELECT * FROM ITEMMASTER;
    
    RETURN rs_cursor;
  END LIST_ITEMMASTER;
 END PCKG_MASTER;

Above sql package codes show that Packages consist of 2 things which are package abstraction and its body.

After you create table, inserting some data and create packages then you set up the PHP page.
First we create itemMaster.lib.php as a class file:

class itemMaster {
		private $conn;
	function __construct() {
			$this->conn = oci_connect('hr', 'hr', 'localhost/XE');
		}
	function list_itemmaster() {
		$curs = oci_new_cursor($this->conn);
			$stmt = oci_parse($this->conn, 
				"begin :ret := PCKG_MASTER.LIST_ITEMMASTER;	end;");
			oci_bind_by_name($stmt, ":ret", $curs, -1, OCI_B_CURSOR);	
			oci_execute($stmt);	
			oci_execute($curs);
			
			echo "<table border='0'>\n";
			echo "<tr><td>Item Code</td><td>Description</td><td>Group</td><td>Type</td><td></td></tr>\n";
			while ($row = oci_fetch_array($curs, OCI_ASSOC+OCI_RETURN_NULLS)) {
				echo "<tr>\n";
				foreach ($row as $item) {
					echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
				}
				echo "<td><a href=''>Edit</a> &nbsp;<a href='javascript:del_item(\"".$row["ITEMCODE"]."\")'>Delete</a></td>";
				echo "</tr>\n";
			}
			echo "</table>\n";		
			oci_free_statement($stmt);			
		}
	function dispose() {
			
			oci_close($this->conn);
		}
		
}

The itemMaster class connect to oracle database using oci_connect(..). Since on package body we use SYS_REFCURSOR so then oci_new_cursor(..) is needed.
Other syntaxes followed and there is dispose() function to close the connection.

Second we use itemMaster Class in a web:

include_once "libs/itemMaster.lib.php";

$item = new itemMaster();
$item->list_itemmaster();
$item->dispose();

The above php code will view all item in itemMaster table.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s