Getting error while inserting into CLOB Column: ORA-06502: PL/SQL: numeric or value error

Question

I am getting the PL/SQL value error while inserting into a CLOB data type as the limit is exceeding from 32767 characters, The limit of my data is exceeding 40000 here.

The error is coming from line number 24 i.e., v_clob_all := while assigning all the values to the CLOB column.

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 24

** Please look into the code below and help me here how to deal with this CLOB value.**

Thanks in advance

1   DECLARE
2      v_clob_all      CLOB;
3      delimiter                 VARCHAR2 (20) := ',';
4      l_err_message   VARCHAR2 (2000);
5      l_retcode_o     NUMBER := 0;
6   BEGIN
7      DBMS_OUTPUT.PUT_LINE ('START');
8   
9      FOR cur_execution_report IN (SELECT TABLE_NAME     TABLE_NAME,
10                                         PURGE_NAME     PURGE_NAME,
11                                         ENABLE_FLAG    ENABLE_FLAG,
12                                         ARCHIVE_FLAG   ARCHIVE_FLAG,
13                                         END_TIME       END_TIME,
14                                         STATUS         STATUS,
15                                         ELIGIBLE_COUNT ELIGIBLE_COUNT,
16                                         TABLE_SIZE     TABLE_SIZE,
17                                         BIZ_RULE       BIZ_RULE,
18                                         ERROR_MESSAGE  ERROR_MESSAGE
19                                    FROM TEST_REPORT_STATUS)
20     LOOP
21        DBMS_OUTPUT.PUT_LINE ('Inside loop');
22  
23        BEGIN
24           v_clob_all :=
25                 v_clob_all
26              || cur_execution_report.TABLE_NAME
27              || delimiter
28              || cur_execution_report.PURGE_NAME
29              || delimiter
30              || cur_execution_report.ENABLE_FLAG
31              || delimiter
32              || cur_execution_report.ARCHIVE_FLAG
33              || delimiter
34              || TO_CHAR (cur_execution_report.END_TIME,
35                          'DD-MON-YYYY HH:MI:SS PM')
36              || delimiter
37              || cur_execution_report.STATUS
38              || delimiter
39              || cur_execution_report.ELIGIBLE_COUNT
40              || delimiter
41              || cur_execution_report.TABLE_SIZE
42              || delimiter
43              || '"'
44              || REPLACE (
45                    (REPLACE (cur_execution_report.BIZ_RULE, ',', '","')),
46                    '"',
47                    '')
48              || '"'
49              || delimiter
50              || cur_execution_report.ERROR_MESSAGE
51              || UTL_TCP.crlf;
52  
53           DBMS_OUTPUT.PUT_LINE ('Length Of CLOB: ' || LENGTH (v_clob_all));
54        --                EXCEPTION
55        --                   WHEN OTHERS
56        --                   THEN
57        --                      l_err_message :=
58        --                         SUBSTR (
59        --                               ' Error while Executing Report clob data fetch for all records purge : '
60        --                            || SQLERRM,
61        --                            1,
62        --                            1500);
63        --                      l_retcode_o := 1;
64        --
65        --                      RAISE RPT_ERR_MSG;
66        END;
67     END LOOP;
68  END;

Answer 1

If I understood you correctly, code you didn't post (why not?) is

declare
  v_clob_all varchar2(32767);

Why didn't you

declare
  v_clob_all clob;

You're working with a CLOB, after all; aren't you?

---

As variable is a CLOB then: apply to_clob to every element you're concatenating, e.g.

       v_clob_all :=
             v_clob_all
          || to_clob(cur_execution_report.TABLE_NAME)
          || delimiter
          || to_clob(cur_execution_report.PURGE_NAME)
          || delimiter
          ...