如何在多级嵌套表中插入时修复"ORA-01401: inserted value too large for column"

Question

下面是表/对象类型创建查询。表是成功创建的

CREATE TYPE ft_obj AS OBJECT (
    ftid         NUMBER(5),
    ftlocation   VARCHAR(30),
    country      VARCHAR(10)
);
/
CREATE TABLE ft_table OF ft_obj (
    ftid PRIMARY KEY
) OBJECT IDENTIFIER IS PRIMARY KEY;
/
CREATE TYPE frod_obj AS OBJECT (
    prodid           NUMBER(6),
    ft_ref           ft_obj,
    proddesc         VARCHAR(50),
    costperitem      DECIMAL,
    labcostperitem   DECIMAL
);
/
CREATE TABLE frod_table OF frod_obj (
    prodid PRIMARY KEY
) OBJECT IDENTIFIER IS PRIMARY KEY;
CREATE TYPE wf_obj AS OBJECT (
    wfid           NUMBER,
    ft_ref         ft_obj,
    wfname         VARCHAR(30),
    taxcode        INT,
    yearlyincome   DECIMAL,
    yearlytax      DECIMAL
);
/
CREATE TABLE wf_table OF wf_obj (
    wfid PRIMARY KEY
) OBJECT IDENTIFIER IS PRIMARY KEY;
/
CREATE TYPE wfusage_obj AS OBJECT (
    jobdate         DATE,
    jobhours        INT,
    jobhourlyrate   DECIMAL,
    jobposted       CHAR,
    wfid_ref        REF wf_obj
);
/
CREATE TYPE wfusage_nesttabtyp AS
    TABLE OF wfusage_obj;
/
CREATE TABLE wfusage_objtab OF wfusage_obj;
/
CREATE TYPE odetails_obj AS OBJECT (
    mfid           NUMBER,
    prodid_ref     REF frod_obj,
    quantity       INT,
    itemprice      DECIMAL,
    wfusage_ntab   wfusage_nesttabtyp
);
/
CREATE TYPE odetails_nesttabtyp AS
    TABLE OF odetails_obj;
/
CREATE TYPE prod_obj AS OBJECT (
    prodoid          NUMBER,
    odate            DATE,
    promisedate      DATE,
    completiondate   DATE,
    shipmentdate     DATE,
    status           VARCHAR(20),
    odetails_ntab    odetails_nesttabtyp
);
/
CREATE TABLE prod_objtab OF prod_obj (
    PRIMARY KEY ( prodoid )
) OBJECT IDENTIFIER IS PRIMARY KEY
NESTED TABLE odetails_ntab STORE AS oprod_ntab ( (
    PRIMARY KEY ( nested_table_id,
                  mfid )
)
ORGANIZATION INDEX
COMPRESS 
NESTED TABLE wfusage_ntab STORE AS wforder_ntab
)
RETURN AS LOCATOR
/
ALTER TABLE oprod_ntab ADD (
    SCOPE FOR ( prodid_ref ) IS frod_table
);
/

在嵌套表中插入数据时，插入错误ORA-01401：插入值太大而无法用于列。以下是插入查询

INSERT INTO prod_objtab VALUES (
 46000,
 '25-April-2019',
 '12-May-2019',
 '13-May-2019',
  '13-May-2019',
  'COMPLETED',
  odetails_nesttabtyp(
      odetails_obj(46001
                    ,(SELECT REF(pt) 
                          FROM frod_table pt
                          WHERE pt.prodid = 10002)
                    ,100
                    ,400
                   ,wfusage_nesttabtyp(
                     wfusage_obj('25-April-2019'
                              ,60
                              ,100
                              ,'AME',
                             (SELECT REF(wf) 
                              FROM wf_table wf
                              WHERE wf.wfid = 252)
                    )
                )
              ) 
            )
          )

在第9行中获取错误

ORA-01401：插入的值太大，无法用于列

Answer 1

花了一个多小时来调试我，我认为这是一个可怕的数据模型。像这样的嵌套对象例证了OO作为管理数据的方法的固有问题。

无论如何，问题是这个问题(不可避免地是我看的最后一件事(：

  wfusage_obj('25-April-2019'
                          ,60
                          ,100
                          ,'AME', <------------ culprit
                         (SELECT REF(wf) 
                          FROM wf_table wf
                          WHERE wf.wfid = 252)
                )

'AME'是三个字符长。这是填充wfusage_obj.jobposted，您将其定义为char。合法语法，但如果我们不指定默认为char(1(的数据长度。显然，这是'AME'的两个字符。它需要(至少(char(3(。

修复了此问题后，您将绊倒

ORA-22979：无法插入对象视图参考或用户定义的参考

这是因为您已经用对象标识符定义了表是主要键。因此，您不能在这些表上使用REF。因此，您需要从表定义中删除它，以使您的代码工作。

我已经在DB＆lt;> Fiddle上发布了一个演示