Procedure with out parameter (SYS_REFCURSOR) with Entity Framework

Question

I'm performing a migration of database, SQL Server to Oracle, in a C# application. The application mentioned, is using Entity Framework to access the database.

In this database there's a procedure that returns a "ComplexType", I researched and found that Oracle does not understand the "ComplexType" and I need to perform the mapping of the output parameter in web.config, then:

  <oracle.manageddataaccess.client>
    <version number="*">
      <implicitRefCursor>
        <storedProcedure schema="ALERTA_MPLUS" name="PR_CONSULTA_DADOS_ROBO">
          <refCursor name="CV_1">
            <bindInfo mode="Output" />
            <metadata columnOrdinal="0" columnName="Broker" baseColumnName="Broker" baseSchemaName="ALERTA_MPLUS" baseTableName="tt_Resultado_Final" nativeDataType="Varchar2" providerType="Varchar2" columnSize="50" />
            <metadata columnOrdinal="1" columnName="Companhia" baseColumnName="Companhia" baseSchemaName="ALERTA_MPLUS" baseTableName="tt_Resultado_Final" nativeDataType="Varchar2" providerType="Varchar2" columnSize="100" />
            <metadata columnOrdinal="2" columnName="Metrica" baseColumnName="Metrica" baseSchemaName="ALERTA_MPLUS" baseTableName="tt_Resultado_Final" nativeDataType="Varchar2" providerType="Varchar2" columnSize="50" />
            <metadata columnOrdinal="3" columnName="Q1" baseColumnName="Q1" baseSchemaName="ALERTA_MPLUS" baseTableName="tt_Resultado_Final" nativeDataType="NVarchar2" providerType="NVarchar2" columnSize="50" />
            <metadata columnOrdinal="4" columnName="Q2" baseColumnName="Q2" baseSchemaName="ALERTA_MPLUS" baseTableName="tt_Resultado_Final" nativeDataType="NVarchar2" providerType="NVarchar2" columnSize="50" />
            <metadata columnOrdinal="5" columnName="Q3" baseColumnName="Q3" baseSchemaName="ALERTA_MPLUS" baseTableName="tt_Resultado_Final" nativeDataType="NVarchar2" providerType="NVarchar2" columnSize="50" />
            <metadata columnOrdinal="6" columnName="Q4" baseColumnName="Q4" baseSchemaName="ALERTA_MPLUS" baseTableName="tt_Resultado_Final" nativeDataType="NVarchar2" providerType="NVarchar2" columnSize="50" />
          </refCursor>
        </storedProcedure>
      </implicitRefCursor>
    </version>
  </oracle.manageddataaccess.client>

This mapping worked perfectly in the development environment, but when I passed the application for approval gave the following error:

I've tried some solutions but none was successful:

• ODAC installed on the server approval;

• Searching, I found the Oracle documentation that indicates the mapping that way:

  <oracle.dataaccess.client>
    <settings>
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursor.CV_1" value="implicitRefCursor bindinfo='mode=Output'" />
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursorMetaData.CV_1.Column.0" value="implicitRefCursor metadata='ColumnName=Broker;BaseColumnName=Broker;BaseSchemaName=ALERTA_MPLUS;BaseTableName=tt_Resultado_Final;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursorMetaData.CV_1.Column.1" value="implicitRefCursor metadata='ColumnName=Companhia;BaseColumnName=Companhia;BaseSchemaName=ALERTA_MPLUS;BaseTableName=tt_Resultado_Final;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursorMetaData.CV_1.Column.2" value="implicitRefCursor metadata='ColumnName=Metrica;BaseColumnName=Metrica;BaseSchemaName=ALERTA_MPLUS;BaseTableName=tt_Resultado_Final;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursorMetaData.CV_1.Column.3" value="implicitRefCursor metadata='ColumnName=Q1;BaseColumnName=Q1;BaseSchemaName=ALERTA_MPLUS;BaseTableName=tt_Resultado_Final;NATIVEDATATYPE=NVarchar2;ProviderType=NVarchar2'" />
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursorMetaData.CV_1.Column.4" value="implicitRefCursor metadata='ColumnName=Q2;BaseColumnName=Q2;BaseSchemaName=ALERTA_MPLUS;BaseTableName=tt_Resultado_Final;NATIVEDATATYPE=NVarchar2;ProviderType=NVarchar2'" />
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursorMetaData.CV_1.Column.5" value="implicitRefCursor metadata='ColumnName=Q3;BaseColumnName=Q3;BaseSchemaName=ALERTA_MPLUS;BaseTableName=tt_Resultado_Final;NATIVEDATATYPE=NVarchar2;ProviderType=NVarchar2'" />
      <add name="ALERTA_MPLUS.PR_CONSULTA_DADOS_ROBO.RefCursorMetaData.CV_1.Column.6" value="implicitRefCursor metadata='ColumnName=Q4;BaseColumnName=Q4;BaseSchemaName=ALERTA_MPLUS;BaseTableName=tt_Resultado_Final;NATIVEDATATYPE=NVarchar2;ProviderType=NVarchar2'" />
    </settings>
  </oracle.dataaccess.client>
  

This is code return the following error (in all environments):

 ORA-06550: line 1, colunm 8: PLS-00306:
 wrong number or types of arguments in call to
 'PR_CONSULTA_DADOS_ROBO' ORA-06550: line 1, colunm 8: PL/SQL:
 Statement ignored;

• I published the application on another machine (another developer) and also worked;

Some information that I think are necessary:

• Visual Studio 2013;
• Oracle.DataAcess.dll version 4.121.1.0;
• Oracle.ManagedDataAccess.dll version 4.121.1.0;
• ISS 7.0;
• Pool - Enable 32-bit Applications true;

If you can help me...

Very thanks in advance!

Answer 1

After many trials and errors managed to solve. I do not know if it is the best solution but worked.

In machine.config exists a definition of <oracle.manageddataaccess.client>:

<configSections>
    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</configSections>

So I added this declaration in web.config and it worked perfectly.