In oracle 11g r2 :ORA-06502: PL/SQL: numeric or value error: character string buffer too small, how to fix it, in 19c have not problem

In oracle 11g it issue ora-06502,but in 19c have not the problem： PLSQL，it create successful:

create or replace FUNCTION test.sp_get_columns (v_owner IN VARCHAR2, v_tabname IN VARCHAR2)  RETURN clob  AUTHID CURRENT_USER  AS  column_list1   varchar2(32767);  column_list2   varchar2(32767);  i number;  l number;  BEGIN  column_list1:='';  column_list2:='';  i:=0;  l:=0;  FOR v_column_name IN (SELECT owner, table_name, column_name,data_length,data_type FROM dba_tab_columns WHERE owner =v_owner and table_name=v_tabname  and data_type in ('VARCHAR2','NVARCHAR2','NUMBER','CHAR') and data_length <=255 order by column_name) LOOP  if v_column_name.data_type='NUMBER' then  v_column_name.column_name:='to_char('||v_column_name.column_name||',''99999999999999999.99'')';  end if;  if l <3700 then  column_list1:=column_list1||v_column_name.column_name||chr(10)||'||''|'''||'||';  else  column_list2:=column_list2||v_column_name.column_name||chr(10)||'||''|'''||'||';  end if;  l:=l+v_column_name.data_length+10;  --dbms_output.put_Line(column_list1);  end loop;    if column_list1 is not null then  column_list1:='nvl(sum(dbms_utility.get_hash_value('||substrb(column_list1,1,lengthb(column_list1)-7)||',0,2147483647)),0) HASH_VAL0 ' ;  END IF;    if column_list2 is not null then  column_list2:='nvl(sum(dbms_utility.get_hash_value('||substrb(column_list2,1,lengthb(column_list2)-7)||',0,2147483647)),0) HASH_VAL1, ' ;  i:=i+1;  END IF;      --for gt 255  FOR v_column_name IN (SELECT owner, table_name, column_name,data_length FROM dba_tab_columns WHERE owner =v_owner and table_name=v_tabname  and data_type in ('VARCHAR2','NVARCHAR2','NUMBER','CHAR') and data_length >255 order by column_name) LOOP  i:=i+1;  column_list2:=column_list2||'nvl(sum(dbms_utility.get_hash_value('||v_column_name.column_name||',0,2147483647)),0) HASH_VAL'||i||','||chr(10);  end loop;    -- add for blob  FOR v_column_name IN (SELECT owner, table_name, column_name,data_length FROM dba_tab_columns WHERE owner =v_owner and table_name=v_tabname  and data_type in ('BLOB')  order by column_name) LOOP  i:=i+1;  column_list2:=column_list2||'nvl(sum(dbms_utility.get_hash_value(dbms_crypto.hash(NVL('||v_column_name.column_name||',''0''),3),0,2147483647)),0) HASH_VAL'||i||','||chr(10);  end loop;    -- add for clob  FOR v_column_name IN (SELECT owner, table_name, column_name,data_length FROM dba_tab_columns WHERE owner =v_owner and table_name=v_tabname  and data_type in ('CLOB')  order by column_name) LOOP  i:=i+1;  column_list2:=column_list2||'nvl(sum(dbms_utility.get_hash_value(dbms_crypto.hash(NVL('||v_column_name.column_name||',''0''),3),0,2147483647)),0)  HASH_VAL'||i||','||chr(10);  end loop;    if column_list1 is not null then   if column_list2 is not null then    column_list2:=','||substrb(column_list2,1,lengthb(column_list2)-2);   end if;  else    column_list2:=substrb(column_list2,1,lengthb(column_list2)-2);  END IF;  RETURN nvl(column_list1,' ')||nvl(column_list2,' ') ;  END sp_get_columns_lob_xml;  /  

function create successful

execute Generate script:

sqlplus -s "/as sysdba"    define FLASHSCN=11712771641230;  set echo off newpage 0 space 0 pagesize 100 feed off head off trimspool on linesize 32767  set serveroutput on  spool /home/oracle/hash_compare/ZYXDB_hash_source.sql  append    declare    sql_id0 varchar2(32767);    sql_id1 varchar2(32767);    sql_id2 varchar2(32767);  begin    --dbms_output.enable(99999999999999);    dbms_output.enable(buffer_size=>null);    dbms_output.put_line('set timing on');    dbms_output.put_line('set numwidth 30');    dbms_output.put_line('set longc 999999 long 999999');    dbms_output.put_line('set pagesize 50000');    dbms_output.put_line('col owner for a15');    dbms_output.put_line('col ins_name for a10');    dbms_output.put_line('col run_seq for a15');    dbms_output.put_line('col count for 99999999999');    dbms_output.put_line('col current_scn for 9999999999999999');    dbms_output.put_line('alter session set nls_date_format=''YYYY-MM-DD HH24:MI:SS'';');    FOR v_tablename IN (SELECT a.owner,a.table_name,a.partition_name,a.bytes bytes FROM test.check_list_all a where rownum1 between 1 and 13000 order by rownum1 asc) LOOP        if v_tablename.partition_name is not null then        sql_id0:='SELECT /*+ PARALLEL(a,10) */  '||'''RESULT:   '' RESULT,'''||v_tablename.owner||'''  OWNER ,'''||v_tablename.table_name ||':'||v_tablename.partition_name ||'''' ||' TABLE_NAME,count(1) COUNT,11712771641230 CURRENT_SCN ,';        select goldengate.sp_get_columns(v_tablename.owner,v_tablename.table_name) into sql_id1 from dual;        sql_id2:=' FROM '||v_tablename.owner||'.'||v_tablename.table_name|| ' partition ('||v_tablename.partition_name||') a;';        dbms_output.put_line(sql_id0 || sql_id1 || sql_id2);      ELSE        sql_id0:='SELECT /*+ PARALLEL(a,10) */  '||'''RESULT: '' RESULT,'''||v_tablename.owner||'''  OWNER ,'''||v_tablename.table_name||''' TABLE_NAME,count(1) COUNT,11712771641230 CURRENT_SCN ,';        select goldengate.sp_get_columns(v_tablename.owner,v_tablename.table_name) into sql_id1 from dual;        sql_id2:=' FROM '||v_tablename.owner||'.'||v_tablename.table_name||' a;';        dbms_output.put_line(sql_id0 || sql_id1 || sql_id2);      end if;    end loop;  end;  /    spool off;  exit;  

it issue ERROR

ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "test.sp_get_columns", line 17 ORA-06512: at line 17

how to fix it in oracle 11g

https://stackoverflow.com/questions/66740211/in-oracle-11g-r2-ora-06502-pl-sql-numeric-or-value-error-character-string-bu March 22, 2021 at 12:05PM