ORA-00947全局声明类型时值不足 [英] ORA-00947 Not enough values while declaring type globally

问题描述

 create table foo(
   id number,
   status varchar2(10)
 );

表已创建.

insert into foo values( 1, 'open' );
insert into foo values( 2, 'close' );
insert into foo values( 3, 'open' );
insert into foo values( 4, 'open' );
insert into foo values( 5, 'close' );

create type foo_obj is object (
      id number,
      status varchar2(10)
    );
   /

 create type foo_nt
     as table of foo_obj;
  /

 create or replace package test_bulk
 is 

 procedure temp;

 end;
 /

 create or replace package body test_bulk
 is 

 procedure  temp
   is 
   v_nt  foo_nt;
   begin
     select id ,status 
     bulk collect into v_nt
     from foo;

   end temp;

   end test_bulk;

这是一种非常奇怪的情况，当我全局创建一个类型对象和该类型的嵌套表并创建一个嵌套表类型的变量并将其批量收集到该变量中时

This is a very odd situation, when I create a type object and nested table of that type Globally and create a variable of the nested table type and bulk collect into that variable I get

ORA-00947:值不足错误

ORA-00947: not enough values error

但是，当我声明一个记录类型和该记录类型的嵌套表，然后在该包内部嵌套表的一个变量时，上述批量收集工作就不会引发错误

However, when I declare a record type and nested table of that record type And then a variable of the nested table Inside the package , then the above bulk collect works and it will not throw error

有人可以帮我吗?

推荐答案

您不能只将值放入对象表中-您需要将值转换为适当类型的对象，然后插入对象.试试

You can't just put values into a table of objects - you need to convert the values into objects of the appropriate type and then insert the objects. Try

procedure temp is
  v_nt  foo_nt;
begin
  select FOO_OBJ(id ,status)
    bulk collect into v_nt
    from foo;
end temp;

未经动物测试-您将是第一位！

Not tested on animals - you'll be first!

分享并享受.

这篇关于ORA-00947全局声明类型时值不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！