Forum .LRN Q&A: acs-mail-lite uses text not clobs ...

Posted by Don Baccus on
SloanSpace has run across a fairly severe problem that's filling my
mailbox with watchdog error reports ...

If a package, say forums, sticks something longer than 4K bytes into a
notification everything more or less barfs due to the use of text
rather than CLOB fields. It looks like it causes sweeping to fail and
leads to repetitive failure because the offender neither gets sent nor

I'm digging into this in a few minutes but ... is there any reason not
to use CLOBs here? I realize they're a bit less efficient but isn't it
best to send the full text of forum posts and the like no matter what?

Posted by C. R. Oldham on

I think you have to use CLOBs for another reason, too.  SQLPlus still can't handle string literals longer than 2499 characters, and I think the Oracle ODBC driver has this problem as well.  This means that if you have to create a script that does literal inserts/updates of some text and your text is longer than 2499 characters then SQLPlus won't do the insert.  Also, if you edit a field in Access (ugh, I know, but it's sort of the best client on Windows to browse the database contents) and it had content of more than 2499 characters it gets truncated.

The disadvantage to clobs is that you can't say

select x from y where x like '%somevalue%';

(If I'm wrong about that I'd love to hear about it).

Posted by Ben Adida on
Don: where are you seeing this use of "text"? In PG, we use text, but in Oracle,
I see the use of clob in notifications and acs-mail-lite. Am I looking in the
wrong place?
Posted by Andrew Grumet on
Ben, Don: I think the issue here might be with a lack of error checking on the SUBJECT string length. Please see
Posted by Andrew Grumet on
To elaborate on the previous note...As a short-term fix, I upped the size of NOTIFICATION_REPLIES.SUBJECT from 100 to 500 characters.  Presumably, the offending inbound note was slurped in, generating new outbound notes to alertees.  Because the inbound note had a long subject line, so will the outbound note.  If the SUBJECT column for acs-mail-lite is 100 chars, that would explain the errors that prompted Don's post.  [I would check directly but don't have ssh access at this moment].
Posted by Don Baccus on
Right, that'll teach me to post before I investigate a bug rather than afterwards.  Good ... I couldn't imagine Ben et al using a non-CLOB there but jumped to conclusions after seeing the error message!

I'll look into chopping off the subject before trying to insert it ...

Sorry for that!