Calling a custom Oracle function via PDO

[Anthony Smith]

$stmt = $dbh->prepare("begin TOOLS_PKG.getOrgCode(?); end;");
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT,
12);
$stmt->execute();
Does anything look wrong? This is a custom Oracle function that is
located in a package called TOOLS_PKG.

I have seen how you do this with an Oracle procedure, but would an
Oracle function be any different?

[Mladen Gogala]

On Thu, 15 Mar 2007 09:04:18 -0700, Anthony Smith wrote:

$stmt = $dbh->prepare("begin TOOLS_PKG.getOrgCode(?); end;");
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
$stmt->execute();
Does anything look wrong? This is a custom Oracle function that is
located in a package called TOOLS_PKG.

I have seen how you do this with an Oracle procedure, but would an
Oracle function be any different?

First, my advice would be to use ADOdb or OCI8 as PDO is very buggy
and not developed as actively as OCI8. Second, on the PL/SQL level you
can only call a procedure and not a function, otherwise, this happens:

SQLbegin
2 sysdate;
3 end;
4 /
sysdate;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00221: 'SYSDATE' is not a procedure or is undefined
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

The "sysdate" function is, of course, well known and well defined
function but is not a procedure. In other words, in PL/SQL one
cannot just invoke functions like procedures and ignore the return
value. The proper code would look like this:

SQLdeclare
2 td date;
3 begin
4 td:=sysdate();
5 dbms_output.put_line('Today is:'||td);
6 end;
7 /
Today is:18-MAR-07

PL/SQL procedure successfully completed.

SQL>

You should use functions like functions, not like procedures.
--
http://www.mladen-gogala.com