DECLARE 


 -- create record type

 TYPE rec IS RECORD (a NUMBER, b NUMBER);


  -- create associative array of record type

 TYPE test_rec is TABLE OF rec index by binary_integer ;        


 -- declare collection variable , doesn't need to  get initialized 

 v_test_rec test_rec;        


 -- declare cursor to get set of records 

 CURSOR c1 IS SELECT LEVEL a , LEVEL*10 b FROM DUAL CONNECT BY LEVEL < 5; 


 i number;

 counter number  ;


 BEGIN  


i := 1; -- initialize counter

counter := 1; -- initialize counter

-- Open cursor explicitly, fetch all data into collection variable in one go and explicitly close the cursor .

 FOR c2 IN c1 LOOP

     v_test_rec(i).a :=C2.a  ;

     v_test_rec(i).b :=C2.b  ;

     i := i+1;

 END LOOP;


 

 -- loop through nested table data for display purpose


  FOR counter IN v_test_rec.first..v_test_rec.last loop 

            dbms_output.put_line('Field a ' || v_test_rec(counter).a ||  ' Field b ' ||v_test_rec(counter).b );

END LOOP;


END;


 

Comments

Post a Comment

Popular posts from this blog

 declare   n  number; begin     select 1 into n from dual; if sql%found then -- if rows returned dbms_output.put_line('implicit1'||sql%rowcount);  end if; if sql%notfound then --if rows not returned dbms_output.put_line('implicit2'||sql%rowcount);  else  dbms_output.put_line('dump sql%notfound ' );  end if; if sql%isopen then --if cursor is in open state dbms_output.put_line('implicit3'||sql%rowcount);  else  dbms_output.put_line('dump sql%isopen' );  end if; end;
Read more
 declare  temp SYS_REFCURSOR; n  number; begin   -- open sys_refcursor for select statement open  temp for select level from dual connect by level < 4 ; loop -- cursor is in open state , cursor attributes are applicable   fetch temp  into n; -- fetch current record from temp assign to n      dbms_output.put_line('n'||n);    exit when temp%notfound;-- loop through the curesor until all the dataset exhausts end loop;   if  temp%found then      dbms_output.put_line('tempfound'||temp%rowcount);   end if;      if  temp%notfound then    dbms_output.put_line('tempnotfound'||temp%rowcount);   end if;     if  temp%isopen then    dbms_output.put_line('tempisopen'||temp%rowcount);   end if;      close temp;    end;
Read more
 declare   n  number; begin  -- Implicit cursor is opened , counter i copied the datatype as %rowtype fetch can't be used. for i in  (select level from dual connect by level < 4 ) loop  n :=i.level;   --fetch i into n;-- ORA-06550: line 10, column 9: PLS-00456: item 'I' is not a cursor     dbms_output.put_line('n'||n);    end loop; end;
Read more