Procedure with out parameter (SYS_REFCURSOR) with Entity Framework
I am performing a SQL Server database to Oracle migration in a C # application. The mentioned application uses Entity Framework to access the database.
There is a procedure in this database that returns "ComplexType", I researched and found that Oracle does not understand "ComplexType" and I need to do an output parameter mapping in web.config and 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 fine in the development environment, but when I submitted the application for approval, the following error appeared:
I have tried some solutions, but none have been successful:
- ODAC is set to server approval;
-
Searching, I found Oracle documentation that points to mapping 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 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 app on a different machine (different developer) and it worked as well;
Some information that seems necessary to me:
- 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 ...
Thanks a lot in advance!
source to share
After many trials and mistakes, it was possible to solve. I don't know if this was the best solution, but it worked.
There machine.config
is a definition <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 ad in web.config
and it worked great.
source to share
I had the same problem and it showed me the way to a solution.
The cause of the problem was that I installed OracleManagedDataAccessClient
via NUGET and someone else did their own client installation. When the client was installed, it created an entry in Machine.Config. When NUGET is installed, it has created an entry in the Web.config. When trying to execute, I got 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 exists. I commented this out and my service worked, but Oracle failed with the error:
"PLS-00306: Invalid Number or Types of Arguments When Calling
When the client was installed, it was an earlier version of Oracle, so the entry in 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 on the entry in web.config
, the app was using an entry machine.config
that did not match the actual version.
I changed the entry in machine.config
to match the web.config and everything works now.
source to share