oracle - ORA-06502 而 dbms_sql.execute() 外绑定

Question

我在通过 dbms_sql 使用绑定变量（输入和输出）执行动态 SQL 时遇到了一些麻烦。我总是得到 ORA-06502 但无法得到原因。到目前为止，我可以减少 SQL 片段以知道 out 参数发生错误。

declare
  l_cur_id NUMBER;
  l_sql    VARCHAR2(100) := 'begin :1 := ''test''; end;';
  l_res    VARCHAR2(100);
  l_dbms   NUMBER;
begin
  l_cur_id := dbms_sql.open_cursor;
  dbms_sql.parse(l_cur_id, l_sql, dbms_sql.native);
  dbms_sql.bind_variable(l_cur_id, '1', l_res);
  l_dbms := dbms_sql.execute(l_cur_id); -- ORA here
  dbms_sql.close_cursor(l_cur_id);
exception
  when others then
    dbms_sql.close_cursor(l_cur_id);
    raise;
end;
/

参数内工作正常。我正在使用 Oracle Database 12 Enterprise Edition Release 12.1.0.2.0

我是否必须以另一种方式配置输出参数？我很感激任何帮助。

标签： oracleplsqloracle12cdynamic-sql

Answer 1

您尚未指定绑定变量的大小。默认情况下，它使用变量的当前长度；从文档：

范围	描述
out_value_size	VARCHAR2、RAW、CHAR OUT 或 IN/OUT 变量的最大预期 OUT 值大小（以字节为单位）。如果没有给出大小，则使用当前值的长度。如果 value 参数未初始化，则必须指定此参数。

由于该变量默认初始化为 null，因此该长度为零；这与说它未初始化相同。因此，当它尝试将四个字符分配'test'给零字符变量时会出错。

您还需要调用dbms_sql.variable_value以检索出绑定变量值。

如果您l_res使用的值至少与您可能在动态块内分配的任何值一样长，那么它将起作用：

declare
  ...
  l_res    VARCHAR2(100) := 'xxxx';
  ...
begin
  ...
  dbms_sql.bind_variable(l_cur_id, '1', l_res);
  l_dbms := dbms_sql.execute(l_cur_id);
  dbms_sql.variable_value(l_cur_id, '1', l_res);
  ...
end;
/

但这显然是理想的，因为实际上您需要提供一个 100 字符长的值，并且如果稍后更改长度，请记住对其进行调整；所以改为在绑定调用中指定长度：

declare
  ...
  l_res    VARCHAR2(100);
  ...
begin
  ...
  dbms_sql.bind_variable(l_cur_id, '1', l_res, 100);
  l_dbms := dbms_sql.execute(l_cur_id);
  dbms_sql.variable_value(l_cur_id, '1', l_res);
  ...
end;
/

db<>小提琴