oracle sql 报错解决办法
1、Error(13,50): PL/SQL: ORA-00984: 列在此处不允许
解决办法：列与你插入的值类型不一致 寒门贵子刘媛媛
解决办法：
INSERT INTO TAB_EMPLOYEE(id,emp_no,emp_name,ur_name,password,role,privilege,flag )VALUES(ur_id,emp_no,emp_name,ur_name, emp_password, emp_role, emp_privilege, emp_flag );
2、Error(13,54): PLS-00049: 错误的赋值变量 'EMP_NO'
解决办法： INSERT INTO TAB_EMPLOYEE(id,emp_no)VALUES(ur_id, ： emp_no );
Error(13,28): PL/SQL: ORA-01733: 此处不允许虚拟列
INSERT INTO TAB_EMPLOYEE(id,emp_no )VALUES(ur_id,emp_no);
在存储过程中的 TAB_EMPLOYEE(id,emp_no) 中的列名必须数数据库表中已存在的列，不能自己随便术数
写列名
3、Error(6,10): PLS-00103: 出现符号 "VARCHAR2" 在需要下列之一时： := . ( @ % ; 符号 ":=" 被替换为 "VARCHAR2" 后继续。
解决办法：可能是声明变量的位置不对，应该在 AS 或 IS 后面， begin 前面
Error(5,9): PLS-00215: 字符串长度限制在范围 (1...32767)
夸人的成语
解决办法：需要指明 varchar2 的数据长度
CREATE OR REPLACE
FUNCTION FINDEMPID
( emp_name IN VARCHAR2
) RETURN VARCHAR2 AS
emp_id VARCHAR2(10);
BEGIN
。。。
END;
4、在将SYBASE的Trigger移植到ORACLE的时候发现一个问题，
就是在一张表的Trigger中不能含有操作该基表的SQL，
例如STOCKINFO的Trigger里不有操作STOCKINFO的SQL。
那加权平均价如何取呢？
现在只能在原平均价的基础再与新价进行加权平均，结果是一样的。
注意：
AFTER UPDATE的Trigger不能操作基表，只有在BEFORE INSERT的Trigger里才能操作基表。
BEFORE INSERT的Trigger只有用INSERT INTO T1(COL1) VALUES('1')；才有效，
INSERT INTO T1(COL1) SELECT '1' FROM DUAL；也是无效的。
例如：
SQL> inrt into t1 values (1,'a'); --先插入一条数据，避免ORA-01403: no data found 错误。
1 row inrted
SQL> commit;
SQL> create or replace trigger tri_t1
2 before inrt on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 lect 'Y' into cvar from t1 WHERE ROWNUM=1;
7 end;
8 /
Trigger created
SQL> inrt into t1 values (2,'b'); -- inrt into ... values 没有问题
1 row inrted
SQL> inrt into t1 lect '3','c' from dual; -- inrt into ... lect .. from 报错
ORA-04091: table TEST.T1 is mutating, trigger/function may not e it滴水观音菩萨
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'
并且:old和:new只有在行级（for each row）的Trigger有效。
网上原文：
ORA-04091与 table mutating
卧薪尝胆的故事简短
近日解决了一个trigger中报ORA-04091错误的问题，补了关于Oracle table mutating的一课：
mutating table 是指一个当前正在被update,delete,inrt语句修改的表，如果在一个行级别的trigger中读取或修改一个mutating table，则往往会遇到ORA-04091错误.例如，如果
在trigger中使用了lect或dml 语句访问trigger所在的表，则就会收到这个错误。
然而，Oracle8i和9i文档中 都没有解释清楚before和after 类型的 row trigger 在对待两种不同的inrt语句(inrt into ... values ... 与 inrt into ... lect ...)时的差别：
霜降诗句1、对于after 类型的 for each row 级别的triggers，不论哪种inrt语句触发了trigger，都不允许在 trigger 中访问本trigger所依赖的table的，测试如下：
SQL> create table t1 ( c1 number,c2 varchar2(10));
Table created
SQL> create or replace trigger tri_t1
2 after inrt on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 lect 'Y' into cvar from t1 WHERE ROWNUM=1; --这里访问了trigger 本表
7 end;
8 /
Trigger created
SQL> inrt into t1 values (1,'a');
ORA-04091: table TEST.T1 is mutating, trigger/function may not e it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TES.TRI_T1'
SQL> inrt into t1 lect '1','a' from dual;
ORA-04091: table TEST.T1 is mutating, trigger/function may not e it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'
2、对于before 类型的 for each row 级别的triggers，如果使用 inrt into ... values 语句触发此trigger ，则在trigger 中访问本table没有问题；
但如果使用 inrt into lect .. from 语句触发此trigger ，则在trigger 中访问本table就报ora-04091错误；
只有在Oracle 7标准的开发文档中有这样的说明：
From the Application Developers Guide
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers.
INSERT statements that involve more than 1 row are not considered
single row inrts."
"INSERT INTO <table_name> SELECT ..." are not considered single row
inrts, even if they only result in 1 row being inrted.
测试如下：
SQL> drop trigger tri_t1;
Trigger dropped
SQL> inrt into t1 values (1,'a'); --先插入一条数据，避免ORA-01403: no data found 错误。
1 row inrted
SQL> commit;
SQL> create or replace trigger tri_t1
2 before inrt on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 lect 'Y' into cvar from t1 WHERE ROWNUM=1;
7 end;
8 /
Trigger created
SQL> inrt into t1 values (2,'b'); -- inrt into ... values 没有问题
1 row inrted
SQL> inrt into t1 lect '3','c' from dual; -- inrt into ... lect .. from 报错
ORA-04091: table TEST.T1 is mutating, trigger/function may not e it
ORA-06512: at "TEST.TRI_T1", line 4
衡水中学作息表ORA-04088: error during execution of trigger 'TEST.TRI_T1'
我们的开发人员因为不知道这个特别情况，近日在修改一个存储过程时候，将原来的inrt values 写法改成了inrt lect 写法，而trigger 又是before类型的，导致出现了ORA-04091错误，搞得分析了好久也没
有头绪。
其实，在metalink中有一篇note说到了：
文档 ID: 注释:132569.1
主题: ORA-4091 on BEFORE ROW TRIGGER with INSERT statement
类型: PROBLEM
状态: PUBLISHED
内容类型: TEXT/X-HTML
创建日期: 16-JAN-2001
上次修订日期: 09-AUG-2004
Problem Description
-------------------
You want to do an inrt into a table that has a BEFORE row Trigger.
When you hard code the values into the INSERT statement, the trigger works fine.
For example:
INSERT
INTO content (cont_name,cont_g,cat_q)
VALUES('blah',100,200);
1 row created.
However, your trigger errors with ERROR ORA-4091 with
lect statement:
INSERT
INTO content (cont_name,cont_q,cat_q) (from category);
ERROR at line 1:
ORA-4091: table <schema>.CONTENT is mutating, trigger/function may not e it
ORA-6512: at "<schema>.INS_CONTENT", line 4
ORA-4088: error during execution of trigger '<schema>.INS_CONTENT'
TRIGGER:
CREATE OR REPLACE trigger INS_CONTENT
BEFORE INSERT on CONTENT
FOR EACH ROW
DECLARE
max_sort number;
BEGIN
SELECT max(cont_sort) INTO max_sort FROM CONTENT;
IF max_sort IS NOT NULL AND max_sort!= 99999 THEN
IF :_sort IS NULL THEN
:_sort := max_sort +1;
END IF;
END IF;
SELECT SEQ_val INTO :new.CONT_SEQ from dual;
END;
Explanation
-----------
Error: ORA 4091
Text: table %s.%s is mutating, trigger/function may not e it
-------------------------------------------------------------------------------
Cau: A trigger (or a ur defined PL/SQL function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.
Explanation:
You cannot look at or modify the table that is mutating.
Note:
From the Application Developers Guide
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers.
INSERT statements that involve more than 1 row are not considered
single row inrts."
"INSERT INTO <table_name> SELECT ..." are not considered single row
开粮油店
inrts, even if they only result in 1 row being inrted.
RELATED DOCUMENTS
-----------------
Oracle Application Developer's Guide (A68003-01)
Chapter 'Using Databa Triggers', page 13-22)
5、Error(19,33): PL/SQL: ORA-00984: 列在此处不允许
插入的变量写错了。如：CREATE OR REPLACE
TRIGGER TENANT_TO_USERLOGIN_TRG
BEFORE INSERT ON TENANT
declare
maxid number;
name1 VARCHAR2(20);
cellphone1 VARCHAR2(20);
email1 VARCHAR2(40);
begin
lect max(id)--取得当前插入的租户编号
into maxid
from TENANT;
lect name,cellphone,email
into name1,cellphone1,email1
from tenant
where id=maxid;
inrt int
o urlogin(id_tenant,name,urname,cellphone,password,email)
values(maxid,name1,name1,ctllp hone1,'888888',email1)；应为：cellphone
END;
