Stored procedure with output parameters in linq to oracle

talsja · Post by **talsja** » Tue 14 Jul 2009 11:29

Hello,

I am building a asp.net /C# webapplication with oracle as underlaying database. I use linq to oracle to access de oracle db. The whole application is making use of exsisting stored procedures with input and output parameters. My experiance with oracle is zero. So now my question is how to deal with these output parameters.

My sp has 29 parameters consisting of input and output parameters
string i_inputParameter, ref object o_OutputParameter.

When i use linq to oracle i have to set all 29 parameters and cannot set only the input parameters because it expects all 29. So how can i do this?

i use context.MyOracleStoredProcedure(i_inputparameter, o_outputparameter);

Thanx in atvance

Talsja

AndreyR · Post by **AndreyR** » Tue 14 Jul 2009 13:42

If you need the values of out parameters, you'll at least have to pass into the generated method some empty parameters to obtain the values in.
In case you don't need the values of out parameters, you can write a wrapper method in the Datacontext.cs file calling the generated method with the set of input parameters only (inside the wrapper you can call the generated method with dummy out parameters).

talsja · Post by **talsja** » Tue 14 Jul 2009 13:56

AndreyR

Thanks for your replay. My stored procedure returns a recordset and i need the output parameters.

So what i have is a personmanager.
Here i have
public Object getPersons(int personID, string PersonName, ?, ?)
{
var context = new myDBContext()
{
}
}

talsja · Post by **talsja** » Tue 14 Jul 2009 13:58

sorry something went wrong

where was i

{
var myquery = context.myStoredProcedure(PersonID, PersonName, ?,?}
my sp has parameter ref object personID, ref object PersonName

so what do i fill in the question marks.
I need the output parameters

AndreyR · Post by **AndreyR** » Tue 14 Jul 2009 14:21

Let you have int outpar1 and object outpar2.
The simplest way is to execute something like this:

Code: Select all

{
int outpar1 = null;
object outpar2 = null;
var myquery = context.myStoredProcedure(PersonID, PersonName, ref outpar1, ref outpar2);
}

After the procedure is called, the variables outpar1 and outpar2 contain the values of out parameters.

talsja · Post by **talsja** » Wed 15 Jul 2009 07:27

Hi AndreyR,

Your example has solved my problem.
Thank u very much for sharing your knowledge.

Greatings Talsja

Vijay · Post by **Vijay** » Wed 22 Jul 2009 19:09

I am having the same issue with object outparam. I am using the new release. Still getting error message saying "The parameter at index 0 in the parameters array is null". Any suggestions?

Thanks

AndreyR · Post by **AndreyR** » Thu 23 Jul 2009 14:01

There were no fixes associated with LINQ and stored procedures in the current build.
Have you tried the workaround?

Vijay · Post by **Vijay** » Thu 23 Jul 2009 18:33

Thank you for the prompt reply.

1) Can you please direct me to the work around?

2) Is the fix for LINQ and Stored procedures scheduled in future builds? if so any indication of when that would be available?

Thanks

AndreyR · Post by **AndreyR** » Fri 24 Jul 2009 08:33

Please show the code you are using for the stored procedure call.
Maybe the problem is not the one described in this post.

Vijay · Post by **Vijay** » Mon 27 Jul 2009 17:01

Code: Select all

       Dim fi As New FIEntities()
            Dim outpar As Object = Nothing
            Dim p = fi.AssocRead(outpar)
            Dim j As ASSOCIATE = p.FirstOrDefault()

This was the code I was using when I get the "parameters array is null" error. [/code]

AndreyR · Post by **AndreyR** » Tue 28 Jul 2009 07:53

I have just tried to execute a stored procedure with the out cursor parameter and everything succeeded.
Please post here or send me (support * devart * com with subject "LINQ SP: Out parameters")
the script of the procedure and of the entity the procedure is mapped to.