Keep in mind that the cache works only in the DBMS-to-client
direction (correct ?) So INSERT/UPDATE/DELETE and in particular
the SELECT stmt given below will not likely benefit, ie, any time
you're sending placeholder data to the DBMS, it still requires
tuple-at-a-time processing.
Array binding is in the works, and I have a DBD::ODBC hack
(check the archives) I can forward if you're willing to use
experimental, unsupported stuff.
Dean Arnold
----- Original Message -----
From: "James.FitzGibbon" <[EMAIL PROTECTED]>
To: "'Schoenenberger Peter'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, March 12, 2002 8:21 AM
Subject: RE: Host Arrays
> Look back in the archives. I asked this same question back in
> January (the thread starts on Jan 24th). In short, yes. Fiddle
> with $dbh->{RowCacheSize} to get what you want. If you set it to
> 200, then when you do your first fetch you will get 200 rows from
> Oracle, and the next 199 fetches will be fulfilled from local memory,
> not over the network.
>
> --
> j.
>
> James FitzGibbon voice/fax 612-761-6121/4277
> Consultant, TTS-3D@TPN4H [EMAIL PROTECTED]
>
>
> > -----Original Message-----
> > From: Schoenenberger Peter [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, March 12, 2002 10:02 AM
> > To: '[EMAIL PROTECTED]'
> > Subject: Host Arrays
> >
> >
> > Hello everyone
> >
> > Is there a way to use the "host array" interface of oracle 8i
> > in perl dbi?
> >
> > With arrays, you manipulate an entire array with a single SQL
> > statement.
> > Thus, Oracle communication overhead is reduced markedly,
> > especially in a
> > networked environment. A major portion of runtime is spent on network
> > roundtrips between the client program and the server
> > database. Arrays reduce
> > the roundtrips.
> > For example, suppose you want to insert information about 300
> > employees into
> > the EMP table. Without arrays your program must do 300 individual
> > INSERTs--one for each employee. With arrays, only one INSERT
> > needs to be
> > done. Or you want to select many records from the database.
> >
> > For example: With embedded SQL in C you can do the following:
> > -------------------------------------------------------------
> > char emp_name[1000];
> > int emp_number[1000];
> > float salary[1000];
> >
> > EXEC SQL SELECT ENAME, EMPNO, SAL
> > INTO :emp_name, :emp_number, :salary
> > FROM EMP
> > WHERE SAL > 2000;
> > --------------------------------------------------------------
> > All data (maximum 1000) will come in one stream over the
> > network to your
> > client.
> >
> > I have tried the select with the "fetchall_arrayref" in perl,
> > but behind the
> > scenery multiple fetches are produced and there is no reduce
> > of roundtrips
> > between the client and the database.
> >
> > Thanks,
> > Peter
> >
