1 Reply Latest reply: Jul 26, 2012 7:34 AM by WolfgangS RSS

    Passing XML file to CLOB argument > 32KB ?

    WolfgangS
      Hi.
      I'm quite an Oracle noob. This is my very first question here in this form.

      I can successfully pass an XML file < 32KB to the p_xml_in parameter of my import package.
      My Import package also processes successfully the xml as supposed.
      Unfortunately passing an XML file >32KB causes this error:
      07-26-2012 09:30:31 ERR:-2147217900 - 'ORA-01460: unimplemented or unreasonable conversion requested' from foo bar my connectionthingi

      It is clear for me that ORA-01460 comes when the value in p_xml_in is to big.
      The question is: How can I transfer very big XML files (> 10MB) to my pakage ? (I thought CLOB can hold many TBs? )


      My Code to call the package:
      With objCommand
          .CommandType = adCmdStoredProc
          .CommandText = "X_LOG_GENERIC.rs_xml_merge"
          
          WriteDebug "CLEAR"
          WriteDebug "1::" & Len(strXML)
          WriteDebug "2::" & strXML
          
          Call .Parameters.Append(.CreateParameter("p_xml_in", adLongVarChar, adParamInput, Len(strXML), strXML))
          Call .Parameters.Append(.CreateParameter("p_cnt_out", DataTypeEnum.adDecimal, ParameterDirectionEnum.adParamOutput))
          Call .Parameters.Append(.CreateParameter("p_tablename_in", adLongVarChar, adParamInput, Len(strTableName), strTableName))
          
          Call ConnectionServer.CommandExec(objCommand, g_strConnId)
          WriteDebug "3:: Execute success"
          
          vntCount = .Parameters("p_cnt_out")
        End With
      The Head of my Package:
      PROCEDURE rs_xml_merge
        (
          p_xml_in        IN CLOB,
             p_cnt_out       OUT NUMERIC,
              p_tablename_in  IN VARCHAR2
        ) AS
      
        v_dom DBMS_XMLDOM.DOMDocument;
        ...
      Thank you in advance.
      MfG WolfgangS


      My Oracle version:
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE 10.2.0.4.0 Production
      TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production

      Edited by: user622067 on 26.07.2012 00:57
        • 1. Re: Passing XML file to CLOB argument > 32KB ?
          WolfgangS
          After a hard day, i found the answer for myself.
          With objCommand
              .CommandType = adCmdStoredProc
              .CommandText = "X_LOG_GENERIC.rs_xml_merge"
              .ActiveConnection = m_objConnection     ' Set connection at first or exception
              .Properties("SPPrmsLOB") = true             '  ptRequiresLOBDataType. This allows to hand out xml strings > 32Kb :))
          
              WriteDebug "CLEAR"
              WriteDebug "1::" & Len(strXML)
              WriteDebug "2::" & strXML
              
              Call .Parameters.Append(.CreateParameter("p_xml_in", adLongVarChar, adParamInput, Len(strXML), strXML))
              Call .Parameters.Append(.CreateParameter("p_cnt_out", DataTypeEnum.adDecimal, ParameterDirectionEnum.adParamOutput))
              Call .Parameters.Append(.CreateParameter("p_tablename_in", adLongVarChar, adParamInput, Len(strTableName), strTableName))
              
              Call ConnectionServer.CommandExec(objCommand, g_strConnId)
              WriteDebug "3:: Execute success"
              
              vntCount = .Parameters("p_cnt_out")
            End With