CODEWITHSUNDEEP
c#oracle-databaseentity-frameworkiisstored-procedures
Renan Rigo Calesso
Renan Rigo Calesso

Reputation: 73

Procedure with out parameter (SYS_REFCURSOR) with Entity Framework

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:

Error
I've tried some solutions but none was successful:

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;

Some information that I think are necessary:

If you can help me...

Very thanks in advance!

Upvotes: 3

Views: 2465

Answers (2)

mandragora
mandragora

Reputation: 1

I had the same issue and this showed me the way to a solution.

The cause of the issue was that I had installed the OracleManagedDataAccessClient through NUGET but someone else had done a native install of the client. When the client was installed, it created an entry in the Machine.Config. When NUGET installed it created an entry in the Web.config. When trying to execute, I received an error that the section for:

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

Already existed. I commented it out and my service worked but Oracle would fail with the error:

"PLS-00306: wrong number or types of arguments in call"

When the client was installed it was an earlier version of Oracle so the entry in the Machine.config was:

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

Since I commented the entry in web.config, the application used the machine.config entry which did not match the actual version.

I modified the entry in the machine.config to match the web.config and everything now works.

Upvotes: 0

Renan Rigo Calesso
Renan Rigo Calesso

Reputation: 73

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.

Upvotes: 2

Related Questions