Stored procedures are almost always the preferred place to put database intensive calculations. Normally they do their thing and are done, and occasionally we get simple values out from them (strings and numbers). And then there are the occasions where we want something more complex, like a LOB (large object binary) value.
If you were selecting a LOB value from a normal query, the code to fetch the value would look something like this:
byte[] binaryBlob = (Byte[])ds.Tables[0].Rows[0]["doc_bl"];
But if your stored procedures looks like this:
PROCEDURE clob_out (OUT CLOB) is ...
things get much more complex.
First, realize that passing out any LOB value from a stored procedure is essentially making it temporary, which means all your database code must be inside of a transaction. If you forget to do this, you will get strange errors you will troubleshoot until you slap your forehead several hours later.
Second, you cannot use the GetOracleLob function that shows up in Visual Studio's help (and Enterprise Library's), as that is associated with a DataReader, which requires an output ref cursor from Oracle. If you try it, you will get an error that says you do not have the right number of parameters associated with the stored procedure. That's because Enterprise Library adds a cursor parameter for you! (will remember that for future use!)
Onto the solution code:
db = DatabaseFactory.CreateDatabase() as OracleDatabase;
OracleCommandWrapper cmd = db.GetStoredProcCommandWrapper( "MyStoredProc" ) as OracleCommandWrapper;
cmd.AddParameter( "CLOB", System.Data.OracleClient.OracleType.Clob, 0, ParameterDirection.Output, true, 0, 0, "CLOB", DataRowVersion.Default, null );
using ( IDbConnection connection = db.GetConnection() )
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
try
{
db.ExecuteNonQuery( cmd, transaction );
Stream s = (Stream) cmd.GetParameterValue( "CLOB" );
Int32 clobSize = Convert.ToInt32( s.Length );
byte[] myValue = new byte[clobSize];
int read = s.Read( myValue , 0, clobSize );
}
finally
{
transaction.Commit();
}
}
Looks easy, right? Note that there could be a potential error with the clobSize variable if your binary would exceed the Int32.MaxValue size, in which case you should implement some form of buffered reads.
Also note that I had to use the fully qualified AddParameter method. That's because, for my situation, I needed a CLOB, not a regular BLOB.
Enjoy!