CREATE OR REPLACE
TYPE spis_integer AS OBJECT
(
ID NUMBER
)
/
CREATE OR REPLACE
TYPE Int_Table AS TABLE OF spis_integer
/
CREATE OR REPLACE FUNCTION Get_Spis_Integer(p_spis IN VARCHAR2)
RETURN Int_Table
DETERMINISTIC
AS
mytbl Int_Table := Int_Table();
i NUMBER;
j NUMBER;
k NUMBER;
k1 NUMBER;
k2 NUMBER;
v_spis_str VARCHAR2(100);
v_str VARCHAR2(100);
v_spis NUMBER;
v_error BOOLEAN;
iCount NUMBER;
maxCount NUMBER DEFAULT 100000;
BEGIN
IF p_spis IS NULL THEN
RETURN mytbl;
END IF;
i := 1;
iCount := 0;
LOOP
j := INSTR(p_spis, ',', i);
IF j = 0 THEN
j := INSTR(p_spis, ';', i);
END IF;
BEGIN
IF j > 0 THEN
v_spis_str := SUBSTR(p_spis, i, j-i);
ELSE
v_spis_str := SUBSTR(p_spis, i);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- EXIT WHEN v_spis_str IS NULL;
k := INSTR(v_spis_str, '-', 1);
BEGIN
IF k > 0 THEN
k1 := trim(SUBSTR(v_spis_str, 1, k-1));
k2 := trim(SUBSTR(v_spis_str, k+1));
ELSE
k1 := v_spis_str;
k2 := k1;
END IF;
v_error := FALSE;
EXCEPTION
WHEN OTHERS THEN
v_error := TRUE;
DBMS_OUTPUT.put_line ('Error in (FUNCTION Get_Spis_Integer)' || CHR(13) ||
SQLERRM || CHR(13) || p_spis=' || p_spis);
END;
IF v_error = FALSE AND k1 IS NOT NULL THEN
k2 := NVL(k2, k1);
FOR k IN k1 .. k2 LOOP
mytbl.extend(1);
mytbl(mytbl.LAST):=spis_integer(k);
EXIT WHEN mytbl.LAST > maxCount;
END LOOP;
END IF;
iCount := iCount + 1;
i := j+1;
EXIT WHEN j = 0;
EXIT WHEN iCount > 100000;
END LOOP;
RETURN mytbl;
END;
/
SELECT * FROM the(SELECT cast(Get_Spis_Integer(:p_spis) AS Int_Table) FROM dual)
