This discussion is archived
3 Replies Latest reply: Dec 6, 2012 3:06 PM by 973111 RSS

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

973111 Newbie
Currently Being Moderated
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......................

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points