arraysoracle-databasecollectionsnumbersora-00932

Got in bulk collect PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER

I created types

create or replace type rec_int_g as object( i integer);
create or replace type typ_int_g is table of rec_int_g;

And a function

create function table_randset 
 return pls_integer is
  
 int_array typ_int_g := typ_int_g();


begin
  
select product_id bulk collect into int_array

  from product;

return randset(int_array);
end;

But got in select PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER error. What is the problem?

Solution

  • The problem is what the error says, you are passing numbers to the collection, when it is expecting a user-defined type. That's because your query is returning a number, not an object. So instead of:

    select product_id bulk collect into int_array from product;

    do:

    select rec_int_g(product_id) bulk collect into int_array from product;

    You also don't need to initialise the collection before a bulk-collect into it, you can simplify that to:

    int_array typ_int_g;

    fiddle with anonymous block rather than function.

    That's assuming you need an object type at all; presumably that's what randset() is expecting, but it looks like you could have defined typ_int_g as table of integer or table of number, perhaps.

    I dont know how get aggregates from plain array (how to address to column name? which column name?)

    There is a column_value pseudo-column name for collections. So you can do:

    select count(column_value), min(column_value), max(column_value)

    fiddle