对象表更新后出现 ORA-12899

发布于 2024-12-12 19:27:36 字数 827 浏览 1 评论 0原文

UPDATE ta_1 SET v_1 = 'new value' WHERE v_1 = 'value';
UPDATE ta_1 SET v_1 = 'newest value';

第一个更新查询返回异常 ORA-12899，第二个在此表上执行时不会返回异常：

CREATE OR REPLACE type t_2 AS object
  (v_1 VARCHAR2 (4000));

CREATE OR REPLACE type t_1 AS object
  (
    v_1 VARCHAR2 (4000),
    v_2 t_2);

CREATE TABLE ta_1 OF t_1
  (CHECK (v_2 IS NULL OR (v_2.v_1 = 'uFonec')) ENABLE
  ) ;

CREATE OR REPLACE TRIGGER ta_1_tr BEFORE
  INSERT OR
  UPDATE ON ta_1 FOR EACH ROW DECLARE BEGIN IF :new.v_2 IS NULL THEN :new.v_2 := NEW t_2 ('uFonec') ;
END IF;
END;

INSERT INTO ta_1 VALUES (t_1 ('value', NULL) ) ;

第一个更新查询返回：

SQL 错误：ORA-12899：列“TA_1”的值太大。“V_2”（实际值：1，最大值：0）

在表上禁用约束的情况下，两个更新查询都可以工作。在 Oracle-XE 10g2 上测试。这是一个错误还是在某些 Oracle 手册中写它是一个功能？

原文

UPDATE ta_1 SET v_1 = 'new value' WHERE v_1 = 'value';
UPDATE ta_1 SET v_1 = 'newest value';

First update query returns exception ORA-12899 and second doesn't when executed on this table:

CREATE OR REPLACE type t_2 AS object
  (v_1 VARCHAR2 (4000));

CREATE OR REPLACE type t_1 AS object
  (
    v_1 VARCHAR2 (4000),
    v_2 t_2);

CREATE TABLE ta_1 OF t_1
  (CHECK (v_2 IS NULL OR (v_2.v_1 = 'uFonec')) ENABLE
  ) ;

CREATE OR REPLACE TRIGGER ta_1_tr BEFORE
  INSERT OR
  UPDATE ON ta_1 FOR EACH ROW DECLARE BEGIN IF :new.v_2 IS NULL THEN :new.v_2 := NEW t_2 ('uFonec') ;
END IF;
END;

INSERT INTO ta_1 VALUES (t_1 ('value', NULL) ) ;

First update query returns:

SQL Error: ORA-12899: value too large for column "TA_1"."V_2" (actual: 1, maximum: 0)

With disabled constraint on table both update queries works. Tested on Oracle-XE 10g2. Is it a bug or in some Oracle manual it's written it is a feature?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不寐倦长更 2024-12-19 19:27:36

在 Oracle 11gR2 EE 上运行此命令（启用约束）：

SQL> INSERT INTO ta_1 VALUES (t_1 ('other value', t_2 ('blah')) ) ;
INSERT INTO ta_1 VALUES (t_1 ('other value', t_2 ('blah')) )
*
ERROR at line 1:
ORA-02290: check constraint (APC.SYS_C0021529) violated


SQL> UPDATE ta_1 SET v_1 = 'new value' WHERE v_1 = 'value';

1 row updated.

SQL> UPDATE ta_1 SET v_1 = 'newest value';

1 row updated.

SQL>

所以，也许这是 XE 中的一个错误。您不运行最新版本的任何原因吗？

Running this on Oracle 11gR2 EE (with the constraint enabled):

SQL> INSERT INTO ta_1 VALUES (t_1 ('other value', t_2 ('blah')) ) ;
INSERT INTO ta_1 VALUES (t_1 ('other value', t_2 ('blah')) )
*
ERROR at line 1:
ORA-02290: check constraint (APC.SYS_C0021529) violated


SQL> UPDATE ta_1 SET v_1 = 'new value' WHERE v_1 = 'value';

1 row updated.

SQL> UPDATE ta_1 SET v_1 = 'newest value';

1 row updated.

SQL>

So, maybe it is a bug in XE. Any reason why you're not running the latest version of it?

回复收藏 0 原文

~没有更多了~