VARCHAR2 database column limitation

I have an interesting problem with one of our systems, which is partly database-related and partly Forms-related.

First a bit about the system itself. The system generates order despatch confirmation e-mails. It does this by loading order data, then using a script to generate the body of the e-mail, which is then stored in a table. The users review the generated mails using an Oracle Form and have the option to add their own comments to the data before they send the mail. When they click on the Send button, the Form concatenates the generated text, the additional text and a customer feedback questionnaire then mails it using (essentially) UTL_SMTP. The table the holds the e-mails is structured something like:

EMAILS

REF_NO           NOT NULL      NUMBER                [PK]
GENERATED_TEXT                 VARCHAR2(4000)
ADDITIONAL_TEXT                VARCHAR2(2000)
DATE_SENT                      DATE
SENT_BY                        VARCHAR2(30)

All was working well, until the users (inevitably!) decided to change the layout of the e-mail. This can now result in the generated text being more than 4000 characters long, which, of course, causes the generation script to fail.

What to do? Can't make the column any bigger. Don't know enough about CLOBs to know if the Forms functionality will still work if we go down that route. Perhaps a nested table to store 4000 character chunks?

Any suggestions would be greatly appreciated!

TIA H Received on Fri Nov 26 2004 - 09:48:58 CST