Stored Procedure ReturnValue (Oracle)

Mar 30, 2012 at 12:10 PM

Looking through the source code, I can see that this doesn't appear to be implemented as of yet.

Are there any plans to implement this? If there aren't I may take a look at it myself.

In the mean time, is there a way to access the underlying command object from a DbContext(), or the Connection, so that I could manually call a stored procedure and consume the return value, e.g a clob/blob?

cheers

Coordinator
Apr 1, 2012 at 2:03 AM

Hello, so far only output parameters are supported (check the documentation tab on how to use). Now if you need support for return values then I will add it. However I'm currently on travel abroad so I will first be able to add it next Sunday.

Until then you could try to use the OnExecuted event that you find on the Context type, and then cast the Command property to the Oracle specific Command type.

Regards,
Lars-Erik Kindblad 

Coordinator
Apr 1, 2012 at 2:35 AM

Actually on the Fluent Command there is support for adding return value parameters. I'm not able to test and verify it now but something like this should hopefully work:

var command = Context().Sql("AnyStoredProcedure")
.CommandTypes(CommandTypes.StoredProcedure)
.Parameter("AnyName", anyValue, DataTypes.AnyType, ParameterDirection.ReturnValue);

command.Execute();

string name = command.ParameterValue<string>("AnyName");

If you want to return a blob I guess you would want to use the VarBinary datatype.


Regards,
Lars-Erik Kindblad 

Apr 1, 2012 at 3:36 PM
Edited Apr 1, 2012 at 3:37 PM

That looks like exactly what I need. I'll not be able to test it
myself either for a week or so, because I'm also about to do some
travelling.

Thanks. I'll update this thread when I get back.

-stu

Apr 11, 2012 at 1:49 PM

I've just been testing this out, and it looks like what you posted won't work.

However. I have found that if you do something like this:

var command = Context.Sql(
                "SELECT SCHEMA.MY_FUNCTION('TEST', 'PARAM', '1') AS Data FROM DUAL")
                .CommandType(DbCommandTypes.Text);

return command.QuerySingle();

Then if you access the dynamic returned from command.QuerySingle(), then you can get at the data:

dynamic data = new ReportLoader().MyReport(args);
var document = new XmlDocument();

using(var stream = new MemoryStream(data.Data))
using(var reader = XmlReader.Create(stream))
{
    document.Load(reader);
}

// blob returned from the function is now in the document

Notice that the alias 'Data' in the actual SQL is magically turned into the property on the dynamic data object which contains the byte[] array returned from the BLOB on the database. I assume that it will work the same for any type returned from an Oracle function, but I haven't tested it.

Hurray for dynamic!

One thing I have noticed is that .Parameter() doesn't seem to pass the value into the sql query, maybe it's something to do with the apostrophes (which are required). I can live with doing a simple string.format, which is slightly inelegant, but not a big deal.

-stu

Coordinator
Apr 26, 2012 at 1:46 PM
Regarding the problem with the parameters, doesn't the following code work?
var command = Context.Sql("SELECT SCHEMA.MY_FUNCTION(@0, @1, @2) AS Data FROM DUAL")
		.Parameters("test", "param", 1);
return command.QuerySingle();

Could I get the SQL to construct your table with the blob field and the function? Then I can debug and find out why it doesn't work.