[rt-devel] Oracle LIKE in CLOB support for RT - Done!

[Brook Schofield]

The support of RT on Oracle has just had a significant boost - after I read 
the documentation.

If the minimum requirement for RT becomes Oracle 9iR2 (9.2) the there is no 
need to specifically support CLOBs for LIKE queries. Since Oracle 8.x is 
being EOL'd at the end of this year - I would see little reason for people 
to deploy a new system (RT3.0) on a database(8.1.7) that will become 
unsupported.

Taken from the Documentation "Oracle 9i Application Developer's Guid - 
Large Objects (LOBs) Release 2 (9.2) Part Nuber A96591-01":
         http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl07mds.htm#137640

"The following SQL VARCHAR2 function and operators are now allowed for 
CLOBs, as indicated in Tabele 7-6:
         * INSTR related operators/functions
                 *INSTR() and variants (See Table 7-7)
                 *LIKE
                 *REPLACE()
         ...

etc...

I've tested this - and it works great. I don't know why I didn't test this 
earlier.

The next issue that we'll have with Oracle and CLOBs are the operations for 
adding and extracting the data - as our DBAs believe there is an issue with 
the 4k limit on inserts and selects. Will need to test this issue further.

Taken from the DBD::Oracle perldoc:
         http://search.cpan.org/author/TIMB/DBD-Oracle-1.13/Oracle.pm#Handling_LOBs

"To insert or update a large LOB, DBD::Oracle has to know in advance that 
it is a LOB type. So you need to say:

   $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });"

and

"One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has to 
be able to tell which parameters relate to which table fields. In all cases 
where it can possibly work it out for itself, it does, however, if there 
are multiple LOB fields of the same type in the table then you need to tell 
it which field each LOB param relates to:

   $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });"


I believe "large" refers to greater than 4k - so many operations will work 
out of the box. This could mean some work in the subclassing of 
DBIx::SearchBuilder::Record or modifications to RT::Record.pm to support 
LOB types. The above Oracle document details the 4,000 character limits as 
well.

-Brook

=========================================================================
=     _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/  _/ Brook Schofield                =
=    _/  _/ _/  _/ _/  _/ _/  _/ _/ _/   B.Schofield at griffith.edu.au    =
=   _/_/   _/_/_/ _/  _/ _/  _/ _/_/     Ph: +61 7 387 53779 - WCN 0.28 =
=  _/  _/ _/ _/  _/  _/ _/  _/ _/ _/     Directory Services Integration =
= _/_/_/ _/  _/ _/_/_/ _/_/_/ _/  _/     Griffith University QLD 4111   =
=========================================================================