Calling an Oracle Stored Proc with CLOB from System.Data.OracleClient


You might have stumbled upon this and it might have made you tear your hair just as it did to me. When you try to call a stored procedure from .NET using System.Data.OracleClient, you won’t be able to pass a CLOB parameter value > 4000 Bytes.

I got the following error message when trying to do so:

ORA-01460 – unimplemented or unreasonable conversion requested

This issue has been reported in lots of places on the web already. Unfortunately I wasn’t able to find the right hint to remedy this problem – until a fellow employee of mine saved my day!

In fact, the solution, or rather workaround, is quite simple and can be found on http://henbo.spaces.live.com/blog/cns!2E073207A544E12!332.entry:

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.

Don’t let the “OracleDbType” (implying that the guy speaks about Oracle’s version of the client) derange you: The trick works for the Microsoft client just as well!

Advertisements

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