Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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)


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,


  #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

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> -- create table type
SQL> create or replace type tab_number as table of number ;
  2 /

Type created.

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 :
  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||' :
  9 end loop;
 10 exception
 11 when others then
 12 dbms_output.put_line('Some error occured : '||sqlerrm);
 13 end;
 14 /

Procedure created.

SQL> show errors
No errors.
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.


I hope that helps you.



Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 01:23 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.