CREATE OR REPLACE PACKAGE CONVERT_PKG IS TYPE STRING_TBL_TYPE IS TABLE OF VARCHAR2(4000); FUNCTION LIST_TO_TABLE( p_LIST IN VARCHAR2, p_DELIMITER IN VARCHAR2 DEFAULT ',' ) RETURN STRING_TBL_TYPE; FUNCTION TABLE_TO_LIST( p_TBL IN STRING_TBL_TYPE, p_DELIMITER IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY CONVERT_PKG IS FUNCTION LIST_TO_TABLE( p_LIST IN VARCHAR2, p_DELIMITER IN VARCHAR2 DEFAULT ',' ) RETURN STRING_TBL_TYPE IS v_STRING_TBL STRING_TBL_TYPE := STRING_TBL_TYPE(); v_START_POSITION NUMBER; v_END_POSTION NUMBER; v_DELIMITER_LENGTH NUMBER; v_EXPANDED_LIST VARCHAR2(32767); BEGIN IF p_DELIMITER IS NULL THEN RAISE_APPLICATION_ERROR( -20500, 'Invalid Delimiter' ); END IF; v_EXPANDED_LIST := p_LIST || p_DELIMITER; v_DELIMITER_LENGTH := LENGTH( p_DELIMITER ); v_END_POSTION := 1 - v_DELIMITER_LENGTH; LOOP v_START_POSITION := v_END_POSTION + v_DELIMITER_LENGTH; v_END_POSTION := INSTR( v_EXPANDED_LIST, p_DELIMITER, v_START_POSITION ); EXIT WHEN v_END_POSTION = 0; v_STRING_TBL.EXTEND; v_STRING_TBL(v_STRING_TBL.LAST) := SUBSTR( v_EXPANDED_LIST, v_START_POSITION, v_END_POSTION - v_START_POSITION ); END LOOP; RETURN v_STRING_TBL; END; FUNCTION TABLE_TO_LIST( p_TBL IN STRING_TBL_TYPE, p_DELIMITER IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2 IS v_LIST VARCHAR2(32767); BEGIN IF p_DELIMITER IS NULL THEN RAISE_APPLICATION_ERROR( -20500, 'Invalid Delimiter' ); END IF; FOR v_I IN 1..p_TBL.LAST LOOP IF INSTR( p_TBL(v_I), p_DELIMITER ) != 0 THEN RAISE_APPLICATION_ERROR( -20500, 'Invalid Delimiter' ); END IF; IF v_I > 1 THEN v_LIST := v_LIST || p_DELIMITER || p_TBL(v_I); ELSE v_LIST := v_LIST || p_TBL(v_I); END IF; END LOOP; RETURN v_LIST; END; END; /
Или
SELECT SUBSTR(txt,instr(sp
||txt
||sp,sp,1,level),instr(sp
||txt
||sp,sp,2,level) - instr(sp
||txt
||sp,sp,1,level) - 1) pol,
rownum nn
FROM
(SELECT 'qq,ww,ee' AS txt, ',' AS sp FROM dual
)
CONNECT BY level <= LENGTH(regexp_replace(sp
||txt,'[^'
||sp
||']',''))
