Output Multiple Values As String
-- PL/SQL script to create a string of numbers

SET SERVEROUTPUT ON;

DECLARE
 CURSOR c_rec IS
  SELECT  level  idx
  FROM  DUAL
  CONNECT BY level <= 9;

  v_idx        SIMPLE_INTEGER :=-1; 
  v_cur_idx    SIMPLE_INTEGER :=0;
  v_idx_str    VARCHAR2(100);
BEGIN
  FOR rec IN c_rec LOOP

     v_idx := rec.idx;
     
     IF v_cur_idx != v_idx THEN

        v_cur_idx := v_idx;
        
        IF v_idx_str IS NULL THEN
           v_idx_str := v_idx;
        ELSE
           v_idx_str := v_idx_str||','||v_idx;
        END IF;
     END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('String Output: '||v_idx_str); 
END;
/

------- Output ----------------------------

anonymous block completed
String Output: 1,2,3,4,5,6,7,8,9


-- PL/SQL script to create a string of odd numbers

SET SERVEROUTPUT ON;

DECLARE
 CURSOR c_rec IS
  SELECT  level  idx
  FROM  DUAL
  CONNECT BY level <= 9;

  v_odd_str    VARCHAR2(100);
BEGIN
  FOR rec IN c_rec LOOP

      IF MOD(rec.idx,2) != 0 THEN

        IF v_odd_str IS NULL THEN
           v_odd_str := rec.idx;
        ELSE
           v_odd_str := v_odd_str||','||rec.idx;
        END IF;

      END IF;
        
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Odd Number String: '||v_odd_str); 
END;
/

------- Output ----------------------------

anonymous block completed
Odd Number String: 1,3,5,7,9

Comma_To_Table

Oracle registered trademark of Oracle Corporation.

Last Revised On: December 27th, 2013

  262