CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
user_odoo
user_odoo

Reputation: 2358

Define array and add values using a loop

How define array and add values to a VARRAY using a loop?

Declare
TYPE code_array IS VARRAY(this is changeable) OF VARCHAR2(10);
begin    ​

  ​FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP  
           ​// How put i in code_array ?   i is 1, 2 ...
      ​END LOOP;
end;

Need this resault eg.: ('1','2','3','4','5')

Upvotes: 0

Views: 1159

Answers (2)

Peter
Peter

Reputation: 616

I am sharing my plsql function, it's quick and dirty done, it does what you asked, surely can be optimized.

FUNCTION AppendArrays(column_values1 IN varchar2_table,column_values2 IN varchar2_table) RETURN varchar2_table IS
        column_values_array Varchar2_Table; -- Try using DBMS_SQL.Varchar2_Table; 
    BEGIN
          column_values_array := column_values1;
          column_values_array.extend(column_values2.count);
          FOR i IN 1..(column_values2.count) LOOP    
            column_values_array(i+column_values2.count) := column_values2(i);
          END LOOP;
          return column_values_array;
END AppendArrays;

Also I have my own type but try using DBMS_SQL.Varchar2_Table instead.

create or replace TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(2000);

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

If you declared a local variable of type code_array, you could do something like this

Declare
  TYPE code_array IS VARRAY(5) OF VARCHAR2(10);
  l_codes code_array;
begin    ​

  ​FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP  
    l_codes(i) := to_char(i);
  ​END LOOP;
end;

If you are iterating from 1 to apex_application.g_f01.count, however, that strongly implies that the number of elements you want to handle is not known at compile time. If that is the case, a varray is almost certainly the wrong type of collection to use (frankly, IMHO, varray is basically always the wrong type of collection to use). You'd be much better off with a nested table or an associative array

Declare
  TYPE code_nt is table of varchar2(10);
  l_codes code_nt := code_nt();
begin    ​

  ​FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP  
    l_codes.extend;
    l_codes(i) := to_char(i);
  ​END LOOP;
end;

Upvotes: 2

Related Questions