Prev: Tcl_CreateInterp() hangs
Next: Passing shell variable as command line argument to expect
From: yahalom on 22 Nov 2008 01:19
On Nov 15, 6:02 am, "thelf...(a)gmail.com" <thelf...(a)gmail.com> wrote:
> On Nov 14, 9:17 am, yahalom <yahal...(a)gmail.com> wrote:
>
> > one more questions. I do not undersntand the bindsize. in the doc it
> > states:
>
> >  bindsize  Sets or returns the size of the  buffer  used
> >                     for  storing  bind variable values.  This is
> >                     used in orabindexec and  oraplexec  to  allow
> >                     the  buffer to be reused on subsequent calls.
> >                     The default is 2000 bytes and the maximum  is
> >                     4000 bytes.
>
> > it is obvoiusly wrong as 4000 is no longer a limit but does it matter
> > to what I set it? will it really matter if I make it longer then the
> >binddata. I will probabaly close the connection so anyway the buffer
> > will stay and be reused.
>
> In the case of clob data.  The bindsize is over-ridden and takes on
> new meaning.  In fact, the documentation is lacking.
>
> For performance reasons, you really don't want to increase the
> bindsize to a really really large number everywhere.
>
> For clob data, it is safe to set the bindsize to the largest clob
> string you are going to use.  The bindpsize will determine the number
> and size of pieces sent to the database in each round trip.  For clob
> data, the bindsize is used to inform the database how big a buffer it
> is going to need to receive the clob data in pieces and re-assemble it
> on the other end.
>
> -Todd

another thing that is worth mentioning and is not documented is that
the clob column should be the last in the binding.

create table test (a number(3),b clob,c varchar2(30))

oraparse $sth "insert into test1 values (:v1, :v2, :v3)"
orabind $sth :v1 2 :v2 [string repeat x 15000] :v3 test
oraexec $sth

will fail ORA-24816: Expanded non LONG bind data supplied after actual
LONG or LOB column

you need to do:
orabind $sth :v1 2 :v3 test x`:v2 [string repeat x 15000]


From: thelfter on 22 Nov 2008 17:02
On Nov 22, 1:19 am, yahalom <yahal...(a)gmail.com> wrote:
> On Nov 15, 6:02 am, "thelf...(a)gmail.com" <thelf...(a)gmail.com> wrote:
>
>
>
> > On Nov 14, 9:17 am, yahalom <yahal...(a)gmail.com> wrote:
>
> > > one more questions. I do not undersntand the bindsize. in the doc it
> > > states:
>
> > >  bindsize  Sets or returns the size of the  buffer  used
> > >                     for  storing  bind variable values.  This is
> > >                     used in orabindexec and  oraplexec  to  allow
> > >                     the  buffer to be reused on subsequent calls.
> > >                     The default is 2000 bytes and the maximum  is
> > >                     4000 bytes.
>
> > > it is obvoiusly wrong as 4000 is no longer a limit but does it matter
> > > to what I set it? will it really matter if I make it longer then the
> > >binddata. I will probabaly close the connection so anyway the buffer
> > > will stay and be reused.
>
> > In the case of clob data.  The bindsize is over-ridden and takes on
> > new meaning.  In fact, the documentation is lacking.
>
> > For performance reasons, you really don't want to increase the
> > bindsize to a really really large number everywhere.
>
> > For clob data, it is safe to set the bindsize to the largest clob
> > string you are going to use.  The bindpsize will determine the number
> > and size of pieces sent to the database in each round trip.  For clob
> > data, the bindsize is used to inform the database how big a buffer it
> > is going to need to receive the clob data in pieces and re-assemble it
> > on the other end.
>
> > -Todd
>
> another thing that is worth mentioning and is not documented is that
> the clob column should be the last in the binding.
>
> create table test (a number(3),b clob,c varchar2(30))
>
> oraparse $sth "insert into test1 values (:v1, :v2, :v3)"
> orabind $sth :v1 2 :v2 [string repeat x 15000] :v3 test
> oraexec $sth
>
> will fail ORA-24816: Expanded non LONG bind data supplied after actual
> LONG or LOB column
>
> you need to do:
> orabind $sth :v1 2 :v3 test x`:v2 [string repeat x 15000]

That has more to do with your version of Oracle.
When you get to 10g and 11g, that will not be the case.

-Todd
First  |  Prev  | 
Pages: 1 2 3
Prev: Tcl_CreateInterp() hangs
Next: Passing shell variable as command line argument to expect