Oracle 来自dual的短文本字符串的单行光标产生CHAR(32767)

嗨

我试过19.9-19.11

我注意到一些关于双重的可疑行为。

我将创建一个示例。目前，您可以在mytab中看到只有一行。

然后我将转储数据类型输出。

SQL> set serveroutput on size unlimited;
declare
  a clob;
  l_msg_content_begin CLOB := EMPTY_CLOB();
  CURSOR cur
    IS
    with mytab as (
      select 'SOMERANDOMTABLE' as main_table from dual
      --union select 'ALSOSOMERANDOMTABLE' as main_table from dual
    )
    select main_table, lower_main_table
    from (
      select main_table, lower(main_table) as lower_main_table
      from mytab
    )
    order by 1 desc;
  rec cur%rowtype;
BEGIN
  FOR rec IN cur LOOP
    dbms_output.put_line(rec.main_table);
    select dump(rec.lower_main_table) into a from dual;
    dbms_output.put_line(a);

    -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    -- If you have only one row from dual, then you get error if you uncomment this: "l_msg_content_begin := ..."
    -- With 2 or more rows from dual, all good
    --l_msg_content_begin := 'blabla '||rec.lower_main_table||' blablabla '||rec.lower_main_table||'bla'||UTL_TCP.CRLF;
  END LOOP;
  --dbms_output.put_line(substr(l_msg_content_begin, 1, 2000) || 'AA');
END;
/

在这里，您可以看到，数据类型是CHAR (类型 = 96)，并检查长度 (因此整个字符串填充有空格 “ascii32 = = space”)

SOMERANDOMTABLE
Typ=96 Len=32767:
115,111,109,101,114,97,110,100,111,109,116,97,98,108,101,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,....................

似乎lower()函数不知何故产生了这种奇怪的行为。

这正常吗 ..？

另外，当我转储rec.main_table时 (所以不更低 () 函数输出)

select dump(rec.main_table) into a from dual;

然后我得到类型CHAR和实际长度。因此，这是意料之中的。


相比之下，当我取消评论第二行时

--union select 'ALSOSOMERANDOMTABLE' as main_table from dual

然后是预期的:

SOMERANDOMTABLE
Typ=1 Len=15: 115,111,109,101,114,97,110,100,111,109,116,97,98,108,101
ALSOSOMERANDOMTABLE
Typ=1 Len=19: 97,108,115,111,115,111,109,101,114,97,110,100,111,109,116,97,98,108,101

类型为varchar，长度为实际长度。

问候
劳尔

我的想法是这是一个错误。我已经将测试用例简化为:

SQL> set serveroutput on size unlimited;
SQL> begin
  2    for rec in
  3    (
  4        select main_table, lower_main_table
  5            from (
  6              select main_table, lower(main_table) as lower_main_table
  7              from ( select 'somerandomtext' as main_table from dual )
  8            )
  9    )loop
 10      dbms_output.put_line(length(rec.main_table));
 11      dbms_output.put_line(length(rec.lower_main_table));
 12    end loop;
 13  end;
 14  /
14
32767

您可以使用强制转换来解决它，但理想情况下，您不必这样做，因为视图中的相同表达式没有相同的问题

SQL> create or replace
  2  view vw as
  3        select main_table, lower_main_table
  4            from (
  5              select main_table, lower(main_table) as lower_main_table
  6              from ( select 'somerandomtext' as main_table from dual )
  7            )
  8  /

View created.

SQL>
SQL>
SQL> desc vw
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -----------
 MAIN_TABLE                                                                       CHAR(14)
 LOWER_MAIN_TABLE                                                                 CHAR(14)

我会记录一只虫子。