Oracle procedure out parameter returns -1 from PHP
oracle, oracle11g, php, stored-procedures
Solution
I'm not familiar with PHP, but according to the manual page for oci_bind_by_name:
You must specify maxlength when using an OUT bind so that PHP allocates enough memory to hold the returned value.
Also, there's at least one minor syntax error in your PL/SQL block. You need to always use double-quotes when referencing `GetUserLogin`. (Although I doubt that's your main problem. If it were, you should get an error message.)
Problem
I am trying to get Oracle procedures to execute from my PHP program. I created a simple test procedure: ``` CREATE OR REPLACE PROCEDURE "JKJ3"."GetUserLogin" { userlogin_in in varchar2, userid_out out numeric, pass_out out varchar2 } IS BEGIN SELECT user_id, password INTO userid_out, pass_out FROM "JKJ3"."USERS" WHERE login = userlogin_in; END; ``` I tested the procedure using Aqua Data Studio: ``` DECLARE userid_out number; password_out number; BEGIN "JKJ3"."GetUserLogin"('UserName', userid_out, password_out); dbms_enable(10000); dbms_put_line(userid_out); dbms_put_line(password_out); END; ``` This works fine, returns the userid and the password value. So far so good! I tried executing the procedure from PHP but I cant get the procedure to work to save my life! PHP Code: ``` PutEnv("ORACLE_HOME=/afs/cad/solaris/oraclient10.2"); $this->conn=oci_connect($this->ucid, $this->dbpassword, $this->db); // Check if connection was sucessful. if ( !$this->conn ) { echo "Unable to connect: " . var_dump( OCIError() ); die("Unable to connect: " . var_dump( OCIError() )); } $query = 'BEGIN "JKJ3"."GetUserLogin"(:login_in, :userid_out, :password_out); END;'; // Execute stored procedure to add new user and return the new users ID $command = oci_parse($this->conn, $query) or die('Cannot parse query'); $userid; $password_out; // bind the input variables and out put variables. oci_bind_by_name($command, ':login_in', $login, 30) or die('cannot bind login'); oci_bind_by_name($command, ':userid_out', $userid) or die('cannot bind userid'); oci_bind_by_name($command, ':password_out', $password_out, 300) or die('cannot bind password'); oci_execute($command); echo 'userid = ' . $userid . ' password = ' . $password_out; oci_close($this->conn); ``` When I execute this I get a userid and password of null. edit I have the same results with other procedures, heck I even went as far as to create a procedure that takes and assigns a static value to a return variable and I still get nothing back (including no errors). http://www.oracle.com/technetwork/articles/fuecks-sps-095636.html (sayHello example). Once again the procedure works from Aqua Data Studio but not from PHP. No exception is thrown, no variable returned. Does Oracle have something like the SQL SERVER Profiler that allows you to view queries being executed against Oracle? (I dont have admin rights for the server though). Doesnt seem like it could be permission based since it works from ADS. I can run queries against the database without a problem so I am confident my connection is working. Am I missing something obvious?