Oracle LIKE in CLOB support for RT - Done!

RT Developers
1

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@griffith.edu.au =
= // /// _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= /// / / /// /// _/ _/ Griffith University QLD 4111 =

2

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.

Yes. I don’t even want to think about porting to Oracle 8, because of
the broken oracle-specific left join syntax.

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.

So. I almost had to do this for postgres tonight, but didn’t want to
deal with the churn (and found a simpler solution).

There’s only one place of consequence where we actually do the parameter
binding in DBIx::SearchBuilder::Handle’s SimpleQuery method.

And the only two places where we pass in data that needs to have its
attributes tagged is Record->Create and Record->__Set. Both of these
methods have access to the ClassAccessible hash of table metadata,
including the column types (as defined in mysql). Perl has
“attributes” (perldoc attributes). It should be possible to tag things
that need special bind_param handling in __Set/Create and cope with that
in SimpleQuery. (perhaps by callign a function in the Handle subclass.

Does that make sense?

-Brook

Thanks!
Jesse

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

3
  • Jesse Vincent [2003-03-20 01:38]:

…Perl has “attributes” (perldoc attributes).

Perl 5.6.0+ has attributes, but I think DBIx::SearchBuilder currently
works with earlier versions. Might not be that big a deal, but
something to consider.

(darren)

The smart way to keep people passive and obedient is to strictly limit
the spectrum of acceptable opinion, but allow very lively debate
within that spectrum.
– Noam Chomsky

4

nod we should be able to special-case the attributes code inside of a
version-tagged if statement.

-jOn Thu, Mar 20, 2003 at 01:00:11PM -0500, darren chamberlain wrote:
  • Jesse Vincent [2003-03-20 01:38]:

…Perl has “attributes” (perldoc attributes).

Perl 5.6.0+ has attributes, but I think DBIx::SearchBuilder currently
works with earlier versions. Might not be that big a deal, but
something to consider.

(darren)

–
The smart way to keep people passive and obedient is to strictly limit
the spectrum of acceptable opinion, but allow very lively debate
within that spectrum.
– Noam Chomsky

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.