In oracle 11g r2,we want to get all the columns and Splicing, it issue:ORA-06502: PL/SQL: numeric or value error: character string buffer too small,and how to modify?
step 1:create table:
create table test.history_session as select * from dba_hist_active_sess_history where 1=1;
step 2: test:
declare
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 ='TEST' and table_name='HISTORY_SESSION'
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;
end loop;
dbms_output.put_Line(nvl(column_list1,' ')||nvl(column_list2,' '));
end;
/
error:
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 14
i want to know how to modify?