Category Archives: Oracle

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;

Continue reading

Advertisements