0

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
Nad
  • 4,605
  • 11
  • 71
  • 160
  • 1
    Why would `l_str` be declared as a `long` rather than a `clob`? `long` has been deprecated for a long, long, **long**, **long** time. I believe more than 2 decades. I'm not certain that's the issue but it can't be helping you. And it probably is if the problem occurs when the string you're passing in exceeds 32k. – Justin Cave Dec 14 '21 at 21:44
  • @JustinCave: while passing `clob`, i am getting error as `expression is of wrong type` at line `l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));` – Nad Dec 14 '21 at 21:47
  • also, how to limit the passing string.. as its dynamic and need to handle it – Nad Dec 14 '21 at 21:48

1 Answers1

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • i am getting space between two strings now, for ex: `'7507', ' 22781', ' 24949'` – Nad Dec 14 '21 at 22:27
  • @hud That is because you have space and new line characters in your data. If you don't want them then remove them from your data (either when you input it, like I did in the answer, or by trimming the data after splitting). – MT0 Dec 14 '21 at 23:38
  • can u help in trimming the data here ?? so that I can try and check – Nad Dec 16 '21 at 05:50
  • @hud Just use `TRIM` on the `COLUMN_VALUE` output (and, if you need to, use it 3 times to trim spaces first then newlines `CHR(10)` second then spaces again third). – MT0 Dec 16 '21 at 09:06
  • ok, but what I want final output should look like this `'7507, 22781, 24949'`.. ok will try and update you accordingly – Nad Dec 16 '21 at 10:32