Forum OpenACS Q&A: acs-mail-lite throws ORA-01461 or ORA-24816 if *client* is on 10g

All is well when OpenACS, i.e., the AOLserver runs on a machine where Oracle 8.1.7 once was installed (but now shut down). Presumably it uses the old and the old driver to communicate to our Oracle 10g R2 database, which runs on a different host.

When running OpenACS with a new (10g R2) and driver, and talking to the same database, most things work just fine. Except notifications about bug tracker activity! Bug tracker uses workflow, which uses notifications, which relies on acs-mail-lite. Every minute notification::sweep::sweep_notifications runs and causes

[26/Jan/2007:00:01:28][15593.1117252816][-sched:7-] Error: nsoracle.c:1355:OracleSelect: error in `OCIStmtExecute ()': ORA-01461: can bind a LONG value only for insert into a LONG column

            insert into acs_mail_lite_queue 
            (message_id, to_addr, from_addr, subject, body, extra_headers, bcc,
             package_id, valid_email_p)
            (acs_mail_lite_id_seq.nextval, :to_addr, :from_addr, :subject, :body,
             :eh_list, :bcc, :package_id, decode(:valid_email_p,'1','t','f'))
That's statement acs_mail_lite::send.create_queue_entry in file acs-mail-lite/tcl/acs-mail-lite-procs-oracle.xql.

For reference, oerr gives the following explanations:

24816, 00000, "Expanded non LONG bind data supplied after actual LONG or LOB column"
// *Cause:  A Bind value of length potentially > 4000 bytes follows binding for
//          LOB or LONG.
// *Action: Re-order the binds so that the LONG bind or LOB binds are all
//          at the end of the bind list.
01461, 00000, "can bind a LONG value only for insert into a LONG column"
// *Cause:
// *Action:
Has anyone gotten this to work?
Christian: did you ever figure this out? Did reordering the body to the end of the insertion fix the issue?
Hi Jade,

Yes, reordering fixed it. Below my cvs diff, though your revision numbers will likely be different. In my cvs commit message, I noted: "Move the 3 CLOB binds to end. My fix for OpenACS bug 3087". See that and, from there, patch 841.

Thanks for picking up this loose end that I left!

diff -u -r1.1.1.2 -r1.2
--- acs-mail-lite/tcl/acs-mail-lite-procs-oracle.xql    15 Jun 2005 01:46:32 -0000
+++ acs-mail-lite/tcl/acs-mail-lite-procs-oracle.xql    19 Mar 2007 16:26:34 -0000      1.2
@@ -55,11 +55,13 @@
     <fullquery name="acs_mail_lite::send.create_queue_entry">
             insert into acs_mail_lite_queue 
-            (message_id, to_addr, from_addr, subject, body, extra_headers, bcc,
-             package_id, valid_email_p)
+            (message_id, to_addr, from_addr, subject,
+             package_id, valid_email_p,
+             body, extra_headers, bcc)
-            (acs_mail_lite_id_seq.nextval, :to_addr, :from_addr, :subject, :body,
-             :eh_list, :bcc, :package_id, decode(:valid_email_p,'1','t','f'))
+            (acs_mail_lite_id_seq.nextval, :to_addr, :from_addr, :subject,
+             :package_id, decode(:valid_email_p,'1','t','f'),
+             :body, :eh_list, :bcc)

I've committed that fix to HEAD. I've also added "cc_addr" to the end since it's a clob too.

Thanks for the fix.

Hello, I'm reviving this thread.

I'm working on Oracle 19c. I'm trying to make an update on a table that has a varchar2(4000) field and a CLOB field, and I'm getting the error ORA-24816.

I modified my code to make the update of the CLOB separately from the rest of the fields, but it stills throwing the error.

Have someone had this issue?

Hi Miguel

I haven't used 19c but I have seen those errors in the past. Things to check:

1. the database character set is correct and consistent with your Naviserver charset.
2. NLS_LANG and NLS_DATE_FORMAT environment variables are correctly set
3. in your UPDATE statement, the CLOB should be the last column listed
4. This is a bit of a wild one, but maybe you need to use TO_CLOB in the UPDATE?

hope that is of some help!

Thank you Brian,

This issue was solved. I was working in two different environments, development and test.

In development Oracle 19c was installed and it was working fine there, I thought in test environment the DB version was the same, but it was not, so it was updated and it is working now.

Thank you for your time.