Hello,
I am running into issues trying to import a stored procedure to my entity framework model. I followed the detailed guide “Entity Framework, LINQ and Model-First for the Oracle Database” - “Function Imports and Retrieving Implicit Resultsets | Inserting and Updating Data Directly” I found at https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/appdev/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.html#section5
This guide is well written and super easy to follow. Using this guide I was able to get my stored procedure imported to my entity model. When I ran a unit test, I got the following exception:
ORA-06550: line 1, column 8: PLS-00306: wrong number or types of arguments in call to 'GET_ROLES_BY_USER'. ORA-06550: line 1, column 8: PL / SQL: Statement IGNORED.
As I said above, I followed the guide step by step, Visual Studio code generated the complex type for the results, the function import, and the new method on my DbContext to invoke the stored procedure. The only code I wrote was to call the method: GetRolesByUser that was added after finishing the steps in the guide.
Any assistance would be greatly appreciated. I will provide more details below for the code of the stored proc, the code generated method in my DbContext, the metadata added to my app.config file, and my code I wrote to call the new method.
Version Info:
Visual Studio 2022 Enterprise
Entity Framework 6.4.4
Oracle.ManagedDataAccess - 21.9.0
Oracle.ManagedDataAccess.EntityFramework - 21.9.0
Oracle Developer Tools for Visual Studio - 21.11.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Stored Proc: This procedure is part of a package named: ROLES.
PROCEDURE GET_ROLES_BY_USER(p_USERNAME IN VARCHAR2, p_RESULTSET OUT MISC.CURSORTYPE) AS
BEGIN
OPEN p_RESULTSET FOR
SELECT CATEGORY_NAME, ROLE_NAME, ROLELEVEL, ROLENAME, ROLE_CATEGORY_ID, DESCRIPTION
FROM TBLROLENAMES, TBLUSERROLES, TBLROLECATEGORIES
WHERE TBLROLENAMES.ROLENAME = TBLUSERROLES.ROLEID
AND TBLROLENAMES.ROLE_CATEGORY_ID = TBLROLECATEGORIES.CATEGORY_ID
AND (TBLUSERROLES.USERNAME = p_USERNAME)
ORDER BY CATEGORY_NAME, ROLE_NAME;
END GET_ROLES_BY_USER;
Visual Studio Code Generated Method Added To DbContext:
public virtual ObjectResult<HrdGetRolesByUserResult> GetRolesByUser(string p_USERNAME)
{
var p_USERNAMEParameter = p_USERNAME != null ?
new ObjectParameter("P_USERNAME", p_USERNAME) :
new ObjectParameter("P_USERNAME", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<HrdGetRolesByUserResult>("GetRolesByUser", p_USERNAMEParameter);
}
Ref Cursor Metadata Added To App.Config:
<oracle.manageddataaccess.client>
<version number="*">
<implicitRefCursor>
<storedProcedure schema="HRIS" name="ROLES.GET_ROLES_BY_USER">
<refCursor name="P_RESULTSET">
<bindInfo mode="Output" />
<metadata columnOrdinal="0" columnName="CATEGORY_NAME" providerType="Varchar2" allowDBNull="false" nativeDataType="Varchar2" />
<metadata columnOrdinal="1" columnName="ROLE_NAME" providerType="Varchar2" allowDBNull="true" nativeDataType="Varchar2" />
<metadata columnOrdinal="2" columnName="ROLELEVEL" providerType="Decimal" allowDBNull="true" nativeDataType="Number" />
<metadata columnOrdinal="3" columnName="ROLENAME" providerType="Varchar2" allowDBNull="false" nativeDataType="Varchar2" />
<metadata columnOrdinal="4" columnName="ROLE_CATEGORY_ID" providerType="Decimal" allowDBNull="true" nativeDataType="Number" />
<metadata columnOrdinal="5" columnName="DESCRIPTION" providerType="Varchar2" allowDBNull="true" nativeDataType="Varchar2" />
</refCursor>
</storedProcedure>
</implicitRefCursor>
</version>
</oracle.manageddataaccess.client>
Model Browser: Shows Store - Stored Proc, Function Import, and Complex Type
Add Function Import Screen:
Edmx File: Sections related to the stored procedure I am trying to leverage.
<!-- SSDL content -->
<Function Name="ROLES_GET_ROLES_BY_USER"
Aggregate="false"
BuiltIn="false"
NiladicFunction="false"
IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion"
Schema="HRIS"
StoreFunctionName="ROLES.GET_ROLES_BY_USER">
<Parameter Name="P_USERNAME" Type="varchar2" Mode="In" />
</Function>
<!-- CSDL content -->
<FunctionImport Name="GetRolesByUser"
ReturnType="Collection(HrdModel.HrdGetRolesByUserResult)">
<Parameter Name="P_USERNAME" Mode="In" Type="String" />
</FunctionImport>
<ComplexType Name="HrdGetRolesByUserResult">
<Property Type="String" Name="CATEGORY_NAME" Nullable="false" />
<Property Type="String" Name="ROLE_NAME" Nullable="true" />
<Property Type="Decimal" Name="ROLELEVEL" Nullable="true" />
<Property Type="String" Name="ROLENAME" Nullable="false" />
<Property Type="Decimal" Name="ROLE_CATEGORY_ID" Nullable="true" />
<Property Type="String" Name="DESCRIPTION" Nullable="true" />
</ComplexType>
<!-- C-S mapping content -->
<FunctionImportMapping FunctionImportName="GetRolesByUser" FunctionName="HrdModel.Store.ROLES_GET_ROLES_BY_USER">
<ResultMapping>
<ComplexTypeMapping TypeName="HrdModel.HrdGetRolesByUserResult">
<ScalarProperty Name="CATEGORY_NAME" ColumnName="CATEGORY_NAME" />
<ScalarProperty Name="ROLE_NAME" ColumnName="ROLE_NAME" />
<ScalarProperty Name="ROLELEVEL" ColumnName="ROLELEVEL" />
<ScalarProperty Name="ROLENAME" ColumnName="ROLENAME" />
<ScalarProperty Name="ROLE_CATEGORY_ID" ColumnName="ROLE_CATEGORY_ID" />
<ScalarProperty Name="DESCRIPTION" ColumnName="DESCRIPTION" />
</ComplexTypeMapping>
</ResultMapping>
</FunctionImportMapping>
Data Access Implementation:
I hope that I provided enough details to properly explain my issue. I have been trying to work this out for a little over a day with no luck. I either did something wrong, or missed a step in the guide but I did it multiple times so I believe I have everything configured correctly. It feels like a bug, the REF CURSOR contains the return data from the stored proc and the Add Function Import wizard seems to collect what is required to map the data over via the app.config metadata to map out the columns being returned from the REF CURSOR and the complex type created to store the data being returned.
Thanks in Advance!
Patrick Shaffer