Re: PlL/SQL and Array Question

Hi,
If you look back in the thread, you'll see that the problem resides on an Oracle7 and NOT Oracle8.
Correct me if I'm wrong, but the first and last attributes of arrays is NOT available in Oracle7 .... Right?

• Peter H. Larsen Oracle Consultant

Lloyd Sheen wrote:

> 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 - 12:00:10 CDT