How to insert a XML node over 4k into an Oracle column type of XMLType with cfqueryparam

[jonathanjiang]

In one of the XML file that I parse, I have a sub-node that is over 4 kilobyte in length, and I am trying to insert this XML into a column in an Oracle 10g table defined as XMLType.

<cfquery name="qInsert" database="abc">

insert into test_table (test_col)

values (

     <cfqueryparam value="#variables.some_xml_4k_plus_in_size#" cfsqltype="cf_sql_clob">

)

</cfquery>

Now if only there is a:  cfsqltype="cf_sql_xmltype", is there a wall to get something like that accomplished...

Error casting an  object of type coldfusion.xml.XmlNodeList cannot be cast to  java.lang.String to an incompatible type.  This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way than it was  designed.

coldfusion.xml.XmlNodeList cannot be cast to  java.lang.String

[Adam Cameron.]

You're gonna have to toString() it first.  It's odd that CF doesn't do this automatically, because it does so in other situations in which one tries to use an XML object as a string (like <cfoutput>).

--

Adam

[jonathanjiang]

Converting the Node to string and typecasting it as cf_sql_clob works well.  However, with the XMLType in Oracle 10g, is there a way to insert a XML formated string greater than 4 KB to a Oracle table with a column defined as XMLType?  I can not seem to do that regardless of what I tried.

Maybe the Adobe engineers can offer some insights?

[Adam Cameron.]

Dunno sorry.  Is this an Oracle limitation, or a JDBC (~ driver) one, or a CF one, though (almost certainly not the latter, as CF is ignorant of DB stuff, for all intents and purposes)?  Can you insert >4kB with a non-JDBC client, like SQL Developer or something?

--
Adam

[jonathanjiang]

When I tried a direct insert from a TOAD editor window into a table column of 'CLOB', this is the error I got.  The same error if I do not use <cfqueryparam> bind variables in ColdFusion.

ORA-01704

string literal too long

Cause: The string literal is longer than 4000 characters.

Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

So, in order to pass a XML string great than 4 KB, I must use bind variables, which leaves <cfqueryparam> as the only option.

I think it is a cfqueryparam tries to check to make sure that cf_sql_clob matches the database type?  And when clob and xmltype does not match, it complains in my face .  Now I am left with inserting the data into the table column as 'CLOB', and converting or transforming the information later...

[Adam Cameron.]

What happens if you don't specify the CFSQLTYPE attribute?  Or maybe try CF_SQL_LONGVARCHAR?

--

Adam

[jonathanjiang]

It is fine as long as the literal string is under 4 KB.  Once it exceeds the 4 KB mark, then it does not matter if there is no CFSQLTYPE, or CF_SQL_LONGVARCHAR...

So, at this time, I think my only option is to insert it as a CF_SQL_CLOB...

[Adam Cameron.]

Hang on.

What error are you getting when you both toString() the XML, and use cf_sql_clob?

Is it still being truncated to 4000 characters?  Or are you getting an error that it's longer than 4000 characters and that's a no-go?

--

Adam

[jonathanjiang]

The insertion of XML data works fine with the following syntax, but the Oracle Table Column must be clob, instead of XMLType.

    <cfqueryparam value="#ToString(variables.Node)#" cfsqltype="cf_sql_clob">

thanks for the ToString suggestions.

JJ

[Guest · Accepted solution]

Ah got it!

oracle cast your  <cfqueryparam value="#variables.some_xml_4k_plus_in_size#" cfsqltype="cf_sql_clob"> as XMLTYPE()

such as following:

XMLTYPE(<cfqueryparam value="#variables.some_xml_4k_plus_in_size#" cfsqltype="cf_sql_clob">)

leaving the column type as xmltype.