nested table element assignment in pl/sql

Question

My requirement in pl/sql nested table is the following:

I have a nested table collection type declared and I populate the elements based on a lookup from a table. In cases where the lookup yields more than one row(more than one value for code), then add all those multiple values in the nested table and proceed. Here is where i am stuck. I am not able to increment that parent counter "indx" inside the exception to process those multiple rows. Since i am not, it only stores the latest data in the nested table and not all of them.

declare
TYPE final_coll_typ IS TABLE OF varchar2(100);

l_final_coll final_coll_typ;

MULTI_FETCH EXCEPTION;
PRAGMA EXCEPTION_INIT(MULTI_FETCH, -1422); -- this is an ora error for exact fetch returns more than the required number of rows

begin

for indx in 1..<count> loop

    <some processing logic here>
    select code into l_final_coll(indx) from lookup_tbl where <some filter>;
    exception
    when MULTI_FETCH then
             for p in (select code from lookup_tbl where <some filter>)
             loop
                            l_final_coll(indx) := p.code;
                            dbms_output.put_line(l_final_coll(indx));
                        end loop;
                        continue; -- this is for further processing after the loop

end loop;
end;

Lets say, the first iteration of the counter indx produced only one row data for code. That gets stored in l_final_coll(indx). Lets say the next iteration of indx i the main for loop produces 2 rows of values for code. My thought was to catch the exception (ORA-01422) and keep adding these 2 code values in the existing nested table.

So, in effect, my nested table should now have 3 values of code in its element. But, currently, I can only get it to populate 2 of them (the single value from first itreration and the latest value from the next)

Any pointers would be appreciated on how I can accomplish this.

PS: Tried manipulating the counter variables indx and p. But, obviously pl/sql does not allow it for a "for loop".

Alex Poole · Accepted Answer · 2014-09-02 17:34:27Z

You don't need to do a single select at all, just use the cursor loop to start with, and append to the collection (which you'd have to initialise):

declare
  type final_coll_typ is table of varchar2(100);
  l_final_coll final_coll_typ;
begin
  l_final_coll := final_coll_typ();
  for indx in 1..<count> loop

    <some processing logic here>

    for p in (select code from lookup_tbl where <some filter>) loop
      l_final_coll.extend(1);
      l_final_coll(l_final_coll.count) := p.code;
    end loop;
  end loop;

  dbms_output.put_line('Final size: ' || l_final_coll.count);
end;
/

For each row found but the cursor, the collection is extended by one (which isn't very efficient), and the cursor value is put in the last, empty, row; which is found from the current count.

As a demo, if I create a dummy table with a duplicate value:

create table lookup_tbl(code varchar2(100));
insert into lookup_tbl values ('Code 1');
insert into lookup_tbl values ('Code 2');
insert into lookup_tbl values ('Code 2');
insert into lookup_tbl values ('Code 3');

... then with a specific counter and filter:

declare
  type final_coll_typ is table of varchar2(100);
  l_final_coll final_coll_typ;
begin
  l_final_coll := final_coll_typ();
  for indx in 1..3 loop  
    for p in (select code from lookup_tbl where code = 'Code ' || indx) loop
      l_final_coll.extend(1);
      l_final_coll(l_final_coll.count) := p.code;
    end loop;
  end loop;

  dbms_output.put_line('Final size: ' || l_final_coll.count);
end;
/

... I get:

anonymous block completed
Final size: 4

As a slightly more complicated option, you could bulk-collect all the matching data into a temporary collection, then loop over that to append those values into the real collection. Something like:

declare
  type final_coll_typ is table of varchar2(100);
  l_final_coll final_coll_typ;
  l_tmp_coll sys.dbms_debug_vc2coll;
begin
  l_final_coll := final_coll_typ();
  for indx in 1..<count> loop

    <some processing logic here>

    select code bulk collect into l_tmp_coll from lookup_tbl where <some filter>;

    for cntr in 1..l_tmp_coll.count loop
      l_final_coll.extend(1);
      l_final_coll(l_final_coll.count) := l_tmp_coll(cntr);
    end loop;
  end loop;
end;
/

There may be a quicker way to combine two collections but I'm not aware of one. Bulk-collect has to be into a schema-level collection type, so you can't use your local final_coll_typ. You can create your own schema-level type, and then use it for both the temporary and final collection variables; but I've used a built-in one, sys.dbms_debug_vc2coll, which is defined as table of varchar2(1000).

As a demo, with the same table/data as above, and the same specific count and filter:

declare
  type final_coll_typ is table of varchar2(100);
  l_final_coll final_coll_typ;
  l_tmp_coll sys.dbms_debug_vc2coll;
begin
  l_final_coll := final_coll_typ();
  for indx in 1..3 loop

    select code bulk collect into l_tmp_coll
    from lookup_tbl where code = 'Code ' || indx;

    for cntr in 1..l_tmp_coll.count loop
      l_final_coll.extend(1);
      l_final_coll(l_final_coll.count) := l_tmp_coll(cntr);
    end loop;
  end loop;

  dbms_output.put_line('Final size: ' || l_final_coll.count);
end;
/

... I again get:

anonymous block completed
Final size: 4

thanks for the many options. The first option you gave was neat...dont know why I didnt think of it...duh !

Collectives™ on Stack Overflow

nested table element assignment in pl/sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related