Skip to Main Content
  • Questions

  • Single row cursor for short text string from dual produces CHAR(32767)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raul.

Asked: May 07, 2021 - 6:17 am UTC

Last updated: July 06, 2021 - 1:20 am UTC

Version: 19.11

Viewed 1000+ times

You Asked

Hi

I have tried 19.9 - 19.11

I have noticed some suspicious behaviour regarding dual.

I will create an example. At the moment, here you can see that from mytab, there comes only single row.

Then I will dump the datatype to output.

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


And here you can see, datatype is CHAR (Typ=96), and check the length (so whole string padded with spaces "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,....................


Seems like lower() function somehow produces this strange behaviour.

Is this normal..?

Also when I dump rec.main_table instead (so not lower() function output)

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


Then I get type CHAR and an actual length. So it is expected.


On contrast, when I uncomment this second line also

--union select 'ALSOSOMERANDOMTABLE' as main_table from dual


Then it is expected:

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


Type is varchar and length is actual length.

Regards
Raul

and Connor said...

My thoughts are that this is a bug. I've simplified the test case down to:

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


You can workaround it with a CAST but ideally you should not have to do it, because the same expression in a view does not have the same issue

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)


I'll log a bug.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 10, 2021 - 10:31 am UTC

Thanks.

Expected behavior

Andrew, May 10, 2021 - 5:43 pm UTC

This is the expected behavior that I ran into some time ago. I was attempting to build an FBI doing something similar -- applying built-in function to a string. It failed due to the value returned by the function exceeding the size limit of the index. It turns out, it seems, that the function will accept strings up to the maximum allowed, and since it does not know the length of the string being passed to it (it does not really care), it will return the maximum length allowed. Thus when executed procedurally, you see the length actually returned, not the length of the set of characters passed. The solution is to either provide a variable large enough to hold the maximum length or use SUBSTR() to limit the size returned.

SQL> declare
2 dummy1 varchar2(64);
3 dummy2 varchar2(32767);
4 begin
5 select main_table, lower_main_table
6 into dummy1, dummy2
7 from (
8 select main_table, lower(main_table) as lower_main_table
9 from ( select 'somerandomtext' as main_table from dual )
10 );
11 dbms_output.put_line(length(dummy1));
12 dbms_output.put_line(length(dummy2));
13 end;
14 /
14
14
</code>

A reader, June 30, 2021 - 8:33 am UTC

So, this is expected behavior and not a bug then, or what..?

Regards
Raul
Connor McDonald
July 06, 2021 - 1:20 am UTC

I believe it to be a bug, but I will clarify internally
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.