ORA-01036:调用存储函数时，非法的变量名称/编号 [英] ORA-01036: illegal variable name/number while calling stored function

问题描述

我尝试在返回数字的包中调用存储的函数:

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;

对于失败，返回0或负整数，对于成功，返回正整数. 这是我的PHP代码调用该函数:

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);

当我使用如图所示的结果(在绑定中使用之前未定义)时，它将返回未定义变量"警告.如果我禁止显示警告并继续下去，它将与null绑定，大小为-1且类型为1(SQLT_CHR)；如果我定义类似$result = -1;的结果，则将结果与-1绑定在一起，其大小为PHP_INT_SIZE，类型为3(SQLT_INT).

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

推荐答案

这不起作用，因为您必须将调用包装在PL/SQL块或SQL查询中:

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";

或

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

这篇关于ORA-01036:调用存储函数时，非法的变量名称/编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！