Procedures of Oracle in php with PDO

Question

I'm having a problem with propel 1.6 and a oracle procedure. I post it under PDO because I'm Propel just for proxying my call to PDO.

Basically the procedure get the user name and password, checks that is OK and return the user. For that reason it returns an types.cursorType.

The sql start like this.

CREATE OR REPLACE 
PROCEDURE         "SP_LOGIN" (R_CURSOR OUT types.cursorType, UserId IN 
VARCHAR2, Password IN VARCHAR2) 

my php code is:

$con = Propel::getConnection(); 
$sql = 'BEGIN SP_LOGIN(:CURSOR, :0, :1); END;'; 
$stmt = $con->prepare($sql); 
$result_arr; 
$stmt->bindParam(":CURSOR", $result_arr, PDO::PARAM_STR || PDO::PARAM_INPUT_OUTPUT); 
$stmt->bindParam(":0", $username, PDO::PARAM_STR); 
$stmt->bindParam(":1", $password, PDO::PARAM_STR); 
$stmt->execute(); 
$result_arr = $stmt->fetchAll(); 

Now that throws an exception of type: {PDOException} SQLSTATE[HY000]: General error: 6550 OCIStmtExecute: ORA-06550: línea 1, columna 7: PLS-00306: numbers or types of bad arguments calling 'SP_LOGIN'

What I'm doing wrong?

Thanks in advance.

P.S: I ask this question on the Propel forum and they direct me to search for the PDO solution.

Answer 1

checks that is OK and return the user

No - according to the prototype it returns a cursor. Cursors have no meaning outside PL/SQL. If you change the type to a sys_refcursor and explicitly initialize $result_arr as an array, I'd expect it to have a better chance to work.

Although looking at Alvaro's answer and the absence of a non-scalar paramter type I think it might not.

Answer 2

I'd suspect the problem is the first parameter. You tell PDO that it's a string (PDO::PARAM_STR) but it's actually types.cursorType. There's a user comment in the PHP manual that suggests that ref cursors are not supported.

Unluckily, the Oracle driver for PDO is experimental and (IMHO) basically abandoned.