DECLARE
 L_curr_POS NUMBER := 1;
 l_nxt_pos number := 0;
 L_LEN NUMBER;
 L_STR varchar2(500) := 'SELECT 1, 2, 3, 4, 5 FROM DUAL, DUAL';
 l_cnt number := 0;
l_occ number := 0;
BEGIN
  SELECT LENGTH( SUBSTR ('SELECT 1, 2, 3, 4, 5 FROM DUAL, dual', 1, INSTR('SELECT 1, 2, 3, 4, 5 FROM DUAL, dual', 'FROM')-1))     INTO L_LEN FROM DUAL;
  DBMS_OUTPUT.PUT_LINE ('TOTAL length  '||l_len);
  FOR i in 1..l_len loop
    SELECT INSTR( 'SELECT 1, 2, 3, 4, 5 FROM DUAL, dual', ',',1, l_occ+1)
                 INTO l_nxt_pos FROM DUAL;
   DBMS_OUTPUT.PUT_LINE ('curr pos  '||l_nxt_pos);
  IF l_nxt_pos > 0 and l_nxt_pos < l_len THEN
    l_cnt := l_cnt + 1;
    l_occ := l_occ + 1;
  END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('TOTAL COLUMNS  '||l_cnt+1);
END;