I’m trying to connect to a stored procedure using PHP and Oracle. I do not have access to the Oracle server I only have text on a stored procedure that’s supposed to work.
Right now I have one call to a stored procedure working perfectly.
/************************/
/ Working Stored Procedure Call */
PHP CODE
@$conn = oci_connect($username, $password, $connection_string);
$id = ‘someidnumber’;
$term = ‘sometermnumber’;
$location = ‘somelocation’;
if(!$conn){
return FALSE;
}
else{
$sql = ‘BEGIN MYSTOREDPROCEDURE(:p_id, :p_last_name, :p_birth_date, :p_ret_id, :P_return_status, :p_return_msg); END;’;
$stmt = oci_parse($conn,$sql);
// Bind the input parameter
oci_bind_by_name($stmt,“:p_id”,$id);
oci_bind_by_name($stmt,“:p_last_name”,$lname);
oci_bind_by_name($stmt,“:p_birth_date”,$birthdate);
// Bind the output parameter
oci_bind_by_name($stmt,“:p_ret_id”,$ret_id, 40);
oci_bind_by_name($stmt,“:P_return_status”,$ret_stat, 32);
oci_bind_by_name($stmt,“:p_return_msg”,$ret_msg, 40);
oci_execute($stmt);
ORACLE STORED PROCEDURE
PROCEDURE MYSTOREDPROCEDURE(p_id IN VARCHAR2,
p_last_name IN VARCHAR2,
p_birth_date IN VARCHAR2,
p_ret_id OUT VARCHAR2,
P_return_status OUT NUMBER,
p_return_msg OUT VARCHAR2
);
However there is a second stored procedure will not work at all. I keep getting the error message:
ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at “MYNONWORKINGSTOREDPROCEDURE”, line 61 ORA-06512: at line 1
when oci_bind_by_name($stmt,“:p_return_msg”,$ret_msg, 40); has a fourth parameter 3 or above.
But, if I change oci_bind_by_name($stmt,“:p_return_msg”,$ret_msg, 2); to something less than or equal to 2 I get:
oci_execute(): ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at “MYNONWORKINGSTOREDPROCEDURE”, line 54 ORA-06512: at line 1
I’ve looked online and all that I’m getting is that the :p_return_msg is either too large or too small. But, :p_return_msg is an OUT parameter. I’m not sure where the issue lies because the two stored procedures are quite similar so I believe they should work. I’m assuming that the error is caused by me, but if no one else can find it I’m going to assume that the Stored Procedure has something wrong with it.
Thank you very much for any help.
/*****************************/
/ NON-Working Stored Procedure Call */
PHP CODE
@$conn = oci_connect($username, $password, $connection_string);
$id = ‘someidnumber’;
$term = ‘sometermnumber’;
$location = ‘somelocation’;
if(!$conn){
return FALSE;
}
else{
$sql = ‘BEGIN MYNONWORKINGSTOREDPROCEDURE(:p_id, :p_term, :p_location, :p_dep_amt, :P_return_status, :p_return_msg); END;’;
$stmt = oci_parse($conn,$sql);
// Bind the input parameter
oci_bind_by_name($stmt,“:p_id”,$id);
oci_bind_by_name($stmt,“:p_term”,$term);
oci_bind_by_name($stmt,“:p_location”,$location);
// Bind the output parameter
oci_bind_by_name($stmt,“:p_dep_amt”,$dep_amt,32);
oci_bind_by_name($stmt,“:P_return_status”,$ret_stat, 32);
oci_bind_by_name($stmt,“:p_return_msg”,$ret_msg, 40);
oci_execute($stmt);
ORACLE Stored Procedure
PROCEDURE .MYNONWORKINGSTOREDPROCEDURE(p_id IN VARCHAR2,
p_term IN VARCHAR2,
p_location IN VARCHAR2,
p_dep_amt OUT NUMBER,
P_return_status OUT NUMBER,
p_return_msg OUT VARCHAR2
);