Re: search on Oracle CHAR primary key column

[prev] [thread] [next] [Date index for 2004/08/25]

Hi Jim,

It's not really a Class::DBI problem. Maybe this will help you further:

Sounds to me like a problem with left shifted keys. If you store 'ABC' in  
a CHAR(4)
field and try to compare with 'ABC', your wan't retrieve any data in your  
query.
The reason is an 'ABC' value gets converted by oracle to 'ABC_' if you  
work with
CHAR(4) instead of VARCHAR2(4).
My experience is that perl modules working with database (not only  
Class::DBI)
sometimes truncate the spaces at the end (when you retrieve values from  
CHAR
columns for ex).
Don't worry, same problem exists if you use Pro/C Oracle Precompiler.
The way how strings from CHAR are retrieved or compared depends on several
precompiler parameters, real pain to handle...

Hints:
1. Use VARCHAR2 instead of CHAR. You wan't notice any serious performance
    improvement by using CHAR. It will save you a lot of trouble.
2. If you insist on CHAR (I warned you):
    - Use leading 0 if your keys are numbers (0001)
    - Use leading spaces (' ABC'), but your sorting will suck.
    - If you can't change the columns or introduce right shifted keys
      (had this problem too with somebody elses DB)
      Use trunc() function in your oracle queries ex.

	select * from some_table where trunc(key_col) = 'ABC';

      This is a preformance killer, because oracle wan't use any regular
      index created on your key_col column. To solve this problem
      you have to build special function index for this column...

good luck
- Adam


        -- 
        -----------------------------------------------------------------------
Adam Przygienda
Computer Science Engineer SEC
SWISSCOM Enterprise Solutions AG
IP-Plus Internet services  http://www.ip-plus.net
mailto:  adam@xxxxxxx.xxx
phone: +41 (0)1 274 65 62   mobile: +41 (0)79 777 41 17
-----------------------------------------------------------------------

> I need to interface with an Oracle table that has CHARs as primary key  
> columns (there are two), but I've been unable to get any  
> selects/searches to match anything unless I use search_like. I finally  
> figured out it was probably because the datatype is being set to VARCHAR  
> by default.
>
> Is there any way I can tell CDBI to treat these as CHARs and bind the  
> parameters correctly?
>
> I found a reference to the data_type method on the kwiki:
>
> http://www.class-dbi.com/cgi-bin/wiki/index.cgi?WorkingWithBlobs
>
> but was unable to get it to work.
>
> I also found this tidbit in the archives:
>
> http://groups.kasei.com/mail/arc/cdbi-talk/2004-07/msg00251.html
>
> but that didn't work either.
>
> Are one of these the solution? If so, I'll assume I'm doing something  
> wrong and keep trying.
>
> I'm using CDBI 0.94.
>
> Thanks,
> Jim
>
> ==========================================
> Jim Brandt
> Administrative Computing Services
> University at Buffalo
>