2 Replies Latest reply: Jan 10, 2013 7:11 AM by 983988 RSS

    How to Pass more than 4000 character usign CLOB from C#

      Hi I’m trying run below query in oracle from C# application using ODP.NET ( using parameter bindings.
      columns ID varchar2(25) path '/ID') as IDXML
      C# Code
      OracleCommand oracleCommand = new OracleCommand();
      oracleCommand.Connection = oracleConnection;
      oracleCommand.CommandText = "SELECT ID " +
      "FROM XMLTABLE( " +
      "'Root/ID' PASSING " +
      "XMLTYPE(:ID) " +
      "columns ID varchar2(25) path '/ID') as IDXML ";
      oracleCommand.Parameters.Add(":IDLIST", OracleDbType. Varchar2);
      oracleCommand.Parameters[":IDLIST "].Value = idList;

      the above query work fine with less than 4000 charecters but my application may get more than 4000 size 3K or 4K size. What is the best data type we can be used in above scenario?
      we tried “Clob” data type but it throwing below error. We tried to pass the data in transaction scope for CLOB data type.
      “ORA-01460: unimplemented or unreasonable conversion requested” how to pass more data.

      Oracle version info
      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE     Production
      TNS for IBM/AIX RISC System/6000: Version - Production
      NLSRTL Version - Production

      Oracle.DataAcces.dll version(
      Thanks in advance

      Edited by: 980985 on Jan 10, 2013 12:13 AM