ORA-30004 Error found while trying to see Attributes page
search cancel

ORA-30004 Error found while trying to see Attributes page

book

Article ID: 244880

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

We are unable to see Attributes in one of our clarity environments. We did a complete reboot of the app server but it still won't populate any results. Can you please advise of what else could be causing this? 

Environment

Release : 15.9.3

Component : Clarity Studio

Cause

ERROR 2022-06-22 15:15:41,920 [https-jsse-nio2-8443-exec-48] niku.xql2 (clarity:some_user:xxx:odf.objectAttributes) Internal Processing exception
com.niku.union.persistence.PersistenceException: 
SQL error code: 30004
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value

Executed:
 WITH OBS_PATHS as ( SELECT id, SYS_CONNECT_BY_PATH(name, '/') path,
                                      level lvl
                              FROM prj_obs_units WHERE 1=1  AND id in (5001002) CONNECT BY PRIOR id = parent_id  order by id,lvl desc )
          select id unit_id,path unit_path
            from OBS_PATHS otable
            where otable.lvl = ( select max(lvl) from OBS_PATHS itable
                                  where itable.id = otable.id) 

Resolution

Looking at the error in the app-ca.log it seems to indicate that one of the OBS values could be having / in the name.

The following query could be used to determine OBS values having "/" in the names. 

SELECT *
FROM PRJ_OBS_UNITS
WHERE NAME LIKE '%/%';

"/" was removed from the OBS values and the object attribute page is opening up with no errors.