Parsing an Oracle Stored Procedure

<?

/* 
This is a class designed for parsing an Oracle Stored Procedure.  Most if us use Stored Procs as a kind of an Abstraction Layer between the Database and the Presentation Layers.  Writing PHP code to run a stored proc neeeds quite a bit of work.  This class is designed to make that easy for you.  It only asks that you write your procs in a way that the returned resultset is always a REF CURSOR.
At the end of the listing Ive written examples of how to use the class
*/



//*****************************************************************
//Class Definition for Parsing the Stored Procedure
//*****************************************************************

class DBProc{

//Class Variables
var $SPName = "NoName";
var $Params;
var $Values;
var $ParamCount=0;

//The Class Constructor.. takes variable parameters
function DBProc(){

	$num_args = func_num_args();

	//if number of arguments is greater than 2 .. something is wrong
	if ($num_args >2){
	  die("<B>Invalid number of Arguments</B>");
	}

	//if number of arguments is equal to 2 .. populate the respective variables
	//first argument MUST be SP Name
	//second MUST be the array containing the Param/Value Pair
	if ($num_args == 2){
	  $name = func_get_arg(0);
	  $paramVals = func_get_arg(1);

	$this->SPName = $name;

		$i = 0;
		while (list($key,$val) = each($paramVals)) {
			$this->Params[$i] = $key;
			$this->Values[$i] = $val;
			$i = $i+1;
     		} //End While
    	 }//End If

}//End Constructor



//Set the Name of the Stored Procedure
function setSPName($name){
	$this->SPName = $name;
 }



//Populate the Param Array
function setParam($param){
	$this->Params = $param;
}


//Populate the Value Array
function setValue($val){
	$this->Values = $val;
}



//Populate the Param/Value Pairs
function setParamValue($paramVals){

	$i = 0;
	while (list($key,$val) = each($paramVals)) {
		$this->Params[$i] = $key;
		$this->Values[$i] = $val;
		$i = $i+1;
	}

 }

 

//Execute the Proc
 function execute(){

  	$mySPName = $this->SPName;
 	$myParam = $this->Params;
 	$myValue = $this->Values;
 	$myParamsNo = sizeof($myParam)-1;


//Check for errors
//If No name provided for the Stored Procedure
if($mySPName == "NoName"){
	$error = 1;
	$errStr = "<B>No Stored Proc Name Provided</B>";
 }
//If every Parameter Not given a Value
if(sizeof($myParam) != sizeof($myValue)){
	$error = 1;
	$errStr .= "<BR><B>Error in Parameter/Value Pair</B>";
 }
//If an error exists .. Disp Msg and Exit
if($error == 1){
	die($errStr);
}


//Calling Login Values.. we keep the DB Login Info in a single file 
include ("dbConnect.php");

 //Create Query String
 $queryStr = "begin ".$mySPName."(";
 	for ($i=0; $i<=$myParamsNo; $i++){
     		$queryStr = $queryStr.":".$myParam[$i].",";
  	 }
 $queryStr = $queryStr.":myCurs); end;";

 	$cursor = OCINewCursor($db_id);
 	$stmt = OCIParse ($db_id, $queryStr);

 	//Bind Input Parameters
 	for($i=0; $i<=$myParamsNo; $i++){
 	   OCIBindByName($stmt, $myParam[$i], &$myValue[$i], -1);
 	 }

 	//Bind the returned cursor
 	OCIBindByName($stmt, myCurs, &$cursor, -1, OCI_B_CURSOR);

 	OCIExecute($stmt);
 	OCIExecute($cursor);

 	//Find number of columns returned and initialize array to accept data
 	$numOfColls = OCINumCols($cursor);
 	$result = array();
 	$arr_index = 0;

 	//This will step thru the data returned in the cursor
 	while(OCIFetch($cursor))
 	{
 		//Iterate thru the number of columns in the returned data creating an ASSOC 2D-array
 		for($i=1; $i<=$numOfColls; $i++){
 		    $colName = OCIColumnName($cursor, $i);
 		    $result[$arr_index][$colName] = OCIResult($cursor,$colName);
    		  }

 		//Increment the numeric value of the ASSOC array
 		$arr_index = $arr_index+1;

 	 }//End While

 	//Return the rowset as a 2D array
return $result;


 OCILogoff($db_id);
}//End Function Execute Declaration


//***********************************************************
//Function to Show SP MetaData
//***********************************************************
function show_MetaData(){

 include ("dbConnect.php");

 	$mySPName = $this->SPName;
 	$myParam = $this->Params;
 	$myValue = $this->Values;
 	$myParamsNo = sizeof($myParam)-1;


//Create Query String
$queryStr = "begin ".$mySPName."(";
for ($i=0; $i<=$myParamsNo; $i++){
    $queryStr = $queryStr.":".$myParam[$i].",";
 }
$queryStr = $queryStr.":myCurs); end;";

	$cursor = OCINewCursor($db_id);
	$stmt = OCIParse ($db_id, $queryStr);

	//Bind Input Parameters
	for($i=0; $i<=$myParamsNo; $i++){
	   OCIBindByName($stmt, $myParam[$i], &$myValue[$i], -1);
	 }

	//Bind the returned cursor
	OCIBindByName($stmt, myCurs, &$cursor, -1, OCI_B_CURSOR);

	OCIExecute($stmt);
	OCIExecute($cursor);

//THE METADATA

	$numOfColls = OCINumCols($cursor);

print "<FONT SIZE=-1>Available Columns in <B>".$mySPName."</B></FONT>";
print "<TABLE>";
print "<TR><TD BGCOLOR=\"BLACK\"><FONT COLOR=\"WHITE\"><B>Column Name &nbsp;</B></TD>";
print "<TD BGCOLOR=\"BLACK\"><FONT COLOR=\"WHITE\"><B>DataType</B></TD></TR>";

	for($i=1; $i<=$numOfColls; $i++){
	    $colName = OCIColumnName($cursor, $i);
	    $colType  = OCIColumnType($cursor,$i);
        $colSize  = OCIColumnSize($cursor,$i);

        print "<TR>";
        print "<TD BGCOLOR=\"#CCCCCC\">".$colName."&nbsp;</TD>";
        print "<TD BGCOLOR=\"#CCCCCC\">".$colType."(".$colSize.")</TD>";
        print "</TR>";
	 }

print "</TABLE>";

OCILogoff($db_id);
}

} // End Class Declaration

