String Concatenation Function With SYS_REFCURSOR Input
The function below takes SYS_REFCURSOR type data as input and returns a concatenated string as output.
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;



String Concatenation - Table Function

Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 25, 2014

  1096