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?

 

Answer 1

User364480375 posted

what error your are getting??

Answer 2

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};
/