Correctly use collection method

Question

CREATE  OR  REPLACE  TYPE  nvarchar2_list_type AS TABLE OF NVARCHAR2(100);


CREATE   TABLE test_table(
   id number primary key,
   cars_list     nvarchar2_list_type 
)
NESTED TABLE cars_list  STORE AS cars_list_storage_table;


insert into test_table(id, cars_list)
    values(1,  nvarchar2_list_type( 'AUDI', 'MERCEDES')  );

All above operations completed success, 1 rows inserted in table test_table, now i write this function:

create or replace function get_cnt 
return number
as
  ret_val number;
begin
  SELECT  cars_list.COUNT    
    INTO  ret_val
   from test_table where id  = 1;

   return ret_val;
end;

This gives error: ORA-00904: "CARS_LIST"."COUNT": invalid identifier

Tell please what is wrong here?

As I know, COUNT method must be used just so (from here)

Answer 1

No, you cannot use count method in this situation. You have SQL nested table at hand, count method is used only with PL/SQL collections.

To count number of nested table's elements you can either unnest that nested table or use scalar sub-query:

Unnesting:

SQL> select id
  2       , count(*) as cnt
  3     from test_table t
  4     cross join table(t.cars_list)
  5    group by id
  6  ;

        ID        CNT
---------- ----------
         1          2

Scalar sub-query:

SQL> select id
  2       , (select count(column_value)
  3            from table(t.cars_list)) as cnt
  4     from test_table t
  5  ;
        ID        CNT
---------- ----------
         1          2

Answer 2

Use

  Select
  Cardinality(cars_list) from test_table