ORA-06502: PL/SQL: numeric or value error: character to number conversion error using clob not working

Question

I want to select the data for multiple JOBID's in an oracle procedure, so while doing that I am getting error as

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

For that reason I changed the data-type of JOBID to CLOB, but still nothing is working and getting the same error.

SELECT CAST( STR2TBL('7507, 22781, 24949, 187771, 189739, 185736, 185737, 187276, 187665, 186012, 185743,
187559, 195056, 185626, 186337, 186655, 187495, 185679, 186352, 187578, 189666, 186017, 186254, 190430, 
185176, 186243, 186389, 184861, 188567, 192888, 186283, 191680, 186355, 186775, 186776, 189203, 189910, 
184871, 184906, 186125, 186336, 186339, 186565, 190469, 192138, 187710, 190267, 189186, 191515, 187711, 
186442, 187866, 186351, 190211, 186159, 191145, 191377, 187859, 187952, 192877, 185906, 187533, 190735,
185907, 186248, 189255, 185625, 190941, 185449, 191380, 186106, 189510, 186360, 191302, 189611, 190617,
185792, 187083, 188226, 185288, 188150, 186903, 189377, 191694, 188053, 186112, 186683, 192014, 185416, 
191872, 185287, 185305, 190949, 186108, 186109, 189351, 186134, 188553, 188677, 189732, 187494, 188509,
192253, 189756, 191681, 186313, 189972, 191752, 185663, 186169, 188137, 188508, 185178, 192363, 185122,
190679, 187951, 190905, 194509, 194510, 203859, 194422, 195960, 199874, 202223, 204643, 204648, 204808, 
193250, 198709, 204132, 203977, 197526, 198293, 204636, 193779, 197365, 194224, 194842, 194657, 202435,
194597, 204664, 199924, 194526, 195664, 196277, 204668, 204671, 199873, 197154, 199879, 200267, 204798,
204806, 204028, 203714, 203858, 204650, 204658, 195039, 196382, 198780, 199991, 204942, 204947, 204955,
199989, 203400, 203409, 194846, 199896, 200558, 200726, 193576, 204633, 204667, 204674, 204675, 204911,
195745, 198303, 201390, 199988, 198405, 198826, 199871, 199926, 193249, 195256, 199877, 194266, 201870,
196115, 196186, 196949, 202114, 204338, 198203, 202113, 195171, 197902, 198215, 204566, 199990, 193851,
195746, 199872, 202511, 200136, 204600, 204634, 204635, 204646, 204651, 204653, 204654, 204665, 204670,
193058, 193260, 203220, 202043, 193061, 205345, 205192, 205033, 205267, 205400, 205249, 205324, 205351,
205117, 205141, 205142, 205289, 205285, 205016, 205151, 205395, 205220, 205100, 205113, 205140, 205105, 
10522, 3473, 676, 2626, 3474, 3116, 8246, 3478, 5136, 675, 6816, 2621, 2622, 2623, 2624, 3476, 3115, 526, 
2625, 2627, 4175, 674, 5873, 5874, 3475, 3114, 446, 8746, 12825, 16173, 12826, 20967, 25133, 32526, 30226' ) AS MYTABLETYPE ) FROM DUAL;

Also STR2TBL is my inbuilt function

create or replace function         str2tbl( p_str in CLOB ) return myTableType
    as
        l_str   long default p_str || ',';
        l_n        number;
        l_data    myTableType := myTabletype();
    begin
        loop
            l_n := instr( l_str, ',' );
            exit when (nvl(l_n,0) = 0);
           l_data.extend;
           l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
           l_str := substr( l_str, l_n+1 );
       end loop;
       return l_data;
  END;

Answer 1

Your procedure is inefficient as it keeps overwriting the CLOB with successively smaller substrings.

Instead, you can track the position of the commas before and after each term and find the substrings from those positions using this split_string function (changing the data type to a CLOB):

CREATE OR REPLACE TYPE stringlist AS TABLE OF VARCHAR2(20)
/

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  CLOB,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
  p_result       stringlist := stringlist();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Then you can use it like this:

SELECT column_value
FROM   TABLE(split_string(
         '7507, 22781, 24949, 187771, 189739, 185736, 185737, 187276, 187665, 186012, 185743, 187559, 195056, 185626, 186337, 186655, 187495, 185679, 186352, 187578, 189666, 186017, 186254, 190430, 185176, 186243, 186389, 184861, 188567, 192888, 186283, 191680, 186355, 186775, 186776, 189203, 189910, 184871, 184906, 186125, 186336, 186339, 186565, 190469, 192138, 187710, 190267, 189186, 191515, 187711, 186442, 187866, 186351, 190211, 186159, 191145, 191377, 187859, 187952, 192877, 185906, 187533, 190735, 185907, 186248, 189255, 185625, 190941, 185449, 191380, 186106, 189510, 186360, 191302, 189611, 190617, 185792, 187083, 188226, 185288, 188150, 186903, 189377, 191694, 188053, 186112, 186683, 192014, 185416, 191872, 185287, 185305, 190949, 186108, 186109, 189351, 186134, 188553, 188677, 189732, 187494, 188509, 192253, 189756, 191681, 186313, 189972, 191752, 185663, 186169, 188137, 188508, 185178, 192363, 185122, 190679, 187951, 190905, 194509, 194510, 203859, 194422, 195960, 199874, 202223, 204643, 204648, 204808, 193250, 198709, 204132, 203977, 197526, 198293, 204636, 193779, 197365, 194224, 194842, 194657, 202435, 194597, 204664, 199924, 194526, 195664, 196277, 204668, 204671, 199873, 197154, 199879, 200267, 204798, 204806, 204028, 203714, 203858, 204650, 204658, 195039, 196382, 198780, 199991, 204942, 204947, 204955, 199989, 203400, 203409, 194846, 199896, 200558, 200726, 193576, 204633, 204667, 204674, 204675, 204911, 195745, 198303, 201390, 199988, 198405, 198826, 199871, 199926, 193249, 195256, 199877, 194266, 201870, 196115, 196186, 196949, 202114, 204338, 198203, 202113, 195171, 197902, 198215, 204566, 199990, 193851, 195746, 199872, 202511, 200136, 204600, 204634, 204635, 204646, 204651, 204653, 204654, 204665, 204670, 193058, 193260, 203220, 202043, 193061, 205345, 205192, 205033, 205267, 205400, 205249, 205324, 205351, 205117, 205141, 205142, 205289, 205285, 205016, 205151, 205395, 205220, 205100, 205113, 205140, 205105, 10522, 3473, 676, 2626, 3474, 3116, 8246, 3478, 5136, 675, 6816, 2621, 2622, 2623, 2624, 3476, 3115, 526, 2625, 2627, 4175, 674, 5873, 5874, 3475, 3114, 446, 8746, 12825, 16173, 12826, 20967, 25133, 32526, 30226',
         ', '
       ));

db<>fiddle here