469,960 Members | 1,887 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,960 developers. It's quick & easy.

Calling a custom Oracle function via PDO

$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?

Mar 15 '07 #1
1 4998
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
Mar 18 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jason Leiser | last post: by
5 posts views Thread by Nick Flandry | last post: by
1 post views Thread by jens Jensen | last post: by
1 post views Thread by burtonl | last post: by
4 posts views Thread by Peter Afonin | last post: by
4 posts views Thread by raghuvendra | last post: by
reply views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.