Access pl/sql attributes dynamically

[MikeAJ]

Hello,

I'm working with a really old package that uses local or plain old pl/sql types defined in a package. Is it possible to access all of the types dynamically? Specifically, what I'm after, is the ability to say:

TYPE t_info IS RECORD (
   tax_type VARCHAR2(15),
   tax_rate_type VARCHAR2(10),
   tax_shipping_flag VARCHAR2(1),
   tax_rate NUMBER);

myType t_info;

FOR each attribute in myType LOOP
  do something with attribute
END LOOP

Anyone know if this is possible?

Thanks!

 

[SantaMufasa]

Mike,

Although there is no provision to "loop" through a list of named record attributes, this is certainly doable if you structure the record, instead, as a PL/SQL table (array), through which you loop using a "FOR i in 1..<expression> LOOP".

Then, after processing via the index loop, and as part of the LOOP-range procedure you could say something like:

FOR i in 1..<expression> LOOP".
    ...generic processing...
    if     i = 1 then my_type.tax_type := tab(1);
    elseif i = 2 then my_type.tax_rate_type := tab(2);
    elseif i = 3 then my_type.tax_shipping_flag := tab(3);
    elseif i = 3 then my_type.tax_rate := tab(4);
    end if;
end loop;
...additional processing...

Let us know if this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MikeAJ]

Thanks for the reply. Unfortunately, it's a big hairy type with many named attributes, and some of the attributes are even types themselves. It would probably take over a year to change this type, as dozens of packages and hundreds of procedures depend on it. So I don't think that's in scope.

Is there any other way to serialize this thing at all?

Thanks for the help.