Skip to main content
J
jonathanjiang
Participant
May 15, 2010
Answered

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

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

<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

    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.

    3 replies

    A
    Anonymous
    Correct answer
    July 22, 2010

    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
    May 17, 2010

    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
    May 17, 2010

    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
    May 18, 2010

    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
    May 15, 2010

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices