Call Stored Procedure ORA-06576: not a valid function or procedure

[raevenx]

I am trying to call a stored procedure that is fed by an input form. I have verified in SQL Developer that my stored procedure works and I can input data. I've also copy / pasted the stored procedure name so I know it is named correctly. Everything I have learned about php and databases I've basically learned this week (and this is the last assignment I have to complete!) So if someone can help me I would really appreciate it.

Warning: oci_execute() [function.oci-execute]: ORA-06576: not a valid function or procedure name in /home/rskor2/public_html/Asst9/insertCustomer.php on line 73
Error Save [ORA-06576: not a valid function or procedure name]

<?
$connection = OCILogon("xxx","xxx","xxx");

if (!$connection) {
	echo "Couldn't make a connection.";
	exit;
} else {echo "You have connected to the UIS Oracle Database! <p>";}

$strSQL = "CALL InsertCustomerAndInterests(inputLName, inputFName, inputArea, inputPhone, inputEmail, inputNat)";

$objParse = oci_parse($connection, $strSQL);
$objExecute = oci_execute($objParse, OCI_DEFAULT);
if($objExecute)
{
	oci_commit($connection); //*** Commit Transaction ***//
	echo "Save completed.";
}
else
{
	oci_rollback($connection); //*** RollBack Transaction ***//
	$e = oci_error($objParse); 
	echo "Error Save [".$e['message']."]";
}

oci_close($connection);
?>

Please and thanks...

Renee

[raevenx]

So I figured out a little more: I wasn't passing the values properly with post, but now I have a new error. 🙁

Warning: oci_parse() [function.oci-parse]: ORA-01756: quoted string not properly terminated in /home/rskor2/public_html/Asst9/insertCustomer.php on line 147

Warning: oci_execute() expects parameter 1 to be resource, boolean given in /home/rskor2/public_html/Asst9/insertCustomer.php on line 149

Warning: oci_error() expects parameter 1 to be resource, boolean given in /home/rskor2/public_html/Asst9/insertCustomer.php on line 167
Error Save []

$LastName = $_POST["inputLName"];
$FirstName = $_POST["inputFName"];
$AreaCode = $_POST["inputArea"];
$Phone = $_POST["inputPhone"];
$Email = $_POST["inputEmail"];
$Nationality = $_POST["inputNat"];

$strSQL = "CALL INSERTCUSTOMERANDINTERESTS ";
$strSQL .= "'$LastName', $FirstName', $AreaCode', '$Phone', $Email', '$Nationality', ";

$objParse = oci_parse($connection, $strSQL);

$objExecute = oci_execute($objParse, OCI_DEFAULT);

if($objExecute)
{
	oci_commit($connection); //*** Commit Transaction ***//
	echo "Save completed.";
}
else
{
	oci_rollback($connection); //*** RollBack Transaction ***//
	$e = oci_error($objParse); 
	echo "Error Save [".$e['message']."]";
}
oci_close($connection);
?>

[NogDog]

I've never worked with the OCI interface, but should your stored procedure be called with parentheses around its input parameters? In any case, looks like you left out a quote before $email, and you probably don't want the terminal comma after the $Nationality parameter. My guess is it would be something like:

$strSQL = "CALL INSERTCUSTOMERANDINTERESTS ";
$strSQL .= "('$LastName', $FirstName', $AreaCode', '$Phone', '$Email', '$Nationality')";

But it's only a guess.

[raevenx]

Thanks for the comment. So yes I was definitely missing quotes but I was also missing the oci_bind_by_name segments. Here is what the working version looks like in case anyone ever runs into the issue.

$LastName = $_POST['inputLName'];
$FirstName = $_POST['inputFName'];
$AreaCode = $_POST['inputArea'];
$Phone = $_POST['inputPhone'];
$Email = $_POST['inputEmail'];
$Nationality = $_POST['inputNat'];

$stid = oci_parse($connection, 'begin XXXXX.INSERTCUSTOMERANDINTERESTS(:inputLName, :inputFName, :inputArea, :inputPhone, :inputEmail, :inputNat); end;');

oci_bind_by_name($stid, ':inputLName', $LastName);
oci_bind_by_name($stid, ':inputFName', $FirstName);
oci_bind_by_name($stid, ':inputArea', $AreaCode);
oci_bind_by_name($stid, ':inputPhone', $Phone);
oci_bind_by_name($stid, ':inputEmail', $Email);
oci_bind_by_name($stid, ':inputNat', $Nationality);

if (!oci_execute($stid)) {
   exit("Procedure Failed.");
}
else echo ("Success");

oci_close($connection);
?>

Renee