oracle DBMS LOB.SUBSTR 抛出 ORA-00904：无效标识符错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10118994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DBMS LOB.SUBSTR Throwing ORA-00904: Invalid Identifier Error
提问by sunil kumar
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.
我们的一个CUTOMER的运行在Oracle SQL Developer中的脚本来升级自己的数据库表结构，procudere和触发器等，但运行脚本的同时，他越来越ORA-00904: Invalid Identifier Error for DBMS_LOB.SUBSTR()并DBMS_LOB.GETLENGTH()在一个过程。
Can somebody tell why is happening so?
有人能说出为什么会这样吗？
There are using Oracle Sql developer Version 3.1.07 Build MAIN-07.42 with Oracle 11g.
有使用 Oracle Sql developer Version 3.1.07 Build MAIN-07.42 with Oracle 11g。
回答by Marcin Wroblewski
I imagine 3 possible reasons:
我想有3个可能的原因：
- the user lacks execute privilege on sys.dbms_lob (although the privilege is granted to PUBLIC by default)
- there is no synonym dbms_lob for sys.dbms_lob in the database (although such public synonym should exist)
- the schema, on which the customer works, contains some other package with the same name DBMS_LOB
- 用户缺乏对 sys.dbms_lob 的执行权限（尽管默认情况下该权限授予 PUBLIC）
- 数据库中没有 sys.dbms_lob 的同义词 dbms_lob（虽然应该存在这样的公共同义词）
- 客户在其上工作的架构包含其他一些同名的包 DBMS_LOB
回答by Shi Lei
Run this sql with sys to check whether your schema has privilege to execute DBMS_LOB.
使用 sys 运行此 sql 以检查您的架构是否具有执行 DBMS_LOB 的权限。
select * from dba_tab_privs where table_name='DBMS_LOB';
By default, you should see PUBLIC in the grantees.
默认情况下，您应该在受赠者中看到 PUBLIC。
If not, you can run sql with sys.
如果没有，您可以使用 sys.sql 运行 sql。
grant execute on sys.DBMS_LOB to public;
or
或者
grant execute on sys.DBMS_LOB to <your_schema_name>
