[rt-devel] A bit of oracle clue?

[Marco Nijdam]

As quoted from Jesse:
> 
> > In Oracle you can not work with the contents of CLOBS, BLOBS, and for that
> > matter the LONG datatype, in queries directly. I'm not sure how the
> > Perl module solves this, but in Java or PL/SQL you get a reference to
> > the CLOB or BLOB from the select, and do some magic handwaving to
> > get the contents of the CLOB/BLOB based on this reference.
> >
> Wait. so I can't even search against the contents of a CLOB with a LIKE?

Correct. But if you would have Oracle interMedia enabled, it could be used
to create an index on the CLOB, allowing you to search for e.g. words
in the CLOB using the added keyword "CONTAINS".

Tables store a so called LOB locator, refering to the actual LOB contents.
So a SELECT will return only the locator. CLOB and BLOBS use copy
semantics for both locator and contents, so two LOB locators in a table
will always be different (so even if DISTINCT would work, every row
would be different because it would probably only compare the locators).

The deprecated LONG and LONG RAW datatypes work a bit different, more
like normal text columns, but you can't use these columns in a WHERE
clause either (except through interMedia), and you are restricted to
just one LONG column per table.

It might be possible to write a PL/SQL stored procedure (or stored function)
working on a LOB that could be used in an SQL query (in the WHERE
clause), though I never tried. E.g. a function that is passed a LOB locator
and a string, and inspects the contents of a LOB for the given string and
returns TRUE/FALSE. Of cause this is Oracle specific.

An (also not very clean) alternative would be to store large texts in
multiple rows. Oracle 8i allows up to 4K per varchar (Oracle 7 up to 2K).
So split up a text in 4K chunks and store those in a separate table.
Although it is clumsy and cumbersome, and less efficient in storage and
searching (compared to using Oracle interMedia), it would allow a
cross-database solution.

Less space efficient, but maybe easier would be to store a summary
in a normal varchar column, which can be used for searching etc.
and store the full text in a CLOB.


Kind regards,
--
-- Marco Nijdam,         marco at west.nl
-- West Consulting B.V., Delftechpark 5, 2628 XJ  Delft, The Netherlands
--                       P.O. Box 3318, 2601 DH  Delft
-- Tel: +3115 219 1600,  Fax: +3115 214 7889