Thanks for the replies and ideas. The datatype of my column is SYS.XMLTYPE which I understood to be a CLOB; which is the source of my confusion. CLOBs should hold up to 4 gig of data. I'm thinking that this error might not be referring to the datatype of my column, but rather, that oracle is not able to handle the length of the string I'm inserting as #XMLSTRING#
Michael, I think your solution would work, but like you said, I think there must be a better way to do it now-a-days. Thanks, Carlos On 9/26/07, Dawson, Michael <[EMAIL PROTECTED]> wrote: > > In the old days, when dealing with long, practically-unlimited strings, > I would create a table that would hold 4000-byte chunks of the original > string. I would insert 4000 bytes into a record, then increment a > related sequence number, until all bytes were inserted. > > When outputting, I would query for the string's ID and then sort it by > the sequence number. Then, loop over the results to put it all back > together. > > My table would have looked similar to: > > StringID : int > Sequence : int > Content : varchar(4000) > > For example, if the string contains 12,469 bytes, I would have three > records of 4000-bytes and one record of 469 bytes. > > Again, this was way back with an early version of Oracle (7.x), however, > it will still work today, although there are now better datatypes > available. > > M!ke > > -----Original Message----- > From: Carlos Paez [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 26, 2007 6:33 PM > To: CF-Talk > Subject: Parsing an XML file and inserting into Oracle SYS.XMLTYPE > > Hi, > > I am getting an error: "ORA-01704: string literal too long" when trying: > <cfset XMLfile = "motpo_exp_xml-ZX00009999-20070626-172221.xml"> > > <!--- Read XML file into string variable called xmlString ---> > <CFFILE ACTION="READ" FILE="#XMLfile#" VARIABLE="xmlString"> > > <cfquery name="iXMLorder" datasource="sds_dev"> > INSERT INTO xmlorders_new > VALUES('1', '#xmlString#') > </cfquery> > > I get this error when working with any XML file larger than 4k. > How am I supposed to use CFQUERY to insert the XML file as a string if > Oracle can't handle a string larger than 4k? > Has anyone worked around this problem before? > > Thanks, > Carlos > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289574 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
