This discussion is archived
2 Replies Latest reply: Jan 10, 2013 5:11 AM by 983988 RSS

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

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

Legend

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