ORA-00907: missing right parenthesis ! while creating view

ORA-00907: missing right parenthesis 

"ORA-00907: missing right parenthesis" can happen if you really missing it in your SQL, But if that is the case you will not search on google.com :).

One of Our oracle SQL developers reported that while creating a view on one of the oracle database were facing "ORA-00907: missing right parenthesis". We started troubleshooting and found that it's a bug from the oracle in 12.2 databases.  The solution is just to apply the patch or change the view of the code. 

CREATE OR REPLACE FORCE VIEW "TEST_V" ("TEST_COL1", "TEST_COL2") AS

SELECT

  -----

FROM TEST_T A WHERE TEST_COL1 IS NOT NULL

GROUP BY

  -----

  REGEXP_REPLACE(LISTAGG(TEST_COL1,';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY TEST_COL1),'([^;]+)(;\1)*(;|$)', '\1\3') AS TEST,

                                        *

ERROR at line 12:

ORA-00907: missing right parenthesis 

This view we were able to create in 12.1 but not in 12.2  so we in the last asked developers to change the view. That is how we handle "ORA-00907: missing right parenthesis" issue.  If you are facing the same issue and are not able to find any clue, just try to create it on the older version. The actual information regarding the bug is not available yet but we update soon we will find it. 

Oracle Means company, not database :) Keep sharing...