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.
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