DBMS LOB.SUBSTR Throwing ORA-00904: Invalid Identifier Error

oracle oracle11g ora-00904
sunil kumar picture sunil kumar · Apr 12, 2012 · Viewed 13.3k times · Source

One of our cutomer is running the scripts in oracle sql developer to upgrade his database table structure, procudere & triggers etc. But while running the script, he is getting ORA-00904: Invalid Identifier Error for DBMS_LOB.SUBSTR() and DBMS_LOB.GETLENGTH() in one procedure.

Can somebody tell why is happening so?

There are using Oracle Sql developer Version 3.1.07 Build MAIN-07.42 with Oracle 11g.

Answer

Marcin Wroblewski picture Marcin Wroblewski · Apr 12, 2012

I imagine 3 possible reasons:

  1. the user lacks execute privilege on sys.dbms_lob (although the privilege is granted to PUBLIC by default)
  2. there is no synonym dbms_lob for sys.dbms_lob in the database (although such public synonym should exist)
  3. the schema, on which the customer works, contains some other package with the same name DBMS_LOB

Related questions

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More
How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More
ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client

I'm trying to connect to a schema on 11g (v11.2.0.1.0) from a PC with 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one - it comes back with a ORA-01017 Invalid Username/Password error …

Read More