Participant

Answered

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

Forum|Forum|16 years ago
May 15, 2010
3 replies
5476 views

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.

insert into test_table (test_col)

values (

)

</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

This topic has been closed for replies.

Correct answer

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.

A

Anonymous

Correct answer

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.

J

jonathanjiangAuthor

Participant

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?

A

Adam Cameron.

Inspiring

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

J

jonathanjiangAuthor

Participant

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...

A

Adam Cameron.

Inspiring

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

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded