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

William Robertson
  • 15,273
  • 4
  • 38
  • 44

0 Answers0