WITH mydata AS (
SELECT DBMS_LOB.substr(summary, 4000) test
    FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
    FROM (SELECT REGEXP_REPLACE (replace(summary,'-','.'), '[' ||  REGEXP_REPLACE (replace(summary,'-','.') || '!', '[^[:punct:]]')  || ']') test 
    FROM my_table))
),
splitted_words as 
( 
SELECT REGEXP_SUBSTR(TEST,'[^#]+', 1, level) AS word 
FROM mydata 
CONNECT BY level <= LENGTH(regexp_replace(TEST,'[^#]')) + 1 
AND PRIOR ROWID = ROWID
AND PRIOR sys_guid() IS NOT NULL 
)
SELECT word, 
COUNT(1) 
FROM splitted_words
GROUP BY word
ORDER BY COUNT(1) DESC;

FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)

FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)

SQL> WITH mydata AS (
  2  SELECT DBMS_LOB.substr(summary, 4000) test
  3      FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
  4      FROM (SELECT REGEXP_REPLACE (replace(summary,'-','.'), '[' ||  REGEXP_REPLACE (replace(summary,'-','.') || '!', '[^[:punct:]]')  || ']') test 
  5      FROM my_table))
  6  ),
  7  splitted_words as 
  8  ( 
  9  SELECT REGEXP_SUBSTR(TEST,'[^#]+', 1, level) AS word 
 10  FROM mydata 
 11  CONNECT BY level <= LENGTH(regexp_replace(TEST,'[^#]')) + 1 
 12  AND PRIOR ROWID = ROWID
 13  AND PRIOR sys_guid() IS NOT NULL 
 14  )
 15  SELECT word, 
 16  COUNT(1) 
 17  FROM splitted_words
 18  GROUP BY word
 19  ORDER BY COUNT(1) DESC;
    FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
                         *
ERROR at line 3:
ORA-00907: missing right parenthesis