--create table locations_localtab as select * from HR.locations;
SET SERVEROUTPUT ON;
declare
type tLOC_type is table of locations_localtab%rowtype index by binary_integer;
tLOC tLOC_type := tLOC_type();
vPostal_code locations_localtab.postal_code%type;
vCity locations_localtab.city%type;
vLocal varchar2(50);
vSource varchar2(50);
comm long;
begin
select location_id,null,postal_code,city,state_province,country_id
bulk collect into tLOC
from HR.locations;
for ii in (select column_name from (select 'POSTAL_CODE' c1,'CITY' c2, 'COUNTRY_ID' c3 from dual) UNPIVOT (column_name for (name_of_col) in (c1,c2,c3))) loop
for i in 1..tLOC.count loop
comm := 'vSource := tLOC('||i|| ').'||ii.column_name ;
dbms_output.put_line(comm);
execute immediate comm;
-- select city into vLocal from locations_localtab where location_id = tLOC(i).location_id;
dbms_output.put_line('vSOURCE -->'||'.'||vSOURCE);
dbms_output.put_line('vLOCAL -->'||'.'||vLOCAL);
end loop;
end loop;
-- output -->
-- vSource := tLOC(1).POSTAL_CODE;
-- vSource := tLOC(2).POSTAL_CODE;
I would like to make loop by column_name to use this iterator for another loop with table type..
I would like to assign value of tLOC(i).ii.column_name into variable vSource, how can I do this?
I have no idea how I can deal with it.
Thank you in advance for your help.