Sorry it took so long.. It seems that there is always a major crisis in the
morning.
-----------------------------------
PL/SQL Code
This takes the 4000 characters and appends them to the correct field
-----------------------------------
CREATE OR REPLACE PROCEDURE "WEB"."UPDATE_EXPLANATION" (pkey in number,
longparam in VARCHAR2, APPENDLOB IN INTEGER) IS
dblob CLOB;
amt BINARY_INTEGER;
BEGIN
amt := LENGTH(longparam);
SELECT BODY_CONTENT INTO dblob FROM CONTENT WHERE CONTENT_ID = pkey
FOR UPDATE;
IF dblob IS NULL THEN
UPDATE CONTENT SET BODY_CONTENT = ' ' WHERE CONTENT_ID = pkey;
SELECT BODY_CONTENT INTO dblob FROM CONTENT WHERE CONTENT_ID =
pkey FOR UPDATE;
END IF;
IF APPENDLOB = 0 THEN
DBMS_LOB.TRIM ( dblob, 0 );
END IF;
DBMS_LOB.WRITEAPPEND (dblob, amt, longparam);
COMMIT;
END;
-----------------------------------
End PL/SQL Code
-----------------------------------
-----------------------------------
CF Code
Description:
This loops over the form field text and puts 4000 character chunks in to
the CLOB
Vars:
N_BODY_AREA = Form field that contains the text to put in the CLOB
-----------------------------------
<CFSET Append = 0>
<CFLOOP FROM="1" TO="#Len(NEW_BODY_AREA)#" INDEX="currentPosition"
STEP="4000">
<CFSET currentBlock = MID(NEW_BODY_AREA, currentPosition, 4000)>
<CFSTOREDPROC DATASOURCE="#APPLICATION.DATASOURCE#"
PROCEDURE="UPDATE_EXPLANATION" DEBUG>
<CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_INTEGER"
VALUE="#evaluate(CONTENT_ID)#">
<CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_LONGVARCHAR"
VALUE="#currentBlock#" >
<CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_INTEGER"
VALUE="#Append#">
</CFSTOREDPROC>
<CFSET Append = 1>
</CFLOOP>
-----------------------------------
End CF Code
-----------------------------------
Of Course you'll need to make some changes to get it updating the correct
cols in your DB..
If you need any help figuring out what any of it does, let me know
-jb
-----Original Message-----
From: Jonathan Fisher [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 23, 2000 5:33 AM
To: [EMAIL PROTECTED]
Subject: Re: Support for NCLOB Datatype in Oracle
Thanks a lot, I'd really appreciate it!
J
----- Original Message -----
From: "James Berry" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 23, 2000 12:06 PM
Subject: RE: Support for NCLOB Datatype in Oracle
> I ran into this problem a while back. Apparently Cold Fusion has
> difficulties putting more than 4000 characters at a time into a CLOB in
> Oracle. I ended up writing a little PL/SQL script that would break down a
> character string passed to it into chunks and insert them one by one. I'm
> not sure if it will work on an NCLOB datatype, but it sounds very similar
> and I was getting the same error. I have the CF Code and the
accompanying
> PL Code. Will post it tomorrow morning when I get to work.
>
> -jb
>
>
> -----Original Message-----
> From: Jonathan Fisher [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, June 22, 2000 11:14 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Support for NCLOB Datatype in Oracle
>
>
> ok...here's where I am now - I switched to the CLOB datatype and still got
> the error. Then I upgraded the Cold Fusion from 4.5.0 to 4.5.1 and was
able
> to get a step further....the message I get now is :
>
> <H4>Error Diagnostic Information</H4>
> <P>Oracle Error Code = 1704
> <P>ORA-01704: string literal too long
> <P>
>
>
> So of course the question is - how can that be? Any suggestions?
>
> Thanks much,
>
> J
>
> ----- Original Message -----
> From: "Jonathan Fisher" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, June 21, 2000 4:24 PM
> Subject: Re: Support for NCLOB Datatype in Oracle
>
>
> > ok, thanks....Is there another type that could be used for storing large
> > amounts of text? The problem is with storing magazine article amounts of
> > text in a database, which we expect may be more than the 4000 character
> > limit allowed by the VARCHAR2 type.....
> >
> > J
> >
> > ----- Original Message -----
> > From: "Dave Watts" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Cc: <[EMAIL PROTECTED]>
> > Sent: Wednesday, June 21, 2000 4:01 PM
> > Subject: RE: Support for NCLOB Datatype in Oracle
> >
> >
> > > > Can anyone tell me whether the native driver for Oracle that
> > > > comes with Cold Fusion Enterprise 4.5 supports Oracle's NCLOB
> > > > data type?
> > >
> > > I'm not 100% certain, but I don't think so - N' data types aren't
ASCII,
> > but
> > > Unicode, which CF doesn't understand.
> > >
> > > Dave Watts, CTO, Fig Leaf Software
> > > http://www.figleaf.com/
> > > voice: (202) 797-5496
> > > fax: (202) 797-5444
> > >
> >
>
> --------------------------------------------------------------------------
> > ----
> > > Archives: http://www.eGroups.com/list/cf-talk
> > > 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.
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Talk to your friends online with Yahoo! Messenger.
> > http://im.yahoo.com
>
> --------------------------------------------------------------------------
> ----
> > Archives: http://www.eGroups.com/list/cf-talk
> > 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.
>
>
> __________________________________________________
> Do You Yahoo!?
> Talk to your friends online with Yahoo! Messenger.
> http://im.yahoo.com
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.eGroups.com/list/cf-talk
> 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.eGroups.com/list/cf-talk
> 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.
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
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.eGroups.com/list/cf-talk
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.
