Calling oracle function which returns SYS_REFCURSOR throws an exception

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
focsi
Posts: 3
Joined: Fri 08 Jan 2016 07:14

Calling oracle function which returns SYS_REFCURSOR throws an exception

Post by focsi » Fri 08 Jan 2016 07:35

We have migrated dotConnect for Oracle 6.5 to 8.5.
Calling oracle function which returns SYS_REFCURSOR throws an exception in version 8.5.521.0. In version 6.5 it doesn't.
Same code in a procedure with out SYS_REFCURSOR works fine.

C# code:

Code: Select all

using( MIRTUSZContext.MIRTUSZDataContext mirtuszDC = new MIRTUSZContext.MIRTUSZDataContext() )
{
    var resultGood = mirtuszDC.FM_GET_DOKID_BY_HASH_OUT ( "test" ); // works fine
    var resultException = mirtuszDC.FM_GET_DOKID_BY_HASH( "test" ); // throws exception
}
Oracle package body:

Code: Select all

function FM_GET_DOKID_BY_HASH( I_LIVELINK_HASH in varchar2 ) return SYS_REFCURSOR is
        L_REC_SET   SYS_REFCURSOR;
        L_SQL_STR   varchar2(2000);    
begin
        open L_REC_SET for 'select 1 AZONOSITO, 
                                   ''abcd''  DOCID 
                              from dual';
        
        return L_REC_SET;    
end;

PROCEDURE  FM_GET_DOKID_BY_HASH_OUT( I_LIVELINK_HASH in varchar2, O_REC_SET OUT SYS_REFCURSOR ) is
        L_SQL_STR   varchar2(2000);    
begin
        open O_REC_SET for 'select 1 AZONOSITO, 
                                   ''abcd'' DOCID 
                              from dual';
        
end;
Exception: Error on executing DbCommand.

Code: Select all

ORA-06550: 2 row, 3 column:
PLS-00306: wrong number or types of arguments in call to 'FM_GET_DOKID_BY_HASH'
ORA-06550: 2 row, 3 column:
PL/SQL: Statement ignored

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Calling oracle function which returns SYS_REFCURSOR throws an exception

Post by MariiaI » Fri 08 Jan 2016 11:24

We have migrated dotConnect for Oracle 6.5 to 8.5.
LinqConnect uses its own classes since version 4.0/ dotConnect for Oracle 7.0.6 released on 22-May-12 (since this version the references to System.Data.Linq are removed, LinqConnect uses only its own classes; there were a lot of fixes/improvements since this version, including the code generation, compiled query cache, etc.).

The generated code for the method, which corresponds to the FM_GET_DOKID_BY_HASH function, should look like this:

Code: Select all

  [Function(Name=@"TEST.FM_GET_DOKID_BY_HASH")]
        [ResultType(typeof(FMGETDOKIDBYHASHOUTResult), 0, ResultTypeOrigin.ReturnValue)]
        public Devart.Data.Linq.ISingleResult<FMGETDOKIDBYHASHOUTResult> FMGETDOKIDBYHASH([Parameter(Name="I_LIVELINK_HASH", DbType="VARCHAR2(4000 CHAR)")] string I_LIVELINK_HASH)
        {
            IExecuteResult _FMGETDOKIDBYHASHResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), I_LIVELINK_HASH);
            return ((Devart.Data.Linq.ISingleResult<FMGETDOKIDBYHASHOUTResult>)(_FMGETDOKIDBYHASHResult.ReturnValue));
        }
Please refer to: https://www.devart.com/linqconnect/docs ... Value.html

We recommend you to make changes to this code this way:
- open Model Explorer;
- right-click the necessary method in Methods and select Attributes;
- click 'Yes' in opened dialog box;
- in the opened 'Model Settings' window click "Add..." and find and select 'Devart.Data.Linq';
- you will see the list of available attributes, please select only 'Devart.Data.Linq.Mapping.ResultTypeAttribute';
- select ResultTypeAttribute(Type, Int32, ResultTypeOrigin) and move it to the Selected Attributes;
- in Constructor Parameters set 'origin' to "ReturnValue", 'type' to you return type (e.g., FMGETDOKIDBYHASHOUTResult), order to 0;
- save changes to your model.

Please notify us about the results.

focsi
Posts: 3
Joined: Fri 08 Jan 2016 07:14

Re: Calling oracle function which returns SYS_REFCURSOR throws an exception

Post by focsi » Fri 08 Jan 2016 12:54

Thank you. This is a working solution. :-)

This line was missing from our generated code:

Code: Select all

[Devart.Data.Linq.Mapping.ResultType(typeof(Fm_Get_Dokid_By_HashResult), 0, Devart.Data.Linq.Mapping.ResultTypeOrigin.ReturnValue)]
I've followed your steps, and it genrated into code.

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Calling oracle function which returns SYS_REFCURSOR throws an exception

Post by MariiaI » Fri 08 Jan 2016 13:10

Glad to see that the issue was resolved.
If you have any further questions, feel free to contact us.

Post Reply