VARRAY, loops, incrementing index, etc.

From: Teresa Robinson <usenet_at_teresarobinson.net>
Date: Fri, 06 Jan 2006 16:37:38 -0500
Message-ID: <pentr1hbrbfb07h0mn1p0p7bchm1e8k1pb_at_4ax.com>

Hello,

[Quoted] I'm coding to load Excel data into Oracle by going through and getting one Excel row (one column and cell at a time) and then inserting that into a temp table. I could be going about this the wrong way, but the issue is this: my array index is not incrementing in one place, but it is in another. Code:

<code>
-- below is varray of varchar2(2000) defined in package spec

v_values		FIELD.EXCEL_VALUE := FIELD.EXCEL_VALUE();
v_cell_value	varchar2(255);
v_arr_index	number;
v_fill_index	number;
v_fill_array	number;
v_used_cols	number;
v_used_rows	number;
v_row_nums	number;
v_col_nums 	number;

...
FOR v_row_nums IN 1 .. v_used_rows LOOP

    FOR v_col_nums IN 1 .. v_used_cols LOOP

        ...
        If i = 0 Then
	--it's text
        ...    --get v_cell_value
            if v_cell_value is not null AND v_cell_value <> '' then
  	     v_values.extend(1);
  	     v_values(v_arr_index) := v_cell_value;
  	     v_arr_index := v_arr_index + 1;
	 end if;
        Else
	--it's a number
	 v_values.extend(1);
 	 v_values(v_arr_index) := v_cell_value;
 	 v_arr_index := v_arr_index + 1;
        End if;
        k := k + 1;

    ...
    END LOOP; --v_col_num in 1 .. v_used_cols, inc k

• I know the for is clumsy, please forgive v_fill_index := 1; v_fill_array := 16; FOR v_fill_array IN v_arr_index .. 16 LOOP v_values.extend(1); v_values(v_fill_index) := null; v_fill_index := v_fill_index + 1; end loop; END LOOP; --v_row_num in 1 .. v_used_rows, inc j </code>

v_values.extend(1) does not work in the top inner loop, the array slot gets no value, and v_arr_index does not increment. k does increment.

v_values.extend(1) DOES work, v_fill_index DOES increment in the second inner loop. Does this all have something to do with the IF statement? And if so, are there any suggestions as to how I should do this?

The second inner loop is just to fill out the rest of the array slots, prior to inserting into the table. I'm new to this language, and it's kind of confusing to make for loops make sense, to me...

Please let me know if you need any more information, and thanks in advance,
--

*please don't respond to email address in header* *it's a bit bucket*

Teresa Robinson
Anteon Corporation
trobinson at anteon dot com
--

Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com Received on Fri Jan 06 2006 - 22:37:38 CET