how to call pl/sql function from asp.net?

Question

Hi... I have created a function in oracle as shown below:

I need to call it from my asp.net page... How can i do so?

 create or replace function fun_GeneratePaper(strDisciplineId IN CHAR,
  iNoOfQuestions IN integer)
  return sys_refcursor as
  r1 sys_refcursor;
  begin open r1 for select getguid() tmp,
   QuestionNo,QuestionText,
   Option1,Option2,
   Option3,Option4,
   Correctanswer,Disciplineid
   from tbliffcoQuestionmaster
   where DisciplineId=strDisciplineId
   AND  rownum <= iNoOfQuestions ;
  return(r1);
  end;

Answer 1

Just call it like a stored procedure using the Oracle .net driver

Answer 2

Something like this should get you on the right track:

OracleConnection connection = null;
OracleCommand command = null;
OracleDataReader reader = null;
OracleTransaction transaction = null; 

try
{
    connection = new OracleConnection(connectionString);;
    command = new OracleCommand();
    command.Connection = connection;
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "fun_GeneratePaper"; // May need to qualify with SCHEMA.fun_GeneratePaper

    command.Parameters.Add("strDisciplineId", OracleType.Char);
    command.Parameters["strDisciplineId"].Value = disciplineId;

    command.Parameters.Add("iNoOfQuestions", OracleType.Int32);
    command.Parameters["iNoOfQuestions"].Value = numberOfQuestions; 

    command.Parameters.Add("sys_refcursor", OracleType.Cursor);
    command.Parameters["sys_refcursor"].Direction = ParameterDirection.Output;

    connection.Open();
    transaction = connection.BeginTransaction();
    command.Transaction = transaction;
    reader = command.ExecuteReader();

    while(reader.Read())
    {
        // Do work
    }

    transaction.Commit();

}
catch(Exception e)
{
    if (transaction != null)
    {
        transaction.Rollback();
    }

    // Handle it
}
finally
{
    if (connection != null)
    {
        connection.Close();
    }
}

Note: This kind of code probably doesn't belong in your ASP.NET page, either way.