|
Subject:
|
size of table (type table is table of number)
|
|
Posted By:
|
MikoMax
|
Post Date:
|
11/18/2003 11:11:00 AM
|
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.
|
|
Reply By:
|
azizmasih
|
Reply Date:
|
11/19/2003 2:11:40 AM
|
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
|
