Re: sql queries and bind variables

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 -- Received on Fri Jan 19 2007 - 13:47:00 CST