Calling a function / procedure present in an Oracle package with PHP

Question

I work with PHP-PDO and Oracle 11g. I work with Oracle packages that have many functions and stored procedures. Now, when I call one of the functions from the sql * plus or sql developer IDEs, I run this command to get a 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 get errors from PDO exception handling. The code from the end of PHP is as follows:

 $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 am returning an exception that is logged in 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 transferring the request incorrectly? Or am I incorrectly binding the parameters?

Update

Now I got the data passed to Oracle and found how to pass null values. My code is now

 $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($res->fetchAll()); 

And now, when I transfer data, I get an error message

 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 check the validity of all types, but still get the same error. I even deleted the null value and passed the string, and also changed the pdo type to PDO :: PARAM_STR, but it still throws an error.

Answer 1

I no longer use PDO, I would use OCI drivers. Thank you for your help.

Answer 2

Does the function perform one or two parameters? In SQL * Plus, you pass two parameters. In PHP you only pass one. If a function requires two parameters and there is no overloaded method that accepts only one parameter, you will get this error.

Answer 3

Here is a link to the answer to a similar question [LINK]: fooobar.com/questions/1451896 / ...

or better

 //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);