[rt-devel] Oracle and RT

[Joop]

A while ago some people asked about Oracle and RT2. I have setup
RT-2.0.9 using Oracle 8.1.7 on a Solaris8 box.

To make it usable I had to change quite a few bits, I know of the
following problems:
1) select distinct * is not allowed on CLOB columns
2) LEFT JOIN is used diferently in Oracle
3) LIMIT x is used differently in Oracle

ad 1)
I changed all CLOB definitions to VARCHAR2(4000) eliminating the select
distinct error but also limiting attachments and ticket body to 4K, the
latter not being a real problem the former is annoying me.
ad 2)
To implement this correctly it would help if Searchbuilder could tell me
to which database I'm connected so that Oracle/Pg/mysql specific things
can be done conditionally else I/we would have to patch each new release
of Searchbuilder to include the db specific changes.
I have done some work on it by looking at where the LEFT JOIN is being
constructed and correct it for Oracle.
ad 3)
I just dropped the limit clause, because I tried using ROWID but that
just doesn't work and I don't know of another method to limit the number
of rows. Possible workaround is to use 'minus'.

Things would be a lot easier if the select distinct * didn't include the
CLOB columns. I have been looking at the RT code but can't figure out
where is determined what columns go into the select statement.
To my observation the following seems to happen:
Do a select distinct * on table x which gets you all columns in that
table and then fetch the data. The advantage is that you can fetch data
from any column, the disadvantage is that you try to compare huge CLOB
columns which is useless since ID is also included in the distinct. ID
is a sequence which is per definition 'distinct'.

My question is :
Is it possible to separate the fetching of the CLOB columns from the
rest of the select?

Joop

-- 
Joop <JoopvandeWege at mococo.nl>