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?
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
A.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;
v_areas_hijasis a local collection variable (specifically it's an associative array), not a relational table you can use in SQL (not justcount(*)).