Re: Binding on a char column

Thank you very much!
I believe that the best is to use varchar instead of char.

I hoped oracle may give me the possibility to set an option or parameter that allows me not to use RTRIM with bind variables. I suppose it doesn't exist, and your very useful suggestion are the only way to resolve this problem!
May you confirm it?

Thank you and kind regards!

hpuxrac ha scritto:

> silvia.fama wrote:
> > Hi!
> > I'm using bind variables to execure select sql queries on my oracle
> > database (version 9 and version 10).
> > My database contains some char columns (char (50), char(1), etc)
> >
> > Using bind variables I have a problem with trailing blanks at the end
> > of the string in a char column. I mean that if I insert in a column of
> > char(10) type 'test' string, I will have in oracle db 'test '
> > string (with trailing blanks).
> > When I bind that column searching for 'test' string I'd need to RTRIM
> > the column:
> > select * from TABLE where RTRIM(col1) =:bind1
> > where bind1 will be associated to test.
> > If my query is:
> > select * from TABLE where col1 =:bind1
> > I will not have the result expected if bind1 is 'test', I should bind
> > 'test ' with blank.
> >
> > So, I know that RTRIM is not good for performance, does oracle gives me
> > the possibility to work into another way?
>
> When you use char instead of varchar2 column definitions in oracle and
> index them you basically have 2 good choices when working with bind
> variables in queries:
>
> 1) include in the bind variable value that you are using additional
> spaces to the end ... this will match how oracle fills up the char
> column with extra spaces
>
> 2) create a function based index on the column that matches the SQL
> that you will be using in your where clause
>
> Option 2 works pretty well when all the queries that you are using on
> that column work the same way ... they all include the same function
> against the char column. Or you can consider have 2 indexes one on
> just the char and the second a function based index but that's extra
> overhead.
>
> A good alternative to consider is using varchar2 instead of char. As
> far as I know, Tom Kyte is not a big fan at all of using char and ( my
> impression is ) pretty much advocates doing everything in oracle with
> varchar2.
Received on Wed Jan 17 2007 - 07:43:17 CST