execute store procedure

[ssvd]

Hello!
Trying to run a query produces an error!
parameters do not match ...

Windows Mobile

Code: Select all

CREATE OR REPLACE PACKAGE BODY QREAL.PKG_QSTORE
IS

  function check_os
  (
    idnumber_ in number,
    status_ in number
  )
   return number  --0 - поддона нет и тара пост., 1 - поддона нет и наша тара, 2 - поддон есть пустой и тара поставщика, 3 - поддон есть пустой и тара наша
   -- 4 - поддон есть не пустой 
is
rn_oshead number;
cnt_ number;
begin
  begin
    select rn into rn_oshead from oshead where idnumber=idnumber_;
  exception 
  when no_data_found then
    if status_=1 then return 1;
    else return 0;
    end if;
  end;  
  select count(*) into cnt_ from osspec where prn=rn_oshead;
  if cnt_<>0 then return 4;
  end if;
  if status_=1 then return 3;
  else return 2;
  end if;
end;

   
END;
/

Code: Select all

            OracleConnection connection = new OracleConnection(_connectionString);

            Devart.Data.Oracle.OracleCommand oracleCommand;
            oracleCommand = new Devart.Data.Oracle.OracleCommand();

            connection.Open();

            OracleCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "pkg_qstore.check_os";

            Devart.Data.Oracle.OracleParameter result = new Devart.Data.Oracle.OracleParameter("result", OracleDbType.Number);
            result.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(result);

            OracleParameter idnumber_ = new OracleParameter("idnumber_", OracleDbType.Number);
            idnumber_.Value = Convert.ToUInt64(fRFIDPallet);
            cmd.Parameters.Add(idnumber_);

            OracleParameter status_ = new OracleParameter("status_", OracleDbType.Number);
            status_.Value = fPallet;
            cmd.Parameters.Add(status_);

            cmd.ExecuteNonQuery(); 

[ssvd]

Code: Select all

 OracleConnection connection = new OracleConnection(_connectionString);
            connection.Open();


            OracleCommand command = new OracleCommand();
            command.Connection = connection;

            command.CommandType = CommandType.Text;
            command.CommandText = "pkg_qstore.check_os";
            command.CommandType = CommandType.StoredProcedure;

            OracleParameter result = new OracleParameter("result", OracleDbType.Number);
            result.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(result);

            OracleParameter idnumber = new OracleParameter("idnumber_", OracleDbType.Number);
            idnumber.Value = Convert.ToInt64(Convert.ToUInt64(_ID));
            command.Parameters.Add(idnumber);

            OracleParameter status = new OracleParameter("status_", OracleDbType.Number);
            status.Value = fPallet;
            command.Parameters.Add(status);

            command.ExecuteNonQuery();

if I do so, an error occurs

ORA-04063: package body "QREAL.PKG_QSTORE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "QREAL.PKG_QSTORE"
ORA-06512: at line 2

[ssvd]

support help?
write them an e-mail, do not respond?

[Pinturiccio]

We have a two business day response policy, but we do our best to process requests as soon as possible. For more information, please refer to http://www.devart.com/dotconnect/oracle ... pport.html

Trying to run a query produces an error!
parameters do not match ...

We have reproduced the issue. For fix the issue you need to replace:

Code: Select all

result.Direction = ParameterDirection.Output;

with

Code: Select all

result.Direction = ParameterDirection.ReturnValue;

You have done it in your next post.

if I do so, an error occurs

ORA-04063: package body "QREAL.PKG_QSTORE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "QREAL.PKG_QSTORE"
ORA-06512: at line 2

The reason of this error is that your package has invalid package body. Try executing your function with another utility, for example SQL Developer. Probably you will get the same error.