Pass BLOBs to Oracle Stored Procedures

Probably the most difficult task that I’ve had to conquer in quite a while is something that’s so mundane and so simple in other database products… that being the the ability to pass a large binary object (a.k.a. “BLOB”) to a stored procedure in Oracle.

My God! Were these people smoking crack when they designed this “feature”?? They would have to try really hard to make it more difficult than they did. At first try, it seemed to have worked, I mean, I successfully sent binary streams of files to my first Oracle stored procedure that accepts BLOBS. Then, later, it all fell apart with odd errors. As it turns out, you can pass up to 32KB directly into a stored procedure BLOB parameter with no problems. It’s when you pass a BLOB that’s BIGGER than 32KB to an Oracle stored procedure where all the “fun” begins!

But first, let’s take a look at a simple Microsoft SQL Server stored procedure that accepts BLOBs (and works incredibly well with BLOBs as large as 2GB):

create procedure MicrosoftBlobProcedure(@MyBlob image) as begin

    insert into MyBlobTable (MyBlobField) values (@MyBlob);

 

end

Pretty straight forward right? Well yes… for Microsoft SQL Server that is. Now, let’s look at the equivelent for Oracle:

[GARD] 

create or replace procedure OracleBlobProcedure(p_MyBlob blob)
as begin
insert into MyBlobTable (MyBlobField) values (p_MyBlob);
end;

OK, the above Oracle procedure works just fine as long as you’re passing in 32KB or less!!! If you try passing in anything greater than 32KB, you’ll start getting useless error messages.

So, how do you fix it? You must change BOTH your procedure and radically change the code that calls it. First, the rewrite of the stored procedure:

 

 

create or replace OracleBlobProcedure(p_MyBlob out blob)
as
l_MyBlob blob;
begin
insert into MyBlobTable (MyBlobField) values (Empty_Blob());
l_MyBlob := p_MyBlob;
return l_MyBlob;
end;

So, what’s up with that you say? Here’s what: Notice that the parameter is now defined as an OUT variable?!?!? Instead of passing your data IN to the procedure, the procedure will pass the variable OUT to you! Just hang on! It gets worse!

Like I said, you don’t pass data INTO the procedure, instead it passes a reference back out to your calling code. Instead of the procedure inserting your BLOB data directly into the table, the procedure instead inserts the results of a call to the function Empty_Blob(). This basically creates a reference to an empty BLOB object. Then assigns it to your local blob variable, then returns the local blob variable back to your code that called the stored procedure. It gets worse!

So, the stored procedure is now DONE and you haven’t passed your data in!!! Well, you do that AFTER the stored procedure completes. No really! Wait! Where are you going?

OK, now we have to jump over to your calling code. We’ll use C# as an example. First, you have to create a transaction (from your calling code) before you call the stored proc. Then you open your database connection, then call the stored proc (passing it NOTHING!). You’ll get back a reference to the empty BLOB created inside the stored procedure. From here, you write your bytes to the referenced object, then end your transaction, then close your connection.

Makes perfect sense, right??? NOT! But that’s the ONLY way to do it! Here’s the C# code to accomplish this:

 

 

using System.Data.OracleClient;

/*… bunch of clutter …*/

private void WriteBlobToOracleAsIfImSmokingCrack(byte[] MyBytes)
{
OracleCommand command = new OracleCommand();
command.connection = new OracleConnection();
command.connection.ConnectionString = “”;
Transaction tx = command.connection.BeginTransaction();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = OracleBlobProcedure;
OracleParameter parm = new OracleParamter(“p_MyBlob”).Direction = out;
command.Parameters.Add(parm);

    command.connection.Open();
command.ExecuteNonQuery();
OracleLob MyLob = command.Parameters[“p_MyBlob”].value;
MyLob.write(MyBytes, 0, MyBytes.Length());
tx.EndTransaction();
command.connection.Close();
}

So there you have it. It was all so obvious, right! I am at a loss as to what in the world they were thinking when they came up with this!! It’s just pure insanity!