存储过程里的序列报错Error: PLS-00302: component 'SEQUENCE_NAME' must be declared
来源:互联网 发布:从哪里招聘美工 编辑:程序博客网 时间:2024/06/01 07:18
今天遇到一个奇怪的问题,存储过程的调用序列编译是报错 Error: PLS-00302: component 'SEQUENCE_NAME' must be declared
序列是存在,拿出来可以执行成功,序列加前缀了,最后找到原因了是因为用户下有一个表和用户名同名导致的,这是oracle一个bug 已经修复了影响的版本为PL/SQL - Version: 10.2.0.4 to 11.2.0.3 - Release: 10.2 to 11.2
如下图报错:
解决方法:
Rename/ drop any conflicting object with the same name as the schema name
SELECT OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME='USERNMAME'
原因:
An object exists with the same name as the schema owner causing a pl/sql compilation error.
Bug:1218383 PLS-327 WHEN COMPILE SCHEMA.SEQUENCE WITH EXISTING TABLE WITH SCHEMA NAME
以下在 11.2.0.1.0 的环境重现这个问题
C:\Users\Administrator>sqlplus u1/u1
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 11月 6 16:31:25 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
创建序列seq_u1
SQL> create sequence seq_u1
2 minvalue 1
3 maxvalue 9999999
4 start with 1
5 increment by 1
6 cache 5;
序列已创建。
创建和用户名同名的表u1
SQL> create table u1 (id number);
表已创建。
创建存储过程调用序列:
SQL> create or replace procedure next_val is
2 v_next number;
3 begin
4 select u1.seq_u1.Nextval into v_next from dual;
5
6 insert into test values (u1.seq_u1.NEXTVAL);
7 exception
8 when others then
9 null;
10 end next_val;
11 /
警告: 创建的过程带有编译错误。
编译有错误:(问题重现)
SQL> show error
PROCEDURE NEXT_VAL 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/14 PLS-00302: 必须声明 'SEQ_U1' 组件
6/31 PLS-00302: 必须声明 'SEQ_U1' 组件
SQL>
查询与用户名同名的对象:
SQL> SELECT OBJECT_NAME, OBJECT_TYPE
2 FROM ALL_OBJECTS
3 WHERE OBJECT_NAME='U1';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
U1 TABLE
删除同名对象
SQL> drop table u1;
表已删除。
从新编译,编译通过问题解决
SQL> alter procedure next_val compile;
过程已更改。
SQL> show error
没有错误。
SQL>
- 存储过程里的序列报错Error: PLS-00302: component 'SEQUENCE_NAME' must be declared
- PLS-00302: component 'SET_NO_OUTLINES' must be declared
- 也遭遇 PLS-00302: component 'SET_NO_OUTLINES' must be declared
- imp PLS-00302: component 'SET_NO_OUTLINES' must be declared【转载】
- imp PLS-00302: component 'SET_NO_OUTLINES' must be declared 问题的处理
- 数据库Server版本造成的导出错误PLS-00302: component 'SET_NO_OUTLINES' must be declared
- ORACLE 11G 触发器调试记录Error: PLS-00201: identifier'SYS.DBMS_SYSTEM' must be declared
- oracle 11.2.0.3 expdp 时报错,PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
- oracle adg PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
- 创建存储过程报错PLS-00410
- Android Studio报Element XXXX must be declared的解决方法
- Oracle数据库导出报componet'SET_NO_OUTLINES'must be declared的错
- 最新版AndroidStudio3.0 Canary 8的时候报错,Annotation processors must be explicitly declared now
- PLS-S-00201, identifier 'CALLDEMO.GET_EMPLOYEES' must be declared 预编译错误原因及解决办法
- Oracle 11gR2 RMAN Duplicate 触发PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared 错误
- PLS-00201: identifier 'SYS.DBMS_JOB' must be declared 错误处理办法
- EXP-00056:ORA-06550:PLS-00201: identifier ‘SYS.DBMS_EXPORT_EXTENSION’ must be declared
- oracle运行存储过程报PLS-00201
- 第一周项目三 程序的多文件组织
- EJB和Web容器中的资源或组件是如何查找的?
- VIM Usage
- [HDU 1422]重温世界杯(DP)
- 第11周项目1-函数版星号图(2)
- 存储过程里的序列报错Error: PLS-00302: component 'SEQUENCE_NAME' must be declared
- copy redis data from server A to server B
- 删除数据----DELETE AND TRUNCATE TABLE
- C4.5算法详解(至今见过写的最好的算法详解)
- 我遇到的java问题
- ListView中getView的原理+如何在ListView中放置多个item
- 第十一周项目1 (3)函数版小星星(ABC+++++)
- Highways
- HDU 1.3.7 悼念512汶川大地震遇难同胞——老人是真饿了