PLS-00357: Table,View Or Sequence reference not allowed in this context

最新推荐文章于 2023-02-28 16:10:17 发布

weixin_30468137

最新推荐文章于 2023-02-28 16:10:17 发布

阅读量576

点赞数

CC 4.0 BY-SA版权

文章标签： php 数据库

原文链接：http://www.cnblogs.com/freewater/archive/2011/05/25/2057272.html

本文探讨了SQL语句中出现的错误“PLS-00357”，详细解释了其原因及解决方案，并通过实例展示了如何正确使用序列、表和视图引用。此外，文章还提醒读者在使用函数时避免直接引用数据库对象，强调了在PL/SQL环境中正确引用对象的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PLS-00357: Table,View Or Sequence reference 'string' not allowed in this context
    Cause: A reference to database table, view, or sequence was found in an inappropriate context. Such references can appear only in SQL statements or (excluding sequences) in %TYPE and %ROWTYPE declarations. Some valid examples follow: SELECT ename, emp.deptno, dname INTO my_ename, my_deptno, my_dept .FROM emp, dept WHERE emp.deptno = dept.deptno; DECLARE last_name emp.ename%TYPE; dept_rec dept%ROWTYPE;
    Action: Remove or relocate the illegal reference.

BEGIN
FOR i IN 1 .. 984 LOOP
    EXECUTE IMMEDIATE 'INSERT INTO OSM_DML_3SP.OSM_TAB_SEQUENCE(COL_0) VALUES(:1)'
       USING OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.NEXTVAL;
END LOOP;
INSERT INTO OSM_DML_3SP.OSM_TAB_SEQUENCE(COL_0)
        VALUES(OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.CURRVAL-45);
END;
/

SQL> @/tmp/a
       USING OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.NEXTVAL;
                                              *
ERROR at line 4:
ORA-06550: line 4, column 47:
PLS-00357: Table,View Or Sequence reference
'OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.NEXTVAL' not allowed in this context
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

Error use:
t := OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.NEXTVAL;

Correct use:
SELECT OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.NEXTVAL INTO t FROM dual;

>>>---
DECLARE
t NUMBER;
BEGIN
FOR i IN 1 .. 984 LOOP
    SELECT OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.NEXTVAL INTO t FROM dual;
    EXECUTE IMMEDIATE 'INSERT INTO OSM_DML_3SP.OSM_TAB_SEQUENCE(COL_0) VALUES(:1)'
       USING t;
END LOOP;
INSERT INTO OSM_DML_3SP.OSM_TAB_SEQUENCE(COL_0)
        VALUES(OSM_DML_3SP.OSM_TAB_SEQUENCESEQ9.CURRVAL-45);
END;

记得函数中有COMMIT, ROLLBACK时也不可以直接用的, 真要好好注意, 感觉使用起来很怪!
FUNCTION 中含有Commit, rollback 出错的情况请看:http://blog.chinaunix.net/u/19782/showart.php?id=242108