count(*) in dbms_sql.number_table

Question

I have declared a number table like:

v_areas_hijas   dbms_sql.number_table;

I fill the table and then I'm trying to get the size of that table with:

select count(*) into v_counter from v_areas_hijas;

but I'm getting:

Error(23,37): PL/SQL: ORA-00942: table or view does not exist

How should I get that size?

Answer 1

Apparently the count method of the collection works.

declare
    p_ids dbms_sql.number_table;
    cnt NUMBER;
begin
    p_ids(1) := 2;
    p_ids(2) := 3;
    p_ids(3) := 4;
    dbms_output.put_line('cnt '||p_ids.count);
end;
/

returns

 cnt 3

Answer 2

v_areas_hijas is a local collection variable (specifically it's an associative array), not a relational table you can use in SQL (not just count(*)). You can cast some collections to relational tables using the table() operator, although there are limitations with scope and compatibility depending on the type, where it's declared and which version of Oracle you have (11g, 12c R1 and 12c R2 have different capabilities.)

Using a globally declared SQL collection type created with create or replace type (or an existing one like sys.ku$_xmlcolset_t) you can do something like this:

declare 
    v_areas_hijas sys.ku$_xmlcolset_t := sys.ku$_xmlcolset_t(1,10,100);
    v_counter integer;
begin
    select count(*) into v_counter from table(v_areas_hijas);
    dbms_output.put_line('Count = ' || v_counter);
end;