Friday, January 18, 2008

Calling Oracle Stored Procedure with PHP

I've just been following this excellent tutorial article
on the Oracle WebSite:
Calling Oracle Stored Procedure with PHP

I'm only just starting to use Oracle PL/SQL and PHP,
and it's still early days. So I'm making really stupid
mistakes and getting errors like this:
Warning: oci_execute() [function.oci-execute]: ORA-06550: line 1, column 7:
PLS-00201: identifier 'ROLLROUTE' must be declared ORA-06550: line 1, column 7:

Here was my code:
 $sql = "BEGIN RollRoute(:PartNo); END;";

$s = oci_parse($c, $sql );
oci_bind_by_name($s,':PartNo',$PartNo,32);
oci_execute($s);

The fix was simple:
 $sql = "BEGIN RROUTING_API.RollRoute(:PartNo); END;";

Yes, the Procedure is in a Package.
So you need to prefix the procedure name, with the package name.
Being a newbie, it look me a while to spot that!