ORA-01460: unimplemented or unreasonable conversion requested

Prepare for some ugliness using CLOB parameters in System.Data.OracleClient.

When you write provider-independent code in ADO.NET, you might find out long string support breaks with Microsoft’s provider for Oracle.

SYMPTOM:

ExecuteNonQuery on your DbCommand fails with "ORA-01460: unimplemented or unreasonable conversion requested".

CAUSE:

If you prepare a CLOB parameter using DbType.String, System.Data.OracleClient has a limit of 32768 characters for the parameter value.

RESOLUTION:

Set the System.Data.OracleClient.OracleParameter.OracleDbType property to System.Data.OracleClient.OracleType.Clob.
 - AND -
Set the parameter value when BeginTransaction has already been called on the DbConnection.

If you want to avoid referencing System.Data.OracleClient, try this (at your very own risk)

Type paramType = param.GetType();

switch (paramType.Name)

{

    case "OracleParameter":

        paramType.GetProperty(

            "OracleType").SetValue(

            param,

            paramType.Assembly.GetType(

            "System.Data.OracleClient.OracleType").GetField(

            "Clob").GetRawConstantValue(),

            null);

        break;

    default:

        param.DbType = DbType.String;

        break;

}

I know, it’s indiscrete and ugly. But it gets the job done and pays for breakfast.

 

Disclaimer: All information and source code is provided "AS IS", for demonstrational purposes only, without warranty of any kind, neither express nor implied, including but not limited to the warranties of merchantability, fitness for a particular purpose or non-infringement. In no event shall the blog author be liable for any direct, indirect, incidental, special, exemplary, or consequential damages (including, but not limited to, procurement of substitute goods or services; loss of use, data, or profits; or business interruption) however caused and on any theory of liability, whether in contract, strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this code sample, even if advised of the possibility of such damage.

About these ads
This entry was posted in Coding Horror. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s