Re: OCI: using oracle's array-interface

From: Scott Urman <surman_at_dlsun338.us.oracle.com>
Date: 1996/10/21
Message-ID: <54gcg4$361_at_inet-nntp-gw-1.us.oracle.com>#1/1


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.   

|> --
|> Gert Rijs
|> mailto:gem_at_wirehub.net (at home)
|> mailto:gert.rijs_at_corp.ah.nl (at work)



Scott Urman Oracle Corporation surman_at_us.oracle.com

Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm

"The opinions expressed here are my own, and are not necessarily that of  Oracle Corporation"
Received on Mon Oct 21 1996 - 00:00:00 CEST

Original text of this message