Hi,

I'm searching how to obtain the length of a table type...

in a stored procedure, I receive a argument and this argument is an array(type table of numbers or varchars...)
 As I must loop this array(to make inserts for example) I would like to know the number of records in this array.

Is it possible?

can you please give me an example?

thanks in advance,

Michael.

Hi Michael,

The count method for collections may be used for this. Please study the example given below:

--------------------------------------------------------------------------
SQL> set serveroutput on size 1000000
SQL>
SQL> -- create table type
SQL> create or replace type tab_number as table of number ;
  2 /

Type created.

SQL>
SQL> -- create the procedure
SQL> create or replace procedure my_proc ( p_tab_number in
tab_number ) as
  2 begin
  3 -- find out the count of elements in this table type
  4 dbms_output.put_line('Total number of elements :
'||p_tab_number.count);
  5 dbms_output.put_line('----------------------------------');
  6 -- loop thru it now
  7 for i in 1..p_tab_number.count loop
  8 dbms_output.put_line('Element '||i||' :
'||p_tab_number(i));
  9 end loop;
 10 exception
 11 when others then
 12 dbms_output.put_line('Some error occured : '||sqlerrm);
 13 end;
 14 /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> -- now call the procedure from an anonymous block
SQL> declare
  2 v_tab_number tab_number := tab_number(null);
  3 begin
  4 for i in 1..5 loop
  5 v_tab_number.extend();
  6 v_tab_number(i) := i*100 ;
  7 end loop;
  8 v_tab_number.trim();
  9 my_proc(v_tab_number);
 10 exception
 11 when others then
 12 dbms_output.put_line('Some error occured : '||sqlerrm);
 13 end;
 14 /
Total number of elements : 5
----------------------------------
Element 1 : 100
Element 2 : 200
Element 3 : 300
Element 4 : 400
Element 5 : 500

PL/SQL procedure successfully completed.

SQL>
SQL>
--------------------------------------------------------------------------

I hope that helps you.

Cheers,
Prat