Re: Why is VARCHAR2(4000) bad ?

On Jan 30, 9:53 pm, DA Morgan <damor..._at_psoug.org> wrote:

> Not according to Julian. <g>
> --

I think you should read the presentation again. Julian is talking about the "bind variable length".

From the presentation.
"Multiple child cursors can be caused by bind variable lengths"

Using a varchar2 bind variable in sqlplus: sql->var b1 varchar2(20)
sql->alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.01
sql->exec :b1 := 'DBA%'
.....

Excerpt from the trace shows the bind variable rounded to the next highest (32) and also the real length (20) , just as Julian was stating in the presentation.
"mxl=32(20)"

BINDS #1:
kkscoacd
 Bind#0

  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=01 siz=32 off=0
  kxsbbbfp=1067c31a8  bln=32  avl=04  flg=05

Again, it has nothing to do with the length of the column in the table, but the choice of the bind variable length.

Andrew Received on Thu Jan 31 2008 - 10:39:24 CST