Summary
Project verify error ORA-00904: "DBMS_LOB"."SUBSTR": invalid identifier Failed
Error
Error message when verifiying project:
[Mercury][Oracle JDBC Driver][Oracle]ORA-00904: "DBMS_LOB"."SUBSTR": invalid identifier Failed SQL: .....
[Mercury][Oracle JDBC Driver][Oracle]ORA-00904: "DBMS_LOB"."SUBSTR": invalid identifier Failed SQL: .....
Cause
Based on the error "ORA-00904: "DBMS_LOB"."SUBSTR": invalid identifier" the probable cause is database users lacks execute privilege on sys.dbms_lob
Fix
- Kindly run the following sql with sys to check whether qcsiteadmin_db schema has privilege to execute DBMS_LOB.
- By default there should be PUBLIC in the grantees or qcsiteadmin_db.
Grant execute on sys.DBMS_LOB to <your_schema_name>
- Please have the organization DBA to run the commands against “dbms_lob” and sys.dbms_sql to qcsiteadmin and ALM projects db as well:
- Grant execute on sys.dbms_sql to qcsiteadmin_db.
- Grant execute on sys.dbms_sql to DOMAIN_PROJECT_DB
- Grant execute on dbms_lob to qcsiteadmin_db.
- Grant execute on dbms_lob to DOMAIN_PROJECT DB
Please also check a related article as a reference regarding the Oracle error which might be useful :
http://stackoverflow.com/questions/10118994/dbms-lob-substr-throwing-ora-00904-invalid-identifier-error
"DBMS LOB.SUBSTR Throwing ORA-00904: Invalid Identifier Error"