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

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

    983988
      Hi I’m trying run below query in oracle from C# application using ODP.NET (2.112.1.0) using parameter bindings.
      SELECT ID
      FROM XMLTABLE( 'Root/ID' PASSING
      XMLTYPE('<Root><ID>1234</ID><ID>123245</ID><ID>1232456</ID><ID>12324567</ID><ID>123245678</ID><ID>1232456789</ID></Root>')
      columns ID varchar2(25) path '/ID') as IDXML
      C# Code
      oracleConnection.Open();
      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 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      Oracle.DataAcces.dll version(2.112.1.0)
      Thanks in advance

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