我希望在一个oracle过程中为多个JOBID's
选择数据,因此在这样做时,我得到的错误为
ORA-06502: PL/SQL:数值或值错误:字符转换错误
由于这个原因,我将JOBID
的数据类型更改为CLOB
,但仍然没有任何东西工作,也没有得到相同的错误。
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;
另外,STR2TBL
是我内置的函数。
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;
发布于 2021-12-14 21:58:19
您的过程效率很低,因为它不断地用较小的子字符串覆盖CLOB
。
相反,您可以跟踪每个术语前后逗号的位置,并使用此函数 (将数据类型更改为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;
/
然后你可以像这样使用它:
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 https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9d3ba55a2136b13613b4188840e45a96
https://stackoverflow.com/questions/70355832
复制相似问题