Answered by:
call function from c#

Question
-
User-1141190189 posted
i have a function in oracle with this signature
test_fun(p_nat_no in number,p_number_type in number) RETURN test_table
in the c# this is the code
string select = "test_fun"; using (OracleConnection connection = new OracleConnection(helper.Global.ConnectionString)) { OracleCommand command = new OracleCommand(select, connection); // command.CommandType = CommandType.StoredProcedure; OracleParameter PrmnatNo = new OracleParameter("p_nat_no ", natNo);//natNo command.Parameters.Add(PrmnatNo); OracleParameter pramNumberType = new OracleParameter("p_number_type ", numberType); command.Parameters.Add(pramNumberType); connection.Open(); using (OracleDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { List<test_table> Tickets = new List<test_table>(); while (reader.Read()) { Tickets.Add(GetPropertyTaxHelper(reader)); } return Tickets; } else return null; } } }
but when trying to call the function from c# an error appears, ? any help? suggestions?
Monday, December 19, 2016 8:26 AM
Answers
-
User269602965 posted
If you are returning a table, you need a third parameter for the OUT variable.
Would recommend use of REFCURSOR which you can then put into a data table, dataset, grid, et al.
/* VB.NET code in ASP.NET application */ ' Get data from stored procedure ' Try Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString Using conn As New OracleConnection(connstr) Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Clear() cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input) cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input) cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input) cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output) conn.Open() Using oda As New OracleDataAdapter(cmd) Dim ds As New DataSet() oda.Fill(ds) Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0) End Using End Using End Using Catch ex As Exception End Try /* CS.NET code in ASP.NET application */ ' Get data from stored procedure ' try { string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString; using (OracleConnection conn = new OracleConnection(connstr)) { using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input); cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input); cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input); cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output); conn.Open(); using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); oda.Fill(ds); this.RadGrid1.MasterTableView.DataSource = ds.Tables[0]; } } } } catch (Exception ex) {} /* Oracle PL/SQL code */ CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND IN DATE ,p_AOU_CODE IN NUMBER ,p_BIRD_CLASS IN VARCHAR2 ,ret_REFCUR OUT SYS_REFCURSOR); END PKG_BIRDS; / CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME} AS PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND IN DATE ,p_AOU_CODE IN NUMBER ,p_BIRD_CLASS IN VARCHAR2 ,ret_REFCUR OUT SYS_REFCURSOR) BEGIN OPEN ret_REFCUR FOR SELECT AOU_CODE, BIRD_CLASS, BIRD_NAME, DATE_BAND, WING_LENGTH, TAIL_LENGTH, EYE_COLOR, PLUMAGE_CODE FROM {SCHEMANAME}BANDED_BIRDS WHERE DATE_BAND > p_DATE_BAND AND AOU_CODE = p_AOU_CODE AND BIRD_CLASS = p_BIRD_CLASS END; END {SCHEMANAME}.{PACKAGENAME}; /
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, December 20, 2016 3:53 AM
All replies
-
User364480375 posted
what error your are getting??
Monday, December 19, 2016 11:24 AM -
User269602965 posted
If you are returning a table, you need a third parameter for the OUT variable.
Would recommend use of REFCURSOR which you can then put into a data table, dataset, grid, et al.
/* VB.NET code in ASP.NET application */ ' Get data from stored procedure ' Try Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString Using conn As New OracleConnection(connstr) Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Clear() cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input) cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input) cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input) cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output) conn.Open() Using oda As New OracleDataAdapter(cmd) Dim ds As New DataSet() oda.Fill(ds) Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0) End Using End Using End Using Catch ex As Exception End Try /* CS.NET code in ASP.NET application */ ' Get data from stored procedure ' try { string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString; using (OracleConnection conn = new OracleConnection(connstr)) { using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input); cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input); cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input); cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output); conn.Open(); using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); oda.Fill(ds); this.RadGrid1.MasterTableView.DataSource = ds.Tables[0]; } } } } catch (Exception ex) {} /* Oracle PL/SQL code */ CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND IN DATE ,p_AOU_CODE IN NUMBER ,p_BIRD_CLASS IN VARCHAR2 ,ret_REFCUR OUT SYS_REFCURSOR); END PKG_BIRDS; / CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME} AS PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND IN DATE ,p_AOU_CODE IN NUMBER ,p_BIRD_CLASS IN VARCHAR2 ,ret_REFCUR OUT SYS_REFCURSOR) BEGIN OPEN ret_REFCUR FOR SELECT AOU_CODE, BIRD_CLASS, BIRD_NAME, DATE_BAND, WING_LENGTH, TAIL_LENGTH, EYE_COLOR, PLUMAGE_CODE FROM {SCHEMANAME}BANDED_BIRDS WHERE DATE_BAND > p_DATE_BAND AND AOU_CODE = p_AOU_CODE AND BIRD_CLASS = p_BIRD_CLASS END; END {SCHEMANAME}.{PACKAGENAME}; /
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, December 20, 2016 3:53 AM