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

Question

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?

Answer 1

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