How to call proc in package with OUT param cursor?

edstaffin · Post by **edstaffin** » Thu 29 Apr 2010 18:34

Hi,
I have a pretty simple proc defined in a package as shown below.
My question is how do I can I call this using linq and get a IQueryable back? Right now, when I drop it on the designer I get the following which, as you can see returns a system.Int32.
Any help would be great!
THanks ... Ed

Code: Select all


       ''' 
        ''' There are no comments for Get_App_Role_List in the schema.
        ''' 
         _
        Public Function Get_App_Role_List( Sappname As String) As System.Int32
            Dim res As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod(), MethodInfo), Sappname)
            Return CType(res.ReturnValue, System.Int32)
        End Function


 PROCEDURE Get_App_Role_List(sAppName IN VARCHAR2, cData OUT cOutput) IS 
   /* --------------------------------------------------------------------------- 
          Procedure:    Get_App_Role_List 
          Developer:      Ed Staffin 
          Date:           12/5/8
          Description:  GET  list of roles
       --------------------------------------------------------------------------- */ 
  
      BEGIN 
          sProcName := 'Get_App_Role_List'; 
          OPEN cData FOR 
          SELECT ROLE_CODE, ROLE_DESC
          FROM APP_ROLE
        WHERE APP_NAME = sAppName; 
                                                          
      EXCEPTION 
      WHEN OTHERS THEN 
          BEGIN 
            COMMON.COMMON_PKG.Log_Error(APPNAME, USER, SQLERRM, sProcName); 
            RAISE_APPLICATION_ERROR(ORAERROR, 'An error has occurred.  Please contact system support for resolution'); 
          END; 
  END Get_App_Role_List;

Code: Select all

AndreyR · Post by **AndreyR** » Thu 06 May 2010 09:43

I have just performed a test by the help of procedure with the structure similar to the one you have posted.
I have got the IExecuteResult return type using the latest 5.60.124 build of dotCOnnect for Oracle.
If the problem persists in the latest build, please send me a script of the objects the procedure depends on, I will try to reproduce the exact situation.

beffes · Post by **beffes** » Wed 02 Feb 2011 11:02

Hi have the same problem (latest version installed )...

Code: Select all

DataContext.Designer.cs

/// 
        /// There are no comments for Getfornecedores in the schema.
        /// 
        [Function(Name=@"TEST.PK_TEST.GETFORNECEDORES")]
        public System.Int32 Getfornecedores([Parameter(Name="ID_TIPO", DbType="VARCHAR2")] string IdTipo, [Parameter(Name="PAIS", DbType="VARCHAR2")] string Pais, [Parameter(Name="LOCALIDADE", DbType="NUMBER")] System.Nullable Localidade)
        {
            IExecuteResult _GetfornecedoresResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IdTipo, Pais, Localidade);
            return ((System.Int32)(_GetfornecedoresResult.ReturnValue));
        }

is returning a INT32 instead of a Cursor

Code: Select all

(here is a part of the package)
...
TYPE csGetResultSet is REF CURSOR;
...
PROCEDURE GetFornecedores( id_tipo VARCHAR2, pais VARCHAR2, localidade NUMBER, resultado OUT csGetResultSet)

StanislavK · Post by **StanislavK** » Wed 02 Feb 2011 17:56

Thank you for the report. This is a known issue for stored procedures declared inside a package. The problem is that no complex types are generated automatically for shapes returned by such functions. As a temporary workaround, you can create this type manually and assign it to the function return type in the Model Explorer toolbar of Entity Developer.

The issue is fixed now, and the fix will be available in the nearest build, which we plan to release in about a week.

StanislavK · Post by **StanislavK** » Mon 14 Feb 2011 09:29

We have released the new 2.20.12 build of LinqConnect which includes the fix for adding Oracle package procedures to the model.

This build can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the information about other fixes and improvements available in LinqConnect 2.20.12, please refer to
http://www.devart.com/forums/viewtopic.php?t=20231