Oracle PL/SQL: How to write a loop in order to print all the components of a variable of Oracle customized type?

Question

Please suppose you have an Oracle TYPE so defined:

   TYPE rec_customized
   IS
      RECORD (
         SECRET_CODE       VARCHAR2 (16),
         PUBLIC_CODE       VARCHAR2 (6),
         COMMON            VARCHAR2 (40),
         EMAIL             VARCHAR2 (241),
         CITY              VARCHAR2 (40),
         NATION            VARCHAR2 (2),
         SURNAME           VARCHAR2 (40),
         NAME              VARCHAR2 (40),
         POLICY_NUMBER     VARCHAR2 (10),
         OLD_CODE          VARCHAR2 (11),
         NEARBY            VARCHAR2 (40),
         PROVINCE          VARCHAR2 (2),
         MARITAL_STATUS    VARCHAR2 (80),
         TELEPHONE         VARCHAR2 (30),
         LITTLE_MOUSE      VARCHAR2 (30),
         STREET            VARCHAR2 (60),
         GRAPE_CODE        VARCHAR2 (1)
      );

Please suppose you have a variable called var01 of type rec_customized.

After having instantiated all the components, if I would like to "DBMS_OUTPUT.PUT_LINE" them, I would be obliged to write:

dbms_output.put_line (var01.secret_code);
dbms_output.put_line (var01.public_code);
dbms_output.put_line (var01.common);
dbms_output.put_line (var01.email);
.........
dbms_output.put_line (var01.grape_code);

Is there any way to write a LOOP in order to print, sequentially, all the components of the variable var01 of type rec_customized?

For example:

FOR MY_INDEX IN 1..WhateverCouldBeHelpful
LOOP
  dbms_output.put_line (var01. .....??);
END LOOP;

I am using Oracle 11G Release 2.

Thank you in advance for your kind help.

Answer 1

Record is a composite data type, it is not allowed to loop through fields of a record. If you have to reference that record more than one time in your PL/SQL block - to use dbms_output.put_line() to output value of individual fields of that record, you may define a nested sub-program, list all fields of a record once and use that sub-program as many times as needed in scope of outer PL/SQL block.

Example:

declare
  type t_rec is record(
     field1 varchar2(11)
   );
  l_rec t_rec;
  procedure print_record(p_val in t_rec) is
  begin
    -- Here you list all p_val record's fields
    -- once and use this procedure as many times
    -- in scope of PL/SQL block this
    -- nested sub-program belongs to as needed
    dbms_output.put_line(p_val.field1);
  end;
begin
  -- [1] --
  select dummy
    into l_rec
    from dual;

  print_record(l_rec);
  -- [2] --
  l_rec.field1 := 'Other value';

  print_record(l_rec);
end;
/

Result:

X
Other value
PL/SQL procedure successfully completed