ORA-01036: illegal variable name/number while calling stored function

Question

I try to call to a stored function in a package that returns a number:

function LOGIN(USERNAME in varchar2 default null
               , PASSWORD in varchar2 default null) 
         return number;

It returns 0 or negative integer for failure and a positive integer for success. Here is my PHP code calling the function:

$sql = ":v_res := PACK.LOGIN(:p_user, :p_pass)";
$bindings = [ ':p_user' => 'test', ':p_pass' => '1234', ':v_res' => & $result];

$statement oci_parse($connection, $sql);
foreach ($bindings as $k => & $v) {
    oci_bind_by_name($statement, $k, $v, customSizeOf($v), determineSqlType($v));
}

oci_execute($statement);

When I use result as shown (not defined before using in binding) it returns "Undefined variable" warning. If I suppress the warning and move on, it is bound with null, size of -1 and type 1 (SQLT_CHR); If I define result like $result = -1;, it is bound with -1, size of PHP_INT_SIZE and type 3 (SQLT_INT).

Either way, on execute, this error is produced

ORA-01036: illegal variable name/number

Answer 1

This does not work as you have to wrap your call either in a PL/SQL block or in a SQL query:

$sql = "BEGIN :v_res := PACK.LOGIN(:p_user, :p_pass); END";

Or

$sql = "SELECT caller(:p_user, :p_pass) v_res FROM DUAL"

Answer 2

This worked, I already don't know why, but it works:

$result = -1;

$sql = <<<"SQL"
begin
    :V_RETURN := PACK.LOGIN(
        USERNAME => :USERNAME,
        PASSWORD => :PASSWORD
    );
end;
SQL;

$bindings = [
    ':USERNAME' => $username,
    ':PASSWORD' => $password,
    ':V_RETURN' => & $result,
];

$statement oci_parse($connection, $sql);
foreach ($bindings as $k => & $v) {
    oci_bind_by_name($statement, $k, $v, customSizeOf($v), determineSqlType($v));
}

oci_execute($statement);

It should have been passed to the array by reference, but still no idea why it works and the other one threw binding error; if anything, it should bind a memory and it would not be $result.

Update: It's better to use SQLT_LNG type instead of SQLT_INT since it may not work properly on Unix systems and give int(-4294967295) for everything (overflow). Contrary to public belief, Windows is fine for both SQLT_INT and SQLT_LNG so I suggest for compatibility issues use SQLT_LNG.

Answer 3

@Cunning : Please try the below options.

1. Check http://php.net/manual/en/oci8.examples.php .

2. Try changing your binding order

   $bindings = [':v_res' => & $result, ':p_user' => 'test', ':p_pass' => '1234'];