Как правильно вызвать и получить результат хранимой процедуры из OracleSQL посредством OleDB, приложение Net.Framework, Windows Forms?

Процедура использует курсор для расчёта переменной.

create or replace Procedure  profit 
  (str_date IN date, prf OUT NUMBER)
IS
  val number := 0;
  CURSOR c1
  IS
    SELECT (PJ.COST-
                    (SELECT SUM(EMP.SALARY)
                     FROM PROJECTS PRJ,
                          DEPARTMENTS_EMPLOYEES DEPE,
                          EMPLOYEES EMP
                     WHERE PRJ.ID = PJ.ID
                           AND PRJ.DEPARTMENT_ID = DEPE.DEPARTMENT_ID
                           AND DEPE.EMPLOYEE_ID = EMP.ID
                           GROUP BY PJ.NAME)
                 *((SELECT EXTRACT(MONTH FROM DATE_END_REAL)
                    FROM PROJECTS
                    WHERE PROJECTS.ID = PJ.ID) -
                   (SELECT EXTRACT(MONTH FROM DATE_BEG)
                    FROM PROJECTS
                    WHERE PROJECTS.ID = PJ.ID))) AS profit
    FROM PROJECTS PJ
    WHERE PJ.DATE_END_REAL > str_date
    AND PJ.DATE_END_REAL < CURRENT_DATE
    AND PJ.DEPARTMENT_ID IS NOT NULL;
BEGIN
  prf := 0;
  OPEN c1;
  LOOP
  FETCH c1 INTO val;
  prf := prf + val;
  VAL := 0;
  EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END profit;

Попытка вызвать через C#:

            OleDbCommand GetProfit = new OleDbCommand("profit(@str_date,@pfr)", OleDbConnection);
            GetProfit.CommandType = CommandType.StoredProcedure;

            GetProfit.Parameters.Add("@str_date", OleDbType.DBDate).Value = StartingDateForProfByPeriod.SelectionStart.ToString("yyyy-MM-dd");
            GetProfit.Parameters.Add("@prf", OleDbType.Numeric);//.Value = profit;


           GetProfit.Parameters[0].Direction = ParameterDirection.Input;
          GetProfit.Parameters[1].Direction = ParameterDirection.Output;

            

            var reader1 = GetProfit.ExecuteReader();

            if (reader1.HasRows)
            {

                while (reader1.Read()) 
                {
                    ProfOfProjForPeriod.Text += (Convert.ToInt32(reader1[0]));
                }
            }

Вызывает ошибку: System.Data.OleDb.OleDbException: "Неопознанная ошибка"


Ответы (1 шт):

Автор решения: VLADISLAV

Правильный способ вызова процедуры:

            OleDbCommand GetProfit = new OleDbCommand("profit", OleDbConnection);
            GetProfit.CommandType = CommandType.StoredProcedure;

            GetProfit.Parameters.Add("str_date", OleDbType.DBDate).Value = StartingDateForProfByPeriod.SelectionStart;
            GetProfit.Parameters.Add("prf", OleDbType.Numeric);//.Value = profit;


            GetProfit.Parameters[0].Direction = ParameterDirection.Input;
            GetProfit.Parameters[1].Direction = ParameterDirection.Output;


            GetProfit.ExecuteNonQuery();
            ProfOfProjForPeriod.Text = Convert.ToString(GetProfit.Parameters[1].Value);
→ Ссылка