execute store procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
ssvd
Posts: 4
Joined: Thu 30 Jan 2014 03:00

execute store procedure

Post by ssvd » Thu 30 Jan 2014 03:04

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(); 

Last edited by ssvd on Thu 30 Jan 2014 07:07, edited 1 time in total.

ssvd
Posts: 4
Joined: Thu 30 Jan 2014 03:00

Re: execute store procedure

Post by ssvd » Thu 30 Jan 2014 03:44

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
Posts: 4
Joined: Thu 30 Jan 2014 03:00

Re: execute store procedure

Post by ssvd » Fri 31 Jan 2014 02:13

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

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: execute store procedure

Post by Pinturiccio » Fri 31 Jan 2014 14:47

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
ssvd wrote: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.
ssvd wrote: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.

Post Reply