3 Replies Latest reply: Dec 6, 2012 5:06 PM by 973111 RSS

    How to call stored procedure with sysref cursor returning a custom table

    973111
      How to call stored procedure with sysref cursor returning a custom table from entity framework.

      I have followed instructions given in http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.htm.

      Mapped every column in app.config file
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursor.MYCUR_ACCT_P"
                     value="implicitRefCursor bindinfo='mode=Output'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.0"
           value="implicitRefCursor metadata='ColumnName=SESSION_ID;BaseColumnName=SESSION_ID;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.1"
           value="implicitRefCursor metadata='ColumnName=ACTIVITY_TIME;BaseColumnName=ACTIVITY_TIME;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=Date;ProviderType=Date;ProviderDBType=DateTime;DataType=System.DateTime;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.2"
           value="implicitRefCursor metadata='ColumnName=REC_TYPE;BaseColumnName=REC_TYPE;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.3"
           value="implicitRefCursor metadata='ColumnName=ACCT_SEQ;BaseColumnName=ACCT_SEQ;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=number;ProviderType=Decimal;ProviderDBType=Decimal;DataType=System.Decimal;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.4"
           value="implicitRefCursor metadata='ColumnName=acct_nbr;BaseColumnName=acct_nbr;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.5"
           value="implicitRefCursor metadata='ColumnName=equity_time;BaseColumnName=equity_time;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=Date;ProviderType=Date;ProviderDBType=DateTime;DataType=System.DateTime;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.6"
           value="implicitRefCursor metadata='ColumnName=acct_status_code;BaseColumnName=acct_status_code;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.7"
           value="implicitRefCursor metadata='ColumnName=acct_status_desc;BaseColumnName=acct_status_desc;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.8"
           value="implicitRefCursor metadata='ColumnName=account_balance;BaseColumnName=account_balance;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=number;ProviderType=Decimal;ProviderDBType=Decimal;DataType=System.Decimal;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.9"
           value="implicitRefCursor metadata='ColumnName=insured_name;BaseColumnName=insured_name;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.10"
           value="implicitRefCursor metadata='ColumnName=pmt_plan_desc;BaseColumnName=pmt_plan_desc;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.11"
           value="implicitRefCursor metadata='ColumnName=due_time;BaseColumnName=due_time;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=Date;ProviderType=Date;ProviderDBType=DateTime;DataType=System.DateTime;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.12"
           value="implicitRefCursor metadata='ColumnName=tbi_date;BaseColumnName=tbi_date;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=Date;ProviderType=Date;ProviderDBType=DateTime;DataType=System.DateTime;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.13"
           value="implicitRefCursor metadata='ColumnName=due_amt;BaseColumnName=due_amt;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=number;ProviderType=Decimal;ProviderDBType=Decimal;DataType=System.Decimal;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.14"
           value="implicitRefCursor metadata='ColumnName=fee_amt;BaseColumnName=fee_amt;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=number;ProviderType=Decimal;ProviderDBType=Decimal;DataType=System.Decimal;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.15"
           value="implicitRefCursor metadata='ColumnName=record_nbr;BaseColumnName=record_nbr;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=number;ProviderType=Decimal;ProviderDBType=Decimal;DataType=System.Decimal;AllowDBNull=true'" />
      <add name="Scott.my_pkg.MY_INTERFACE.RefCursorMetaData.MYCUR_ACCT_P.Column.16"
           value="implicitRefCursor metadata='ColumnName=bill_to_party;BaseColumnName=bill_to_party;BaseSchemaName=my_pkg;BaseTableName=inq_2_output;NATIVEDATATYPE=varchar2;ProviderType=varchar2;ProviderDBType=String;DataType=System.String;AllowDBNull=true'" />

      Imported a function and created a complextype object for out cursor

      and calling that SP
      var someVar = new SomeEntities();

      //Function Imposrt is SOMESP
      //Complex object class is function name is

      SOMESP_Result cmplObj = new SOMESP_Result();

      ObjectParameter refObj = new ObjectParameter("MYCUR_ACCT_P", cmplObj);


      //var refObj = new EFUL_INTRFC_PKG_INQUIRY2_ACCT_INTERFACE_Result();
      List<SOMESP_Result> dataQuery = someVar.SOMESP(someName, someNumber, refObj).ToList();

      someName, someNumber are IN parameters of SP

      I am getting an error "The DbType 'Object' is not valid for the EntityParameter.DbType property on the 'MYCUR_ACCT_P' object.

      data types for cursor columns
      SESSION_ID VARCHAR2(30),
      ACTIVITY_TIME DATE,
      REC_TYPE VARCHAR2(1),
      ACCT_SEQ NUMBER(22) not null,
      ACCT_NBR VARCHAR2(30),
      INSURED_NAME VARCHAR2(100),
      ACCOUNT_BALANCE NUMBER(14,2),
      EQUITY_TIME DATE,
      ACCT_STATUS_CODE VARCHAR2(2),
      ACCT_STATUS_DESC VARCHAR2(30),
      PMT_PLAN_CODE VARCHAR2(6),
      PMT_PLAN_DESC VARCHAR2(30),
      DUE_TIME DATE,
      TBI_DATE DATE,
      DUE_AMT NUMBER(14,2),
      FEE_AMT NUMBER(14,2),
      RECORD_NBR NUMBER(22)

      Please help......................