Re: OCI: using oracle's array-interface

Hi Scott,

I was afraid this would be the answer...

I can not use your solution (using a pl/sql table) because i don't know the pl/sql in advance (i read it from another file), nor am i the author of the pl/sql. The program i wrote applies a user-supplied pl/sql-block to every record of a file (a sql*loader replacement). I want to bind some variables as 'scalar' so that they can be used for reporting or counting/summing at the file-level.

Scott Urman wrote:
>
> In article <3268A483.1018_at_wirehub.net>, Gert Rijs <gem_at_wirehub.net> writes:
> |> Hi there,
> |>
> |> I have an oci-program that executes a pl/sql block for thousands of
> |> records in a file. A simplified example of the pl/sql:
> |>
> |> begin
> |> insert into tab values (:sum);
> |> :count := :count + :sum;
> |> end;
> |>
> |> The :sum variable is bound to an array of size 1000 in my program (i use
> |> the oracle array-interface). My problem is the :count variable: i want
> |> it bound to a 'single' host-variable (-> not an array) so when i execute
> |> the pl/sql block (oexn(cda, 1000, 0)) oracle processes it a thousand
> |> times using a different value for :sum each time but the 'same' value
> |> for :count.
> |> Can this be done???
>
> Not the way you have this set up. I don't believe that you can bind an arary
> like this (with the insert statement). Since the statement is in a PL/SQL block,
> you need to use obndra(). This would bind the array to a PL/SQL table. Thus
> your block would have to loop through the table, with something like
>
> BEGIN
> FOR v_Count IN 1..:loop_count LOOP
> INSERT INTO tab VALUES (:sum(v_Count));
> :count := :count + :sum(v_Count);
> END LOOP;
> END;
>
> Then you bind :sum with obndra(), and :loop_count and :count with obndrv(). You
> don't use oexn(), you use oexec().
>
> This approach will work, but it won't be as fast as simply doing the insert
> directly (not in PL/SQL) using oexn() and then summing the array yourself in C.
> Received on Mon Oct 21 1996 - 00:00:00 CEST