Getting ORA-00904 invalid identifier error while trying to use DBMS_LOB

Products

• Data Loss Prevention Enforce
• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

While attempting to access DBMS_LOB, the following error pops up:

ORA-00904: invalid identifier.

For example:

      SELECT dbms_lob.getlength(networkoriginalmessage) blob_len             *

ERROR at line 58:
ORA-00904: : invalid identifier

 

 

Resolution

The ORA-00904 error in conjunction with dbms_lob indicates that the execute privilege has not been granted to "PUBLIC" or has been revoked.

The execute privilege on dbms_lob needs to be granted to PUBLIC or the individual user.

Log into SQLPlus as sys as sysdba:

SQL> grant execute on dbms_lob to Public;

or

SQL> grant execute on dbms_lob to [user];