CODEWITHSUNDEEP
arraysoracle-databaseplsql
Zesty
Zesty

Reputation: 2991

How do I push items into arrays and iterate through them in PL/SQL?

I'm trying to do something very basic in PL/SQL, but I keep getting owned... how do I push items into an array and iterate through them?

Googling it seems to suggest using owa_text.multi_line;

owa_text.multi_line is a record of this type:

   /* A multi_line is just an abstract datatype which can hold */
   /* large amounts of text data as one piece.                 */
   type multi_line is record
   (
      rows        vc_arr,
      num_rows    integer,
      partial_row boolean
   );

To iterate through vc_arr, we have to use l_array.first.. l_array.last. But that gives an error while trying to access it.

Here's a simple sample to find load distinct values into an array:

    declare
    l_persons owa_text.multi_line := owa_text.new_multi();
/* Documentation of owa_text.new_multi(): Standard "make element" routines. */
--function  new_multi return multi_line;

    l_value_exists boolean := false;

    cursor c_get_orders is
    select person,
             choice
    from my_orders;         

    begin
          for i in c_get_orders loop
                l_value_exists := false;
                for j in l_persons.rows.first.. l_persons.rows.last loop --Fails here, 
    --PL/SQL: numeric or value error
                          if l_persons.rows(j) = i.person then
                                l_value_exists := true;
                                exit;
                          end if;
                    end loop;
                    if not l_value_exists then
                          owa_text.add2multi(i.person, l_persons); 
                    end if; 
              end loop;
              for i in l_persons.rows.first.. l_persons.rows.last loop
                    write_to_log(l_persons.rows(i));
              end loop;
        end;

What am I missing? How do I do this?

EDIT: Here's a script to get set up, if it helps follow the example:

create table my_orders
(
  person  varchar2(4000 byte),
  choice  varchar2(4000 byte)
);

insert into my_orders
   (person, choice)
 values
   ('Tom', 'Juice');
insert into my_orders
   (person, choice)
 values
   ('Jane', 'Apple');
insert into my_orders
   (person, choice)
 values
   ('Tom', 'Cake');
insert into my_orders
   (person, choice)
 values
   ('Jane', 'Chocolate');
insert into my_orders
   (person, choice)
 values
   ('Tom', 'Coffee');
commit;

Upvotes: 1

Views: 6347

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30775

Presumable, the new_multi() method initializes an empty collection.

One of the more esoteric features of Oracle collections is that using FIRST / LAST to iterate over empty collections doesn't work - you have to either check whether the collection is empty, or use 1 .. <collection>.COUNT instead:

declare
  type t_number_nt is table of number;
  l_numbers t_number_nt := t_number_nt();
begin
  -- raises ORA-006502
  /*  for i in l_numbers.first .. l_numbers.last
  loop
    dbms_output.put_line(l_numbers(i));
  end loop;
  */
  -- doesn't raise an error
  for i in 1 .. l_numbers.count loop
    dbms_output.put_line(l_numbers(i));
  end loop;
end;

UPDATE

For a more thorough explanation of techniques for iterating over PL/SQL collections, see this OTN article by Steven Feuerstein

Upvotes: 2

neshkeev
neshkeev

Reputation: 6476

It has to be like this:

declare
  l_persons owa_text.multi_line;
begin
  OWA_TEXT.new_multi (l_persons);
  FOR i IN 1 .. l_persons.num_rows
  loop
    null;
  end loop;
end;

Upvotes: 1

Related Questions