Inserting a LONG field into Oracle
The LONG datatype is a weird one. If you attempt to insert a value that is less than 2000 characters it will return an error. However, if you attempt to insert a value that is greater than 2000 characters, you have to define it as a LONG type before putting it into the database. In the following example the Rules_HTML field is a LONG datatype but it may not be 2000 characters. Here is my CF code to deal with this information:
Example HTML/CFML code:
Code:
<!--- First set a variable to the length of the value of the Rules_HTML field --->
<CFSET RulesHTMLLength = Len(form.Rules_HTML)>
<CFQUERY NAME="UpdatePromo" DATASOURCE="#Application.Datasource#">
<!--- If the Rules_HTML length is gt 2000 characters define it as a LONG field --->
<CFIF RulesHTMLLength GT 2000>
DECLARE Rules_HTML_Temp LONG;
BEGIN
Rules_HTML_Temp := '#Rules_HTML#';
</CFIF>
UPDATE PROMO
SET Banner_Image_ID = #form.Banner_Image_ID#
, Logo_Image_ID = #form.Logo_Image_ID#
, Front_Page_Image_ID = #form.Front_Page_Image_ID#
, Begin_Date = '#DateFormat(form.Begin_Date, "DD-MMM-YYYY")#'
, End_Date = '#DateFormat(form.End_Date, "DD-MMM-YYYY")#'
, Notes = '#form.Notes#'
, Summary_HTML = '#form.Summary_HTML#'
, Tag_Line = '#form.Tag_Line#'
<!--- If the Rules_HTML field is a long datatype, insert the variable pointing to the value --->
<CFIF RulesHTMLLength GT 2000>
, Rules_HTML = Rules_HTML_Temp
<CFELSE>
<!--- If it's not a long datatype, do a normal insert, with the actual rules_html field itself --->
, Rules_HTML = '#form.Rules_HTML#'
</CFIF>
WHERE Promo_ID = #form.Promo_ID#
<!--- Then if the rules_html field is a long field, you have to add a semicolon and an END statement --->
<CFIF RulesHTMLLength GT 2000>
;
END;
</CFIF>
</CFQUERY>