Home » SQL & PL/SQL » SQL & PL/SQL » Entering php variables into SP (Oracle Database 11g Enterprise Edition Release 11.1.0.6.0)
icon13.gif  Entering php variables into SP [message #398870] Sat, 18 April 2009 19:28 Go to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hello,

I am trying to input three variables from a php script into my stored procedure but I am getting the error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1

This is my php code
<?php
// Connect to Oracle
$conn = oci_connect(" "," "," ");

$compID=$_POST["compID"];
$custNo=$_POST["custNo"];
$answer=$_POST["answer"];

//assemble SQL statement
$query = "begin Enter_Competition_SP('$compID','$custNo','$answer');end;";

//parse statement
$stmt = oci_parse($conn, $query) or die ("Cannot Parse Query");
//run it
oci_execute($stmt) or die ("Cannot execute statement");
oci_free_statement($stmt);
OCIFreeCursor( $cur );
oci_close($conn);
?>

this is my SP:
create or replace PROCEDURE Enter_Competition_SP
(var_CompID IN NUMBER, var_CustID IN NUMBER, var_Answer IN VARCHAR2)
AS 
BEGIN 
INSERT INTO 
COMPETITIONENTRIES
VALUES 
(var_CompID,var_CustID,var_Answer); 
END Enter_Competition_SP;

and my table looks like this:

CREATE TABLE  "COMPETITIONENTRIES" 
   (	"CompetitionID" NUMBER(8,0), 
	"CustomerID" NUMBER(8,0), 
	"Answer" VARCHAR2(30), 
	 CONSTRAINT "PK_COMPETITIONENTRIES" PRIMARY KEY ("CompetitionID", "CustomerID") ENABLE
   )
/

can anybody help me with this.
many thanks

Re: Entering php variables into SP [message #398871 is a reply to message #398870] Sat, 18 April 2009 19:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
$compID & $custNo must be valid numbers; no letters allowed.
print/display the values of the variable before make the call into Oracle procedure
Re: Entering php variables into SP [message #398880 is a reply to message #398870] Sun, 19 April 2009 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I encourage you to NOT use lower case characters in your column names and quotes in your create table statement.

Regards
Michel
icon7.gif  Re: Entering php variables into SP [message #398903 is a reply to message #398870] Sun, 19 April 2009 06:31 Go to previous message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
many thanks for your help
my compid attribute wasnt gettin set DOh

Smile
Previous Topic: Find maximum number by using while loop ??
Next Topic: JOIN LOGIC
Goto Forum:
  


Current Time: Wed Dec 04 19:40:09 CST 2024