Re: sql queries and bind variables

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. Received on Wed Jan 24 2007 - 04:28:19 CST