execute store procedure

execute store procedure

Postby 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

Postby 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

Postby ssvd » Fri 31 Jan 2014 02:13

support help?
write them an e-mail, do not respond?
ssvd
 
Posts: 4
Joined: Thu 30 Jan 2014 03:00

Re: execute store procedure

Postby 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/docs/?Support.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.
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle