String Concatenation Using Table Function
The function below takes SYS_REFCURSOR type data as input and returns a concatenated string is output. This function is setup as pipelined table function.

CREATE OR REPLACE TYPE string_list IS TABLE OF VARCHAR2(32767);
/

CREATE or replace FUNCTION concat_codes(cur IN SYS_REFCURSOR, i_separator IN VARCHAR2)
RETURN string_list DETERMINISTIC PIPELINED
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;
   PIPE ROW(v_str);
   RETURN;
EXCEPTION
  WHEN OTHERS THEN
   RAISE;
END concat_codes;
/

Back



Oracle registered trademark of Oracle Corporation.
Last Revised On: November 03, 2013

  3432