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