Comma Separated String Using COUNT(*) OVER()
-- Create a comma separated string using
-- COUNT(*) OVER()

DECLARE
 CURSOR c_id IS
  WITH q_data AS
   (SELECT  'A'||LPAD(level,2,'0') tst_cd 
    FROM DUAL
    CONNECT BY level <=10)
  SELECT tst_cd, COUNT(*) OVER() cnt
  FROM q_data;

 v_idx    SIMPLE_INTEGER := 0;
 v_str    VARCHAR2(200);
BEGIN
 FOR id_rec IN c_id LOOP
  v_idx := v_idx + 1;

  IF v_idx < id_rec.cnt THEN
    v_str := v_str||id_rec.tst_cd||', '; 
  ELSE
    v_str := v_str||id_rec.tst_cd;
  END IF;

 END LOOP;
 DBMS_OUTPUT.PUT_LINE(CHR(10)||v_str);
END;
/
   Comman Separated String

Count(*) OVER() and SUM() OVER()

Comma To Table Using DBMS_UTILITY

String Concatenation

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 11th, 2013

  1148