CREATE OR REPLACE FUNCTION "FIND_IN_SET" (piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',')
RETURN NUMBER IS
l_idx number:=0;
str varchar2(500);
piv_str varchar2(500) := piv_str2;
res number:=0;
BEGIN
IF instr(piv_str, p_sep, 1) = 0 THEN
IF piv_str = piv_str1 THEN
res:= 1;
END IF;
ELSE
LOOP
l_idx := instr(piv_str,p_sep);
IF l_idx > 0 THEN
str:= substr(piv_str,1,l_idx-1);
IF str = piv_str1 THEN
res:= 1;
EXIT;
END IF;
piv_str := substr(piv_str,l_idx+length(p_sep));
ELSE
IF piv_str = piv_str1 THEN
res:= 1;
END IF;
EXIT;
END IF;
END LOOP;
END IF;
RETURN res;
END FIND_IN_SET;
CREATE OR REPLACE FUNCTION "GETAPPEARCHILDLIST" (rootcode IN VARCHAR)
RETURN VARCHAR
AS
RESULT_CODES VARCHAR2(2000);
BEGIN
select wmsys.wm_concat(CODE) INTO RESULT_CODES
FROM (select CODE from PRE_APPEAR_STRATEGY start with code=rootcode connect by prior code=PARENT_CODE);
RETURN RESULT_CODES;
END;
END GETAPPEARCHILDLIST;