This discussion is archived
1 Reply Latest reply: Jul 26, 2012 5:34 AM by 625070 RSS

Passing XML file to CLOB argument > 32KB ?

625070 Newbie
Currently Being Moderated
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 ?
    625070 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points