Re: VARRAY, loops, incrementing index, etc.

From: Sybrand Bakker <>
Date: Fri, 06 Jan 2006 23:20:56 +0100
Message-ID: <>

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

>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:
>-- below is varray of varchar2(2000) defined in package spec
>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
>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
>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
>*please don't respond to email address in header*
>*it's a bit bucket*
>Teresa Robinson
>Anteon Corporation
>trobinson at anteon dot com

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
Received on Fri Jan 06 2006 - 23:20:56 CET

Original text of this message