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

Question

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.

Detonator

Answer 1

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;

Bright

Yes. I've rarely had any use for RECORD structures, apart the ones that are associated with a table or cursor using %ROWTYPE and are handy for FETCHing into. – Bright

hm, well, I use them any time I need a temporary data storage with known data structure - that makes code some sort of self-commenting. maybe that's a bad practice. – Detonator

Answer 2

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;

Stagnate

Lovely! Note to anyone re-using this, the function definition has to be the last thing in the declare block (and the compile error you get otherwise is completely unhelpful). – Galleon

Also, if you run into PLS-00222: no function with name 'b' exists in this scope, you probably added `index by pls_integer' to the table definition. Like I did :) Thanks @Shallow. – Divestiture

Answer 3

Since release 18c Qualified Expressions provides an alternative way to define the values of complex data types. Quote:

Starting with Oracle Database Release 18c, any PL/SQL value can be provided by an expression (for example for a record or for an associative array) like a constructor provides an abstract datatype value. In PL/SQL, we use the terms "qualified expression" and "aggregate" rather than the SQL term "type constructor", but the functionality is the same.

Here's an working example:

declare 
    type a is record (a1 number, a2 number);
    type b is table of a index by varchar2 (16);
    arr b := b ('key1' => a (1, 2), 'key2' => a (3, 4)); 
begin 
    declare key varchar2 (16) := arr.first; begin 
    <<foreach>> loop
        dbms_output.put_line (arr(key).a1||','||arr (key).a2);
        key := arr.next (key);
        exit foreach when key is null;
    end loop; end;
end;
/
PL/SQL procedure successfully completed.

1,2
3,4

Extraditable