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 libclntsh.so 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) libclntsh.so 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

SQL: 
            insert into acs_mail_lite_queue 
            (message_id, to_addr, from_addr, subject, body, extra_headers, bcc,
             package_id, valid_email_p)
            values
            (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.

and

01461, 00000, "can bind a LONG value only for insert into a LONG column"
// *Cause:
// *Action:

Has anyone gotten this to work?

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!

/Christian

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      1.1.1.2
+++ 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">
         <querytext>
             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)
             values
-            (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)
         </querytext>
     </fullquery>

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?

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.