While developing my CMS lite app I sometimes need to have a look at the state of certain regions or package function/procedures results. Thanks to the chapter Debugging an Application in the Application Express User's Guide I started with the #timing# tag, which does provide some information on the execution time (per region) which you can see at the bottom of my page. But I wanted also some output on packages, so I searched OTN and found a nice introduction at Injecting custom debug output into debug mode. The rest is easy, using a debug procedure (which is part of a package) that will work both inside SQL*PLUS (or any other tool) and APEX:

  PROCEDURE Debug(
    p_Message IN VARCHAR2
  )
  IS
   BEGIN
     IF v_Debug_Mode
     THEN
        DBMS_OUTPUT.PUT_LINE(p_Message);
     ELSIF wwv_flow.g_debug
     THEN
        htp.p(p_Message || '<br>');
     END IF;
  END Debug;

  PROCEDURE Debug(
    p_Message IN VARCHAR2
  )
  IS
   BEGIN
     IF v_Debug_Mode
     THEN
        DBMS_OUTPUT.PUT_LINE(p_Message);
     ELSIF wwv_flow.g_debug
     THEN
        wwv_flow.debug(p_Message);
     END IF;
  END Debug;

Somehow this question slipped my last years Odd things (on OTN) series, so I decided to collect several methods for this problem. Sort of a reverse String Aggregation Techniques. ;-)

Version 1: CONNECT BY (taken from MODEL clause vs. old school SQL)

WITH t AS (SELECT '10,12,20,30,30,3,12,12,56' col1
             FROM dual
          )
SELECT SUBSTR(col1
             ,DECODE(level, 1, 1, INSTR(col1,',',1,level-1)+1)
             ,DECODE(INSTR(col1,',',1,level), 0, LENGTH(col1), instr(col1,',',1,level)
             -DECODE(level, 1, 0, INSTR(col1,',',1,level-1))-1)
             ) col_new
  FROM t
  CONNECT BY level < LENGTH(REPLACE(TRANSLATE(col1,'01234567890','00000000000'),'0')) + 2
; 
COL_NEW
----------
10
12
20
30
30
3
12
12
56

WITH t AS (SELECT '10,12,20,30,30,3,12,12,56' col1
             FROM dual
          )
SELECT REGEXP_SUBSTR(col1, '[^,]+', 1, ROWNUM) col_new
  FROM t
  CONNECT BY level <= LENGTH(REGEXP_REPLACE(col1, '([^,])+|.', '\1'))
; 

COL_NEW
----------
10
12
20
30
30
3
12
12
56

CREATE OR REPLACE PACKAGE utl_strings
AS
  TYPE t_outtab IS TABLE OF VARCHAR2(255);
  
  FUNCTION str_to_rows(
    p_string IN VARCHAR2             -- source string
  , p_delim  IN VARCHAR2 DEFAULT ',' -- delimiteter 
  ) RETURN t_outtab PIPELINED
  ;
END utl_strings;
/ 
 
CREATE OR REPLACE PACKAGE BODY utl_strings
AS
  FUNCTION str_to_rows(
    p_string IN VARCHAR2             -- source string
  , p_delim  IN VARCHAR2 DEFAULT ',' -- delimiteter 
  ) RETURN t_outtab PIPELINED
  IS
    v_start   NUMBER := 1;
    v_len     NUMBER := NVL(LENGTH(p_string), 0);
    v_pos     NUMBER;
  BEGIN  
    WHILE v_start <= v_len 
    LOOP
      v_pos := INSTR(p_string, p_delim, v_start);  
    
      IF v_pos = 0 THEN 
         v_pos := v_len + 1;
      END IF;
         
      PIPE ROW(SUBSTR(p_string, v_start, v_pos - v_start));
      
      v_start := v_pos + 1;   
    END LOOP;  
  END str_to_rows;  
END utl_strings;
/ 

SELECT column_value col_new
  FROM TABLE(utl_strings.str_to_rows('10,12,20,30,30,3,12,12,56'))
;

COL_NEW
----------
10
12
20
30
30
3
12
12
56

WITH t AS (SELECT '10,12,20,30,30,3,12,12,56' col1
             FROM dual
          )
SELECT col_new
  FROM t
 MODEL
   PARTITION BY (ROWNUM rn)
   DIMENSION BY (0 dim)
   MEASURES(col1, col1 col_new)
   RULES ITERATE(99) UNTIL (ITERATION_NUMBER = LENGTH(REGEXP_REPLACE(col1[0], '[^,]')))
                (col_new[ITERATION_NUMBER] = REPLACE(REGEXP_SUBSTR(col1[0], '(^|,)[^,]*', 1, ITERATION_NUMBER+1), ','))
;

COL_NEW
----------
10
12
20
30
30
3
12
12
56