[SOLVED] Calling a function/procedure present inside an Oracle package from PHP

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($res->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.

Solution

I am not using PDO anymore, I would be using OCI drivers. Thank you for all the help.