Skip to Main Content
  • Questions
  • Single row cursor for short text string from dual produces CHAR(32767)


Question and Answer

Connor McDonald

Thanks for the question, Raul.

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

Last updated: May 10, 2021 - 8:52 am UTC

Version: 19.11

Viewed 100+ times

You Asked


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;
  a clob;
  l_msg_content_begin CLOB := EMPTY_CLOB();
  CURSOR cur
    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;
  FOR rec IN cur LOOP
    select dump(rec.lower_main_table) into a from dual;

    -- 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;
  --dbms_output.put_line(substr(l_msg_content_begin, 1, 2000) || 'AA');

And here you can see, datatype is CHAR (Typ=96), and check the length (so whole string padded with spaces "ascii32 == space")

Typ=96 Len=32767:

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:

Typ=1 Len=15: 115,111,109,101,114,97,110,100,111,109,116,97,98,108,101
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.


and we 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  /

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> desc vw
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -----------
 MAIN_TABLE                                                                       CHAR(14)
 LOWER_MAIN_TABLE                                                                 CHAR(14)

I'll log a bug.


  (2 ratings)


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


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 /

More to Explore


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