How to manually initialize a collection of RECORDs in PL/SQL?

0 votes
asked Sep 14, 2010 by be-here-now

guys. Here's a simple sample two-dimensional array in PL/SQL, which is working perfectly.

declare
  type a is table of number;
  type b is table of a;

  arr b := b(a(1, 2), a(3, 4));
begin
  for i in arr.first .. arr.last loop
    for j in arr(i).first .. arr(i).last loop
      dbms_output.put_line(arr(i) (j));
    end loop;
  end loop;
end;

What I need to do, is to create something similar for a table of RECORDS. Like this:

 type a is record(a1 number, a2 number);
 type b is table of a;

The question is, can I manually initialize this kind of array, or it is supposed to be filled by bulk collects or similar? The same syntax as above doesn't seem to work, and I wasn't able to find any initialization sample in manuals.

2 Answers

0 votes
answered Sep 14, 2010 by tony-andrews

There is no "constructor" syntax for RECORDs, so you have to populate them like this:

declare
 type a is record(a1 number, a2 number);
 type b is table of a;
 arr b := b();
begin
 arr.extend(2);
 arr(1).a1 := 1;
 arr(1).a2 := 2;
 arr(2).a1 := 3;
 arr(2).a2 := 4;
end;
0 votes
answered Sep 23, 2015 by shallow

This works without objects, but you have to declare a constructor function for type 'a' values.

declare  
  type a is record(a1 number, a2 number);
  type b is table of a;

  arr b;

  --Constructor for type a
  function a_(a1 number, a2 number) return a is
    r_a a;
  begin
    r_a.a1 := a1;
    r_a.a2 := a2;

    return(r_a);
  end;

begin
  arr := b(a_(1, 2), a_(3, 4), a_(5, 6), a_(7, 8));

  for i in arr.first .. arr.last loop
    dbms_output.put_line(arr(i).a1||', '||arr(i).a2);
  end loop;
end;
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...