That is about what I have, but I need to do the insert if the data doesn't exist. Which, as I sit here thinking about it, when I do the select at the beginning of the page I know if it didn't find it, and I can do the insert at that time, thereby eliminating the need to try an insert at the end of the page. Must have been the collective minds of all the west coast people reading my email . . . thanks . . . :) Dan -----Original Message----- From: Jennifer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 05, 2000 5:19 PM To: [EMAIL PROTECTED] Subject: Re: Oracle queries - 4000 character limit . . . anything new? I certainly do have that. Please hold.... Pasting.... type something like this in SQL command line: CREATE PROCEDURE update_Body(p_ID IN NUMBER, b1 IN VARCHAR2) BEGIN UPDATE TABLENAME SET BODY = b1 WHERE TABLENAME.ID = p_ID; END; In cfm page: <cfstoredproc procedure="update_Body" datasource="datasourcename" debug="Yes"> <cfprocparam type="in" value="#ID#" cfsqltype="CF_SQL_INTEGER"> <cfprocparam type="in" value="#Body#" cfsqltype="CF_SQL_LONGVARCHAR"> </cfstoredproc> This sets up a variable that can contain mucho text and dumps the text in the variable. Then it puts the variable in the SQL statement. Then it's not a 4k LITERAL text string. At 04:50 PM 7/5/00 -0700, you wrote: >Anyone know of any new workarounds for the 4000 character limit on >updates/inserts to an Oracle database, or a fix for the problem? This is >the one that returns an error of "string literal too long". I am on CF4.5, >Oracle8.0.5, NT4.0, native drivers. It works using a stored procedure, but >my code for the stored procedure below, which is as simple as I think I can >get it, is taking too long. If it fails on the insert and then does the >update, the normal scenario, I'm losing about 60-80ms, compared to the 10ms >it takes to select the same data out of the table through the native >drivers. > >Thanks for any insights . . . . >Dan > >** Oracle Stored Procedure ** >procedure update_state(p_cfid IN varchar2, p_state IN long) >as >begin > insert into app_state > (cfid, state, lastwritten) > values > (p_cfid, p_state, sysdate); >exception > when dup_val_on_index then > update app_state > set state = p_state, > lastwritten = sysdate > where cfid = p_cfid; >end; > >--------------------------------------------------------------------------- --- >Archives: http://www.mail-archive.com/[email protected]/ >To Unsubscribe visit >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or >send a message to [EMAIL PROTECTED] with 'unsubscribe' in >the body. ---------------------------------------------------------------------------- -- Archives: http://www.mail-archive.com/[email protected]/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. ------------------------------------------------------------------------------ Archives: http://www.mail-archive.com/[email protected]/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
