silvia.fama wrote:
> 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.
If you can change the data type I would highly recommend it. I haven't
found a use for CHAR in a very long time.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Jan 17 2007 - 08:30:57 CST