//*********************************************************************
//Function to Print an Array (Used Only During Test Phases)
//*********************************************************************
function print_array($array) {
if(gettype($array)=="array") {
echo "<ul>";
while (list($index, $subarray) = each($array) ) {
echo "<li>$index <code>=&gt;</code> ";
print_array($subarray);
echo "</li>";
}
echo "</ul>";
} else echo $array;
}


//Showing the use of the ParseSP Class

$mySP = new parseSP;
$mySP->setSPName("myPkgName.MyProcName");
$params = array("startingletter" => "g");
$mySP->setParamValue($params);
$mycurdata = $mySP->execute();
print_array($mycurdata);

$mySP->show_MetaData();

echo "<HR>";


//another way of using it
$param = array(startingLetter);
$value = array("");
$SP2 = new parseSP;
$SP2->setSPName("myPkgName.MyProcName ");
$SP2->setParam($param);
$SP2->setValue($value);
$mycurdata = $SP2->execute();
print_array($mycurdata);

$SP2->show_MetaData();


echo "<HR>";

//yet another way this is the one I like best !!
$params = array("startingletter" => "b");
$proc = new parseSP("myPkgName.MyProcName ",$params);
$result = $proc->execute();
print_array($result);
$proc->show_metaData();

?>

PHPBuilder
Policies

• Legal Notices
• Licensing
• Permissions
• Privacy Policy
• Advertise

PHP Developer
Topics

• Architecture
• Databases
• HTML/CSS/JavaScript
• Functions
• Tools

PHP Developer
Resources

• E-mail Offers
• Careers
• Forums
• Code Snippet Library

PHP Developer
References

• Getting Started
• News Archive
• Setup and Installation
• Site Operation
• Tips and Quickies
• Tricks and Hacks