Updating Oracle Clob field

Forum|Forum|19 years ago
June 6, 2006
3 replies
1401 views

I am having some trouble updating an oracle db that has a clob field, this is the part of the query that is getting the form data:
<cfqueryparam cfsqltype="cf_sql_clob" value="#trim(form.additional_information_fr)#">

When I dump the form variables it is getting passed over ok, but when I output the update SQL statement I am getting a '?' next to the field and it inserts a blank string.

I also tried this: <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.additional_information)#"> which works as long as the data is less then 4000 charcters, I know there are issues with drivers and have incorporated the latest driver release from adobe: http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=1a3c2ad0

Still no luck, anyone else encounter issues with Oracle and Clob fields that could shed some light on the situation for me. I've been searching the web all day and tried a few different solutions without any success.

Database access

This topic has been closed for replies.

A

Anonymous

This is a codesnippet from and update action where we enter data into a clob field in Oracle.

<cfif IsDefined("Form.NEWS_STORY") AND #Form.NEWS_STORY# NEQ "">
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#Form.NEWS_STORY#">
<cfelse>
NULL
</cfif>

Looking at it the query param is the same as yours so i guess this isnt much help for you.

T

thomascraigAuthor

Known Participant

Matrix16,

Yes this method works up to 4000 characters as soon as I go 4001 I get an error.

U

usachrisk

New Participant

Having the same problem when using the JDBC drivers and trying to insert more than 1000 characters. On 1001, a blank line is inserted instead. When not using bind variables (CFQUERYPARAM), it'll accept up to 4000 characters (limitation of Oracle I'm pretty sure when not using bind variables). Using the JDBC drivers with another application allows much more than the 1000 characters that I can't seem to get through with CF7.

A

Anonymous

I have successfully updated a clob field without any issues, i will check out my code tomorrow and let you know how i did it.

P

paross1

Participating Frequently

Just curious, do you have Enable long text retrieval (CLOB) enabled in your datasource's advanced settings in CF admin?

Phil