Forum OpenACS Q&A: acs-mail-lite throws ORA-01461 or ORA-24816 if *client* is on 10g
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
That's statement acs_mail_lite::send.create_queue_entry in file acs-mail-lite/tcl/acs-mail-lite-procs-oracle.xql.[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'))
oerr gives the following explanations:
and24816, 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.
Has anyone gotten this to work?01461, 00000, "can bind a LONG value only for insert into a LONG column" // *Cause: // *Action:
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 -r126.96.36.199 -r1.2 --- acs-mail-lite/tcl/acs-mail-lite-procs-oracle.xql 15 Jun 2005 01:46:32 -0000 188.8.131.52 +++ 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>
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.
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?
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!
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.