CREATE or replace FUNCTION concat_codes(cur IN SYS_REFCURSOR, i_separator IN VARCHAR2) RETURN VARCHAR2 IS v_str VARCHAR2(32767) := NULL; v_col_value VARCHAR2(4000); BEGIN LOOP FETCH cur INTO v_col_value; EXIT WHEN cur%NOTFOUND; IF v_str IS NULL THEN v_str := v_col_value; ELSE v_str := v_str || i_separator || v_col_value; END IF; END LOOP; CLOSE cur; -- avoids ORA-00604 and ORA-01000 error RETURN v_str; EXCEPTION WHEN OTHERS THEN CLOSE cur; RAISE; END concat_codes; /
SELECT concat_codes(CURSOR( SELECT q_data.hcpcs_cd FROM (SELECT '99001' hcpcs_cd FROM DUAL UNION SELECT '99002' hcpcs_cd FROM DUAL UNION SELECT '99003' hcpcs_cd FROM DUAL UNION SELECT '99004' hcpcs_cd FROM DUAL UNION SELECT '99005' hcpcs_cd FROM DUAL UNION SELECT '99006' hcpcs_cd FROM DUAL UNION SELECT '99007' hcpcs_cd FROM DUAL ) q_data), '|') concat_hcpcs_codes FROM DUAL;
Back
74696