Procedure with out parameter (SYSREFCURSOR) with Entity Framework

Asked

Viewed 238 times

1

I am having a problem that is keeping me awake. I am making a database migration, from SQL Server to Oracle, in a C#application. The application mentioned is using Entity Framework to perform database access.

I managed to migrate the base and access to the bank with peace of mind, however, there is a process that returns a "Complextype", I did some research and found out that Oracle doesn’t understand the Complextype and that I need to map the parameter output in the web config., soon:

  <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 it gave the following error: Erro no servidor de homologação I’ve tried some solutions but none were successful:

  • I installed the ODAC on the approval server;

  • Looking, I found an Oracle documentation that indicates the mapping in this 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 code returns the following error (in all the environments):

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

- I published the application on another machine (from another developer) and it also worked;

Some information I think is needed:

  • Visual Studio 2013;
  • Oracle.DataAcess.dll version 4.121.1.0;
  • Oracle.ManagedDataAccess.dll version 4.121.1.0;
  • ISS 7.0;

Update 11/11/14

If I put the code <oracle.manageddataaccess.client> in the machine.config works. When I cut out the machine.config and put in the web.config stops working and returns the same error.

Is there any other configuration file? Other than machine.config and of web.config? I find it strange to work on one and the other not.

If you can help me...

Thank you very much!

c# entity-framework oracle iis stored-procedures

  • Your application pool is 32 or 64 bit?

  • 32 bits. In all environments.

  • The archive machine.config has some configuration of this type?

  • @Gypsy Heart Mendez yes. It has the Manageddataaccess configuration.<oracle.manageddataaccess.client> <version number="4.121.1.0"> <Settings> <Setting name="tns_admin" value="c: app mjv product 12.1.0 client_1 network admin" /> </Settings> </version> </oracle.manageddataaccess.client>

  • You should not set like this. Or the settings are in Web.config or stay in the machine.config, even setting up different things. That must be the cause of your problem.

  • I already removed this information from machine.config and, in addition to continuing with the cited error, when I tested the other features gave error in Oracle dll.

  • The error happens when merging the settings of machine.config and Web.config? Or you simply deleted the statement on machine.config? What are the new mistakes?

  • @I’m at home now and I can not see but, one thing I assure you, in my machine the machine.config also has this configuration of Manageddataaccess and works normally.

  • @Ciganomorrisonmendez took the test by taking the machine.config and adding the information on the web.config. The error was exactly the same as the print above. But thanks for the suggestion. Do you have any more? rs.

Show 4 more comments

1 answer

1


After many tests and errors I managed to solve. I don’t know if it is the best solution but I solved.

In the machine.config there is 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 statement to web.config and worked perfectly. I believe that the web.config is not joining the settings of machine.config.

If you have similar problems try this which can work.

  • Was that it? Gosh. + 1.

  • @It’s... I thought it was strange not to conflict with the machine.config, when I pasted the same tag on web.config but it worked perfectly and did not harm any of the other applications.

Browser other questions tagged c# entity-framework oracle iis stored-procedures

You are not signed in. Login or sign up in order to post.