Using oracle functions

Jupiler · Post by **Jupiler** » Tue 12 Apr 2011 11:53

I'm trying to understand how a simple oracle function (returning a string) can be used with the entity framework 4. I'm using the devart entity developer to create my edmx.
I'm only able to get this working when adding an output parameter in the entity method and setting the value by using the command text (ie: begin :result := mySchema.testfunction(); end;)
and then calling the function in visual studio with the output parameter:
ObjectParameter param = new ObjectParameter("result", "");
myObjectcontext.ExecuteFunction("Testfunction", param);
MessageBox.Show(param.Value.ToString());

But this seems to be a bit stupid, I would like to use the function like this:
ObjectResult result = myObjectcontext.ExecuteFunction("Testfunction");

My question is how to setup the entitymethod for this to work, I've set the return type to string and mapped to the oracle function but this results in a "PLS-00306: wrong number or types of arguments in call to 'TESTFUNCTION" error message.
Can anyone help or is there some documentation on the subject?
Many thanks

AndreyR · Post by **AndreyR** » Wed 13 Apr 2011 08:32

Please perform the following steps:
1. Drag the Testfunction function on the designer surface.
2. Go to the Moder Explorer->Model.Store. Edit the properties of this function: set Concealed to true.
3. Drag the function from the Model.Store to the designer surface.
4. Change the Return Type of the generated method (Model Explorer->Methods->Testfunction->Properties) to String.

Jupiler · Post by **Jupiler** » Wed 13 Apr 2011 08:52

Thank you for you reply, I’ve amended the properties like you explained. When I try to execute the code I get an error: EntityCommandExecutionException: The data reader returned by the store data provider does not have enough columns for the query requested.
I’m calling the function using:
ObjectResult result = EFContext.ExecuteFunction("Testfunction");
It seems like the result of the database function is not passed to the result of the generated method.

AndreyR · Post by **AndreyR** » Wed 13 Apr 2011 09:04

Call the function like in the following snippet:

Code: Select all

string s = myObjectcontext.Testfunction();

Jupiler · Post by **Jupiler** » Wed 13 Apr 2011 09:11

Hi Audry,
The function is not available in my objectcontext, I cannot see it on the edml/x either but it does exist as a method.
Any idea why?
Thanks

AndreyR · Post by **AndreyR** » Wed 13 Apr 2011 11:33

Are you using the default code generation?
Please send us the model or a small test project reproducing the issue.

Jupiler · Post by **Jupiler** » Wed 13 Apr 2011 11:44

No, I've set the code generation strategy to none and am using the ADO.NET C# POCO Entity Generator template. When setting it to default the function is available. Is there any way to use the template and still have the functions available on my objectcontext?

Jupiler · Post by **Jupiler** » Wed 13 Apr 2011 12:02

I've managed to get the functions available on my objectcontext but am still running into the same error:
"The data reader returned by the store data provider does not have enough columns for the query requested."
I'm sending you my edml by mail, maybe you are able to see what's wrong.

AndreyR · Post by **AndreyR** » Wed 13 Apr 2011 13:24

Take a look at the POCO template discussion.
It contains links to some corrections associated with the Stored Procedures.

Jupiler · Post by **Jupiler** » Wed 13 Apr 2011 13:33

The function is in my context now but I'm still getting the "The data reader returned by the store data provider does not have enough columns for the query requested." error, this is not a T4 template problem is it?

AndreyR · Post by **AndreyR** » Thu 14 Apr 2011 13:26

I have made a simple test using a very simple function (it just returned 'test') and succeeded using the latest 6.10.135 build of dotConnect for Oracle.
Could you please upgrade to the latest build?
Please let me know if the error persists.

Jupiler · Post by **Jupiler** » Mon 18 Apr 2011 08:34

Hi Andrey,
I've upgraded to the latest version and I'm now able to get the result like this:
ObjectResult result = myObjectcontext.Testfunction();
Your example with:
string s = myObjectcontext.Testfunction();
is not working, is there a property that I should change?
Regards,
Femke

AndreyR · Post by **AndreyR** » Mon 18 Apr 2011 09:13

Maybe your procedure returns a set of strings instead of single strings.
The code you are using is correct as well.

Jupiler · Post by **Jupiler** » Mon 18 Apr 2011 09:21

My function is only returning a single string:
CREATE OR REPLACE FUNCTION JIT.testfunction
RETURN VARCHAR2
IS
BEGIN
RETURN 'Test function = OK!!';
END;
/
How can I 'tell' the entity developer that it is returning a single string?
These are my settings:
Aggregate False
Built In False
Command Test
Composable False
Concealed Function True
Name TESTFUNCTION
Niladic False
Parameter Type Semantics AllowImplicitConversion
Result Set Parameter Name
Return Type VARCHAR2
Schema JIT
Store Procedure Name

AndreyR · Post by **AndreyR** » Mon 18 Apr 2011 10:38

Go to the Model Explorer->Model->Methods->Testfunction, open the Properties window, and set the Return Type to string. It should help.