ORA-00904 "DBMS_LOB"."SUBSTR": invalid identifier Failed

  • KM02555160
  • 13-Sep-2016
  • 13-Sep-2016

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: .....

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

  1. Kindly run the following sql with sys to check whether  qcsiteadmin_db schema has privilege to execute DBMS_LOB.
                  Select * from dba_tab_privs where table_name='DBMS_LOB'; 
  1. By default there should be PUBLIC in the grantees or qcsiteadmin_db.
      3.  If not can you please run sql with sys. 

                 Grant execute on sys.DBMS_LOB to <your_schema_name> 
  1. 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"