On Jan 24, 10:28 am, "silvia.fama" <silvia.f..._at_googlemail.com> wrote:
> On 19 Gen, 20:47, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
>
>
> > On Jan 19, 1:28 pm, "silvia.fama" <silvia.f..._at_googlemail.com> wrote:
>
> > > Hi!
> > > I'd liek to understand why oracle changes its behaviour when using bind
> > > variables.
> > > I try to explain.
> > > If I have a char column (e.g. COLUMN1 is char(50)), I can execute
> > > succesfully a select as:
> > > select * from TABLE1 where COLUMN1 = 'silvia'
> > > also if the length of "silvia" is less then 50.
> > > With this kind of select oracle knows to right trim blank values.
> > > Instead if I use bind variables:
> > > select * from TABLE1 where COLUMN1 = :NAME
> > > where :NAME is "silvia"
> > > Oracle doesn't recognize "silvia" but I need to RTRIM the column:
> > > select * from TABLE1 where RTRIM(COLUMN1) = :NAME
>
> > > Why there is a different behaviour?
>
> > > I use:
> > > errora = OCIBindByPos ( dbproc_ora9->oci_stmtp ,
> > > &bndpp ,
> > > dbproc_ora9->oci_err ,
> > > (ub4)(j+1) ,
> > > (dvoid *) name ,
> > > (sb4)datalen ,
> > > (ub2)datatype ,
> > > (dvoid *) 0 ,
> > > (ub2 *) 0 ,
> > > (ub2 *) 0 ,
> > > (ub4) 0 ,
> > > (ub4 *) 0 ,
> > > (ub4) OCI_DEFAULT
> > > ) ;
>
> > > I tried two things:
> > > first:
> > > name is "silvia"
> > > datalen is 6
> > > and datatype is SQLT_CHAR
>
> > > second;
> > > name is "silvia\0"
> > > datalen is 7
> > > datatype is SQLT_STR
>
> > > no difference; query finds "silvia" only in the second case and if I
> > > use RTRIM.
>
> > > May someone explain it to me?
>
> > > Thank you!See your SQL manual for datatype comparison rules
> > Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02, CH 2,
> > Basic Elements of Oracle SQL
>
> > There are different rules for blank padded and non-padded values.
>
> > Oracle is consistent with the ANSI 99 E021-11 Character Comparison rule
>
> > When coding in OCI make sure you have set your C string null
> > terminators and that you set your Oracle length bytes correctly.
>
> > HTH -- Mark D Powell --- Nascondi testo tra virgolette -- Mostra testo tra virgolette -Hi! I resolved the problem!!!
> So, for all that developer that used RTRIM to make a select with bind
> variable, I suggest to specify in the OCI function (OCIBindByPos or
> OCIBindByName) the datatype SQLT_AFC instead of SQLT_CHR or SQLT_STR.
> RTRIM is no more needed!!
> Not easy to find!!
>
> e.g.
>
> errora = OCIBindByPos ( dbproc_ora9->oci_stmtp ,
> &bndpp
> ,
> dbproc_ora9->oci_err ,
> (ub4)(j+1)
> ,
> (dvoid *) name ,
> (sb4)datalen ,
> (ub2)SQLT_AFC ,
> (dvoid *) 0
> ,
> (ub2 *) 0
> ,
> (ub2 *) 0
> ,
> (ub4) 0
> ,
> (ub4 *) 0
> ,
> (ub4) OCI_DEFAULT
> ) ;
>
> Silvia.
Out of interest, why use CHAR for anything? It just causes problems.
Received on Wed Jan 24 2007 - 09:28:45 CST
