Wednesday, January 28, 2009

Calling oracle Procedure which return recordsets from PHP

Here is an example of how to fetch records from oracle procedure using cursor which returns a recordset and read it from PHP.
Please create a procedure to get values from a table region_master.

CREATE OR REPLACE PROCEDURE "GETREGIONLISTBYNAMEANDDESC"(
        RegionName In Region_MASTER.region_name%TYPE,
        RegionDesc In Region_MASTER.region_desc%TYPE,
        ResultSet OUT sys_refcursor)
IS
begin
OPEN ResultSet for
    SELECT region_id,region_code,region_name,region_desc,updated_dt FROM Region_MASTER WHERE region_name LIKE '%'|| RegionName || '%'
    AND region_desc LIKE '%' || RegionDesc || '%';
END;

To fetch the results from php write the following code in PHP to get the values.


$curs = OCINewCursor($varOrConnection);
$sqlAssetData = "BEGIN CPM.GETREGIONLISTBYNAMEANDDESC( :regname, :regdesc, :ResultSet); END;";
$resAssetData = ociparse($varOrConnection, $sqlAssetData) or die ('Can not Execute statment');
ocibindbyname($resAssetData, ":regname", &$regname,32);
ocibindbyname($resAssetData, ":regdesc", &$regdesc,32);
ocibindbyname($resAssetData, ":ResultSet", &$curs, -1, OCI_B_CURSOR);                                                                         
ociexecute($resAssetData) or die ('Can not Execute statment');
ociexecute($curs);                                                                                                                            
while (OCIFetchInto($curs,&$ResultSet)) {                                                                                                     
   echo "
";
    print_r($ResultSet); 
   echo "";
}

No comments: