Re: VARRAY, loops, incrementing index, etc.

On Fri, 06 Jan 2006 16:37:38 -0500, Teresa Robinson <usenet_at_teresarobinson.net> wrote:

>Hello,
>
>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

1
The code you post is incomplete.
i doesn't get a value anywhere, v_cell_value doesn't get a value anywhere
2 v_arr_index is not initialized so it might be NULL (numbers can be NULL too), and NULL + 1 results in NULL 3 I would convert the Excel sheet into a csv file, and use Sql*loader to load the data, to avoid re-inventing the wheel. Also Sql*loader will be much faster.

--
Sybrand Bakker, Senior Oracle DBA