Calling a function/procedure present inside an Oracle package from PHP
I am working with PHP-PDO and Oracle 11g. I am working with Oracle packages which have many functions and stored procedures. Now when I make a call to one of the functions from sql*plus or sql developer IDE, I run this command to get the result set.
select package_name.function_name(param1,param2) from dual
It works fine and returns my result set. Now when I do the same, I am getting errors from the PDO Exception handling. The code with on PHP end looks like this,
$stmt = "select package_name.function_name (?,?) from dual";
$res = $this->ConnOBJ->prepare($stmt);
$param1 = '1';
$param2 = null;
$result->bindParam(1,$param1);
$result->bindParam(2,$param2);
$result->execute();
And I get back an exception which is being logged into my log file.
Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 904 OCIStmtExecute: ORA-00904: "PACKAGE_NAME"."FUNCTION_NAME"": invalid identifier (/var/www/php-5.3.3/ext/pdo_oci/oci_statement.c:146)' in /opt/web/dir/ora_class.php:209 Stack trace: #0 /opt/web/dir/ora_class.php(209): PDOStatement->execute() #1 /opt/web/dir/ora_class.php(298): dbPDO->execPackage() #2 {main} thrown in /opt/web/dir/ora_class.php on line 209
Am I passing the query in a wrong way? Or am I binding the parameters in a wrong way?
Update
I have now got the data going through to Oracle, and have found how to pass null values. My code now is
$stmt = "select package_name.function_name(?,?) from dual";
$res = $this->ConnOBJ->prepare($stmt);
$param1 = 1;
$param2 = null;
$res->bindParam(1,$param1,PDO::PARAM_INT);
$res->bindParam(2,$param2,PDO::PARAM_NULL);
$res->execute();
var_dump($r开发者_运维百科es->fetchAll());
And now when I pass data, I get back the error
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 932 OCIStmtFetch: ORA-00932: inconsistent datatypes: expected CHAR got DTYCWD (/var/www/php-5.3.3/ext/pdo_oci/oci_statement.c:467)' in /opt/web/dir/ora_class.php:216 Stack trace: #0 /opt/web/dir/ora_class.php(216): PDOStatement->fetchAll() #1 /opt/web/dir/ora_class.php(305): dbPDO->execPackage() #2 {main} thrown in /opt/web/dir/ora_class.php on line 216
I am making sure all the types are right, but I still am getting back the same error. I even removed the null value and passed in a string, and changed the pdo type to PDO::PARAM_STR, but it still gives me the error.
Does the function take one parameter or two? In SQL*Plus, you're passing two parameters. In PHP, you're passing only one. If the function requires two parameters and there is no overloaded method that takes only one parameter, you'd get this error.
I am not using PDO anymore, I would be using OCI drivers. Thank you for all the help.
Here is a link to an answer for a similar question [LINK] : https://stackoverflow.com/a/57558306/7897970
or best
//Your connection details
$conn = oci_connect($username, $password, '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))' );
/* Your query string; you can use oci_bind_by_name to bind parameters or just pass the variable in it*/
$query = "begin :cur := functionName('".$param1."','".$param2."','".$param3."'); end;";
$stid = oci_parse($conn, $query);
$OUTPUT_CUR = oci_new_cursor($conn);
oci_bind_by_name($stid, ':cur', $OUTPUT_CUR, -1, OCI_B_CURSOR);
oci_execute($stid);
oci_execute($OUTPUT_CUR);
oci_fetch_all($OUTPUT_CUR, $res);
// To get your result
var_dump($res);
精彩评论