Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 18th, 2003, 12:11 PM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default size of table (type table is table of number)

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.

  #2 (permalink)  
Old November 19th, 2003, 03:11 AM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
table size silvia C# 2 February 10th, 2006 04:12 AM
Table Size Considerations hugh@kmcnetwork.com SQL Server 2000 0 April 24th, 2005 08:30 PM
Fixed size table amc Dreamweaver (all versions) 4 August 29th, 2004 07:03 AM
Size of database and table Jane SQL Server 2000 2 March 15th, 2004 03:57 AM
Get table size Mitch SQL Server 2000 1 September 16th, 2003 11:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.