2 Replies Latest reply: Apr 23, 2013 5:36 PM by 1004326 RSS

    Call to Oracle stored procedure that returns ref cursor doesn't work

    1004326
      I'm trying to use an OData service operation with Entity Framework to call an Oracle stored procedure that takes an number as an input parameter and returns a ref cursor. The client is javascript so I'm using the rest console to test my endpoints. I have been able to successful call a regular Oracle stored procedure that takes a number parameter but doesn't return anything so I think I have the different component interactions correct. When I try calling the proc that has an ref cursor for the output I get the following an error "Invalid number or type of parameters". Here are my specifics:

      App.config
      <oracle.dataaccess.client>
      <settings>
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYWINDFARMID.RefCursor.P_RESULTS" value="implicitRefCursor bindinfo='mode=Output'" />
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYWINDFARMID.RefCursorMetaData.P_RESULTS.Column.0" value="implicitRefCursor metadata='ColumnName=WINDFARM_ID;BaseColumnName=WINDFARM_ID;BaseSchemaName=PGDATA_WC;BaseTableName=WORKORDERS;NATIVEDATATYPE=Number;ProviderType=Int32'" />
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYWINDFARMID.RefCursorMetaData.P_RESULTS.Column.1" value="implicitRefCursor metadata='ColumnName=STARTTIME;BaseColumnName=STARTTIME;BaseSchemaName=PGDATA_WC;BaseTableName=WORKORDERS;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYWINDFARMID.RefCursorMetaData.P_RESULTS.Column.2" value="implicitRefCursor metadata='ColumnName=ENDTIME;BaseColumnName=ENDTIME;BaseSchemaName=PGDATA_WC;BaseTableName=WORKORDERS;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYWINDFARMID.RefCursorMetaData.P_RESULTS.Column.3" value="implicitRefCursor metadata='ColumnName=TURBINE_NUMBER;BaseColumnName=TURBINE_NUMBER;BaseSchemaName=PGDATA_WC;BaseTableName=WORKORDERS;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYWINDFARMID.RefCursorMetaData.P_RESULTS.Column.4" value="implicitRefCursor metadata='ColumnName=NOTES;BaseColumnName=NOTES;BaseSchemaName=PGDATA_WC;BaseTableName=WORKORDERS;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYWINDFARMID.RefCursorMetaData.P_RESULTS.Column.5" value="implicitRefCursor metadata='ColumnName=TECHNICIAN_NAME;BaseColumnName=TECHNICIAN_NAME;BaseSchemaName=PGDATA_WC;BaseTableName=WORKORDERS;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="PGDATA_WC.ODATAPOC.GETWORKORDERSBYID.RefCursor.P_RESULTS" value="implicitRefCursor bindinfo='mode=Output'" />
      </settings>

      OData Service Operation:
      public class OracleODataService : DataService<OracleEntities>
      {
      // This method is called only once to initialize service-wide policies.
      public static void InitializeService(DataServiceConfiguration config)
      {
      // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
      // Examples:
      config.SetEntitySetAccessRule("*", EntitySetRights.All);
      config.SetServiceOperationAccessRule("GetWorkOrdersByWindfarmId", ServiceOperationRights.All);
      config.SetServiceOperationAccessRule("CreateWorkOrder", ServiceOperationRights.All);
      config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
      }
      [WebGet]
      public IQueryable<GetWorkOrdersByWindfarmId_Result> GetWorkOrdersByWindfarmId(int WindfarmId)
      {

      return this.CurrentDataSource.GetWorkOrdersByWindfarmId(WindfarmId).AsQueryable();
      }


      [WebGet]
      public void CreateWorkOrder(int WindfarmId)
      {
      this.CurrentDataSource.CreateWorkOrder(WindfarmId);
      }

      }

      Here is the stored procedure:

      procedure GetWorkOrdersByWindFarmId(WINDFARMID IN NUMBER,
      P_RESULTS OUT REF_CUR) is
      begin
      OPEN P_RESULTS FOR
      select WINDFARM_ID,
      STARTTIME,
      ENDTIME,
      TURBINE_NUMBER,
      NOTES,
      TECHNICIAN_NAME
      from WORKORDERS
      where WINDFARM_ID = WINDFARMID;
      end GetWorkOrdersByWindFarmId;


      I defined a function import for the stored procedure using the directions I found online by creating a new complex type. I don't know if I should be defining the input parameter, WindfarmId, in my app.config? If I should what would that format look like? I also don't know if I'm invoking the stored procedure correctly in my service operation? I'm testing everything through the rest console because the client consuming this information is written in javascript and expecting a json format. Any help is appreciated!

      Edited by: 1001323 on Apr 20, 2013 8:04 AM

      Edited by: jennyh on Apr 22, 2013 9:00 AM