Converting oracle query to ado.net query not working

Question

I am trying to write a ado.net query in c#. The below is the query.

DECLARE
   i_user_id      user_master.login_user_id%TYPE;
   i_whse         case_hdr.whse%TYPE;
   i_sku_id       item_master.sku_id%TYPE;
   i_actl_qty     case_dtl.actl_qty%TYPE;
   i_actl_wt      case_hdr.actl_wt%TYPE;
   i_orig_qty     case_dtl.orig_qty%TYPE;
   i_shpd_n_qty   case_dtl.shpd_asn_qty%TYPE;
   o_case_nbr     case_hdr.case_nbr%TYPE;
   o_error        VARCHAR2(2048);
   result         NUMBER;
BEGIN
i_user_id := 'PSI';
i_whse := '008';
i_sku_id := '2346701';
i_actl_qty := 1;
i_actl_wt := 20;
i_orig_qty := 1;
i_shpd_n_qty := 1;
o_case_nbr := NULL;
o_error := NULL;
result := inventory_engine.lpn_create(i_user_id, i_whse, i_sku_id, i_actl_qty, i_actl_wt, i_orig_qty, i_shpd_n_qty, o_case_nbr, o_error);
END; 

And I wrote this and tried many other variations. What am I doing wrong.

using (OracleConnection con = _dbService.GetOracleConnection())
{
    con.Open();
    string query2 =
                @"DECLARE
                    result number;
                BEGIN
                    result := inventory_engine.lpn_create(:i_user_id, :i_whse, :i_sku_id, :i_actl_qty, :i_actl_wt, :i_orig_qty, :i_shpd_n_qty, :o_case_nbr, :o_error);
                end;";
    var cmd = new OracleCommand();

    cmd.Connection = con;
    var result = new OracleParameter("result", OracleDbType.Decimal, 200, null, ParameterDirection.ReturnValue);
    cmd.Parameters.Add(result);
    cmd.Parameters.Add(new OracleParameter("i_user_id", OracleDbType.Varchar2, 200, skuDto.UserId, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("i_whse", OracleDbType.Varchar2, 200, skuDto.Warehouse, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("i_sku_id", OracleDbType.Varchar2, 200, skuDto.SkuId, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("i_actl_qty", OracleDbType.Decimal, 200, skuDto.ActualQuantity, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("i_actl_wt", OracleDbType.Decimal, 200, skuDto.ActualWeight, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("i_orig_qty", OracleDbType.Decimal, 200, skuDto.OriginalQuantity, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("i_shpd_n_qty", OracleDbType.Decimal, 200, skuDto.ShippedQuantity, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("o_case_nbr", OracleDbType.Varchar2, 200, null, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("o_error", OracleDbType.Varchar2, 2048, null, ParameterDirection.Input));

    cmd.CommandText = query2;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
    return (string)result.Value;
}

It throws the below error.

Oracle.ManagedDataAccess.Client.OracleException: 'ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "***.INVENTORY_ENGINE", line 316
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried giving the matching datatypes as in database. and now tried to increase it to a big number. It still throws the same error.

Note: The oracle query works perfectly fine in SQL developer.

Answer 1

You don't need an anonymous PL/SQL block. You can call the procedure simply as

string query2 = @"BEGIN
                     :result := inventory_engine.lpn_create(:i_user_id, :i_whse, :i_sku_id, :i_actl_qty, :i_actl_wt, :i_orig_qty, :i_shpd_n_qty, :o_case_nbr, :o_error);
                  end;";
cmd.CommandType = CommandType.Text;
cmd.CommandText = query2;

var result = new OracleParameter("result", OracleDbType.Decimal, 200, null, ParameterDirection.ReturnValue);
... adding Input parameters

Answer 2

After a lot head banging, I took the help of a friend. There were a couple issues in the code. 1. Changed the query to below so that it returns a proper value

string query2 =
    @"DECLARE
        result number;
    BEGIN
        result := inventory_engine.lpn_create(:i_user_id, :i_whse, :i_sku_id, :i_actl_qty, :i_actl_wt, :i_orig_qty, :i_shpd_n_qty, :o_case_nbr, :o_error);
        :result := result;
    end;";

2. Verify the data being passed i_whse = "008" but it was only reading as "8" fixed it. 3. Verify the parameter direction

var result = new OracleParameter("result", OracleDbType.Int32, 200, null, 
    ParameterDirection.ReturnValue);
cmd.Parameters.Add(result);
var caseNumber = new OracleParameter("o_case_nbr", OracleDbType.Varchar2, 20, null,
    ParameterDirection.InputOutput);
cmd.Parameters.Add(caseNumber);
var error = new OracleParameter("o_error", OracleDbType.Varchar2, 2048, null, 
    ParameterDirection.Output);
cmd.Parameters.Add(error);

4. Also updated the parameters with the actual size and precision.