oracle cursor, concat with loop run figure

I want to use a cursor to loop through a management reporting chain using the Connect By Prior from Oracle. The order is then reversed so the VP is at the top and the record i started with is at the bottom (A.Corpid).

there's a cursor C1 on top that retrieves corpid following certain criteria

Each Canonical Name returned from that table (say f.i it has 6 rows) i want to store in defined TIER1,TIER2, TIER3 variables.

Somehow i can't figure out how to combine the word 'TIER' with the row number i

DECLARE 
cursor c1 is
select distinct corpid, cn from Mytable where Country ='ITA';
master varchar2(50);
Tier1 varchar2(50);
Tier2 varchar2(50);
Tier3 varchar2(50);
Tier4 varchar2(50);
Tier5 varchar2(50);
Tier6 varchar2(50);
Tier7 varchar开发者_如何学Go2(50);
Tier8 varchar2(50);
Begin
for rec in c1
loop
dbms_output.put_line(rec.cn);

   DECLARE 
   Cursor C2 is

    SELECT CN  FROM Mytable A CONNECT BY PRIOR A.reportsto=A.corpid 
    START WITH A.corpid=rec.corpid
    order by rownum desc;

        Begin
        open C2;
        for i in 1..8 loop
            fetch C2 into master;
            dbms_output.put_line(master);
              'Tier'||to_char(i)) :=master ;  
            end loop;
            end;

     if TIER1 is null then
    TIER1:='';
    end if;
*/ remmed out until the variable assignments work */
-- update mytable set VP_TIER1=TIER1 where corpid=rec.corpid; 
end loop;
end;

Oracle complains about the '||'

(ORA-06550: line 33, column 31: PLS-00103: Encountered the symbol "|" when expecting one of the following: := . ( @ % ; )

I've tried as well to concat but that didn't work either

ORA-06550: line 33, column 26: PLS-00306: wrong number or types of arguments in call to 'CONCAT'

Your concatenation of the literal and variable aren't going to evaluate to the variable TIER1 like you are hoping. Try using arrays instead:

DECLARE  
    cursor c1
    is 
        select distinct corpid, cn
        from Mytable where Country ='ITA'; 
        master varchar2(50);
        TYPE Tier_arr_t IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
        Tier_arr TIER_ARR_T;  
Begin 
    for rec in c1 
    loop 
        dbms_output.put_line(rec.cn);

        DECLARE
            Cursor C2
            is
                SELECT CN 
                FROM Mytable A
                CONNECT BY PRIOR A.reportsto=A.corpid      
                START WITH A.corpid=rec.corpid     
                order by rownum desc;
        Begin
             open C2;

             for i in 1..8
             loop              
                  fetch C2 into master;
                  dbms_output.put_line(master);
                  Tier_arr(i) :=master ;
             end loop;
        end;

        if TIER1 is null
        then
              TIER1:='';
        end if;
        update mytable set VP_TIER1=Tier_arr(1), VP_TIER2=Tier_arr(2) where corpid=rec.corpid;
    end loop;  
end; 

There is also probably a more set-based approach to doing this which would be much preferred, but this should work if it is just a one-time need.