时间戳

首页 > 解决方案 > ORA-02315: 默认构造函数的参数数量不正确

oracle - ORA-02315: 默认构造函数的参数数量不正确

问题描述

我有这张表，我正试图开始工作，但每当我尝试插入它时都会出错，我不明白我为什么会得到它。任何帮助将不胜感激！

CREATE TYPE item AS OBJECT
(itemID NUMBER(6),
description CLOB) NOT FINAL;
/
CREATE TYPE producers_t AS OBJECT
(producerID NUMBER(3),
producer VARCHAR(35));
/
CREATE TYPE  producers_type AS TABLE OF REF producers_t;
/
CREATE TYPE dvds_typ UNDER item
(ISBN NUMBER(13),
subject VARCHAR(35),
title VARCHAR(150),
producers producers_type);
/
CREATE TABLE dvds_tab OF dvds_typ (PRIMARY KEY (itemID)
) NESTED TABLE producers STORE AS producers_ntab;
/
INSERT INTO dvds_tab
    VALUES (95, 'A dvd about...', '1324567891234', 'Sci-Fi', 'A long dvd',
        producers_t(producers_type(56, 'Gary')));

标签： oracleplsql

解决方案

您创建producers_typeasTABLE OF REF producers_t但您没有传入REF值，而是传入了基础对象。如果要存储非REF值，则将类型创建为：

CREATE TYPE  producers_type AS TABLE OF producers_t;
/

（正如@AlexPoole在您的插入语句中指出的那样交换producers_t(producers_type(56, 'Gary'))到。）producers_type(producers_t(56, 'Gary'))

例如：

CREATE TYPE item AS OBJECT(
  itemID      NUMBER(6),
  description CLOB
) NOT FINAL;

CREATE TYPE producers_t AS OBJECT(
  producerID NUMBER(3),
  producer   VARCHAR(35)
);

CREATE TYPE  producers_type AS TABLE OF producers_t;

CREATE TYPE dvds_typ UNDER item(
  ISBN      NUMBER(13),
  subject   VARCHAR(35),
  title     VARCHAR(150),
  producers producers_type
);

CREATE TABLE dvds_tab OF dvds_typ (
  PRIMARY KEY ( itemID )
) NESTED TABLE producers STORE AS producers_ntab;

INSERT INTO dvds_tab
VALUES (
  95,
  'A dvd about...',
  '1324567891234',
  'Sci-Fi',
  'A long dvd',
  producers_type(producers_t(56, 'Gary'))
);

db<>在这里摆弄

由于您使用的是对象表，另一种选择是只创建一个表item：

CREATE TABLE items OF item (
  PRIMARY KEY ( itemID )
);

INSERT INTO items
VALUES (
  dvds_typ(
    95,
    'A dvd about...',
    '1324567891234',
    'Sci-Fi',
    'A long dvd',
    producers_type(producers_t(56, 'Gary'))
  )
);

然后，要获取数据，您可以使用：

SELECT i.itemid,
       i.description,
       i.isbn,
       i.subject,
       i.title,
       p.producerid,
       p.producer
FROM   (
  SELECT i.itemId,
         i.description,
         TREAT(VALUE(i) AS dvds_typ).isbn      AS isbn,
         TREAT(VALUE(i) AS dvds_typ).subject   AS subject,
         TREAT(VALUE(i) AS dvds_typ).title     AS title,
         TREAT(VALUE(i) AS dvds_typ).producers AS producers
  FROM   items i
) i
OUTER APPLY TABLE( i.producers ) p

哪个输出：

项目 | 说明 | 国际标准书号 | 主题 | 标题 | 生产者 ID | 制片人
-----: | :------------- | ------------: | :-------- | :--------- | ---------: | :--------
    95 | 一个关于...的DVD | 1324567891234 | 科幻 | 很长的DVD | 56 | 加里

db<>在这里摆弄

推荐阅读