0

In Oracle, total 36 record i am getting by using PL/SQL variable type v_arr is varray(25) of varchar2(20). Inside the PL/SQL block i am using 2 different FOR loop for 2 different operation. in the 1st time i am using 

For i in 1 .. v_arr.count(20) loop

and for the next time i used for i in v_arr.count(21) .. v_arr.count loop.

after compile i got error in both the condition. How to resolve this issue. Please help me.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
user8487380
  • 156
  • 3
  • 18
  • `v_arr.count(20)` isn't valid, as the error you probably got will have said. What did you want the `(20)` to do? The count is the count. *"and for the next time..."* - what is "the next time" and what did you want to happen? Varrays aren't really for PL/SQL programming - the normal `table of` construction gives more useful functionality. – William Robertson Dec 18 '18 at 13:57

2 Answers2

2

I suppose you want this 

SQL> set serveroutput on;

SQL> declare
  type a_arr is varray(20) of varchar2(20);
  arr  a_arr:=a_arr('un1','un2','un3','un4','un5','un6','un7','un8','un9','un10',
                    'un11','un12','un13','un14','un15','un16','un17','un18','un19','un20');
begin  
 for i in 1..arr.count
  loop
    dbms_output.put_line(arr(i));
  end loop; 
end;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • I have done but it is taking too much time and also it is only for first loop, i also want for 2nd for loop. – user8487380 Dec 18 '18 at 11:26
  • 1
    How long did it take? It ran in 0.001 seconds on my laptop. – William Robertson Dec 18 '18 at 13:59
  • @Barbaros when i run the above then till 1 to 20 there is some less gap to analyzed the table. But from the 21st schema there are some more gaps between 20th & 21st schema analyzing. SO how to resolve this issue. Please help me. – user8487380 Dec 20 '18 at 14:32
2

Building on Barbaros' example, perhaps you wanted something like this:

declare
    type a_arr is table of varchar2(20);
    arr  a_arr := a_arr
         ( 'un1','un2','un3','un4','un5','un6','un7','un8','un9','un10'
         , 'un11','un12','un13','un14','un15','un16','un17','un18','un19','un20'
         , 'x1','x2','x3' );
begin  
    for i in 1..least(arr.count,20) loop
        dbms_output.put_line(arr(i));
    end loop;

    if arr.count > 20 then
        for i in 21..arr.count loop
            dbms_output.put_line(arr(i));
        end loop;
    end if;        
end;
William Robertson
  • 15,273
  • 4
  • 38
  • 44