oracle 11g ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Question

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?

Answer 1

You can't set cursor's variable to something else; this is wrong:

v_column_name.column_name:='to_char('||v_column_name.column_name| ...

Declare a local variable instead, then do those transformations, if needed. Something like this (ran as SCOTT user, so I'm using USER_TAB_COLUMNS instead).

SQL> CREATE TABLE history_session
  2  (
  3     id   NUMBER
  4  );

Table created.

SQL> DECLARE
  2     column_list1   VARCHAR2 (32767);
  3     column_list2   VARCHAR2 (32767);
  4     l_column_name  VARCHAR2 (200);          --> local variable
  5     i              NUMBER;
  6     l              NUMBER;
  7  BEGIN
  8     column_list1 := '';
  9     column_list2 := '';
 10     i := 0;
 11     l := 0;
 12
 13     FOR v_column_name IN (  SELECT                                     --owner,
 14                                    table_name,
 15                                    column_name,
 16                                    data_length,
 17                                    data_type
 18                               FROM user_tab_columns
 19                              WHERE     1 = 1
 20                                    --                                  AND owner = 'TEST'
 21                                    AND table_name = 'HISTORY_SESSION'
 22                                    AND data_type IN ('VARCHAR2',
 23                                                      'NVARCHAR2',
 24                                                      'NUMBER',
 25                                                      'CHAR')
 26                                    AND data_length <= 255
 27                           ORDER BY column_name)
 28     LOOP
 29        IF v_column_name.data_type = 'NUMBER'
 30        THEN
 31           --v_column_name.column_name :=
 32           l_column_name :=
 33                 'to_char('
 34              || v_column_name.column_name
 35              || ',''99999999999999999.99'')';
 36        ELSE
 37           l_column_name := v_column_name.column_name;
 38        END IF;
 39
 40        IF l < 3700
 41        THEN
 42           column_list1 :=
 43              column_list1 || l_column_name          --v_column_name.column_name
 44                                            || CHR (10) || '||''|''' || '||';
 45        ELSE
 46           column_list2 :=
 47              column_list2 || l_column_name          --v_column_name.column_name
 48                                            || CHR (10) || '||''|''' || '||';
 49        END IF;
 50
 51        l := l + v_column_name.data_length + 10;
 52     END LOOP;
 53
 54     DBMS_OUTPUT.put_Line (NVL (column_list1, ' ') || NVL (column_list2, ' '));
 55  END;
 56  /
to_char(ID,'99999999999999999.99')
||'|'||

PL/SQL procedure successfully completed.

SQL>

The result doesn't look pretty, but I'll leave it to you - fix it, now that procedure kind of works.