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

Question

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.

Well I have queried other tables in the schema, and they return a result set, so credentials work. — macha, Jan 19 '11 at 20:58
@macha - Yes. Post the specification of the function in the package — Justin Cave, Jan 19 '11 at 22:04
@macha - OK. The function you posted appears to require 4 parameters, not 2. Also, can you post the definition of the MY table (or at least what the four `%TYPE` declarations resolve to? — Justin Cave, Jan 19 '11 at 23:02

score 1 · Answer 1 · answered Jan 19 '11 at 20:43

1

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.

answered Jan 19 '11 at 20:43

Justin Cave

227,342
24
367
384

Well I am trying to pass null through the other parameter. I am passing value only for one parameter. How do I handle this? – macha Jan 19 '11 at 20:53
@macha - So you are getting exactly the same error now that you're passing a NULL for the second parameter? – Justin Cave Jan 19 '11 at 21:13
Well no, I actually found the way how to bind a null value and now I am getting a different error. – macha Jan 19 '11 at 21:36
@macha - OK. Have you updated your post with the new error? The error that is there appears to be the same error you were getting when you were only passing in a single parameter. – Justin Cave Jan 19 '11 at 21:37
Justin, I just updated the post, could you take a look at it? – macha Jan 19 '11 at 21:50

score 1 · Accepted Answer · answered Jan 24 '11 at 14:36

1

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

answered Jan 24 '11 at 14:36

macha

7,337
19
62
84

score 0 · Answer 3 · answered Aug 19 '19 at 14:02

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

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

Update

3 Answers3