Issue:
SCM user fails to log on to SCM Broker from Workbench or Administrator. HServer log displays this error message:
-- START: 2016/04/26 11:11:45 DB Connect#: 2 SINCE LAST SQL: 5
SELECT DBMS_LOB.GETLENGTH(ENCRYPTPSSWD) AS BLOBSIZE,
ENCRYPTPSSWD
FROM HARUSERDATA
WHERE USROBJID = ?
[CAI/PT][ODBC Oracle Dynamic driver][Oracle]ORA-00904: : invalid identifier
SQLSTATE=S0022
Environment:
CA Harvest SCM Broker on Linux / Windows platforms.
Resolution:
LOB.GETLENGTH(ENCRYPTPSSWD) function in the statement is what is seen as the invalid identifier.
In order to rectify this problem Run the following sql as the "system" user to check whether your schema has privilege to execute DBMS_LOB.
select * from dba_tab_privs where table_name='DBMS_LOB';
By default, you should see PUBLIC in the grantees.
If not, you can run sql with sys.
grant execute on sys.DBMS_LOB to public;
or
grant execute on sys.DBMS_LOB to <your_schema_name>