Re: PlL/SQL and Array Question

From: Lloyd Sheen <lsheen_at_sfi-software.com>
Date: 11 Jun 1998 16:37:01 GMT
Message-ID: <01bd9557$09f18b00$181bf8ce@nt_w28>

The following is PL/SQL code which will loop through a PL/SQL table (Array)

Defined in package specification:

	type stat_rec is record
		( stat# number(3),
			value number);
	type stat_rec_table is table of stat_rec
		index by binary_integer;

	delta_stat stat_rec_table;

Procedure reading PL/SQL table:

procedure DumpStats
is

	t_statid v$mystat.STATISTIC#%type;
	t_value v$mystat.value%type;
	p_value v$mystat.value%type;
	t_name v$statname.name%type;
	idx number;
	
begin
	idx := delta_stat.first;
	while idx is not null loop
		t_statid := delta_stat(idx).stat#;
		t_value := delta_stat(idx).value;
		p_value := prior_stat(idx).value;
		select name into t_name from v$statname
			where  v$statname.statistic# = t_statid;
		dbms_output.put_line(rpad(t_name,40,' ') || to_char(p_value,'999,999') ||
to_char(t_value,'999,999'));
		idx := delta_stat.next(idx);
	end loop;

end;

All the above code was tested using the PL/SQL debugger found in SQL-Programmer 2.5 shipping soon from Sylvain Faust Intl.

http://www.sfi-software.com

Lloyd Sheen
Product Manager SQL-Programmer

Peter H. Larsen <plarsen_at_dc.dynares.com> wrote in article <357EEF6A.D9FE400E_at_dc.dynares.com>...
> Using typedef, yep - you can ON THE SERVER (NOT in the Oracle tools) pass
whole arrays to a function. Requirement: Define your procedure in a package, define type type as an internal or external depending on your requirements, and then use that
> type in your parameter declaretion. Restrictions: You cannot call this
function from PL/SQL version 1.1 ei. all the client tools from Oracle.
>
> To LOOP thru an array is difficault because as a matter of fact, the
"array" is considered an inifite table by the PL/SQL engine. You can have items in place nr. 5, 10 and 10000 and all others are empty and will yield NO DATA FOUND if requested. If
> you know your max-limit of the array (most programs operate with that)
then simple loop through the array:
>
> DECLARE
> TYPE IDX IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
> AR IDX;
> I NUMBER;
> MAXITEM NUMBER := 10000;
> FOUND NUMBER := 0;
> BEGIN
> FOR I IN 1.. MAXITEM
> LOOP
> begin
> if AR(I) = 0 THEN
> -- Found an item
> found := found + 1;
> end if;
> exception
> when no_data_found then null; -- No element defined
> end;
> end loop;
>
> A better way is thinking "objective", and encapsulating your array in a
package, and in that package count the number of elements as they are assigned. Unfortunately you cannot make multible instances of this "object" in Oracle7 ... but Oracle8
> can!
>
> Hopes this helps a bit ...
>
> - Peter H. Larsen
> Oracle Consultant
>
> Sean Dolan wrote:
>
> > I am trying to pass an array into a stored function on an
Oracle 7.3.4 server. First, can I pass an array to a function?
> >
> > Second, is there any command/syntax for looping through all the items
in the array? For example,... FOR EACH ITEM IN ARRAY blah,blah, blah... or is there a count function to find the number of items in the array so I can do a LOOP that way?
> >
> > Thanks,
> > Sean Dolan
>
>
>
>
Received on Thu Jun 11 1998 - 11:37:01 CDT