issue with oracle output parameters in c#

Question

I am so mad at oracle. I have been battling to get two values (output paramaters) from oracle database. I need to get two values (o_unallocated_ledgercode and o_allocated_ledgercode) based on paymentType and vendor.

What issue is: these values remain null in C#. I cannot get these values from database. what may be wrong? please look at my c# codes. i need someone's different perspective. I may have missed something. Please give me code example. Thanks!

Oracle package:

CREATE OR REPLACE package body BANKIMPORTS.pkg_vendor_config as

PROCEDURE get_ledger_codes (i_vendor in varchar2,
                            i_payment_type in varchar2,
                            o_allocated_ledgercode out varchar2,
                            o_unallocated_ledgercode out varchar2) is
BEGIN
  IF UPPER (i_payment_type) = 'SUBS' THEN
    SELECT CV.CONFIGVALUE
      INTO o_unallocated_ledgercode
      FROM VENDOR v
      JOIN VENDOR_CONFIG_ITEM_VALUE CV ON v.Id = CV.Vendor_Id
      JOIN CONFIG_ITEMS c ON CV.Config_Item_Id = c.Config_Item_Id
      WHERE C.ITEMNAME = 'Subs_Unallocated_LederCode'
        AND V.DESCRIPTION = i_vendor;

    SELECT CV.CONFIGVALUE
      INTO o_allocated_ledgercode
      FROM VENDOR v
      JOIN VENDOR_CONFIG_ITEM_VALUE CV ON v.Id = CV.Vendor_Id
      JOIN CONFIG_ITEMS c ON CV.Config_Item_Id = c.Config_Item_Id
      WHERE C.ITEMNAME = 'Subs_Payment_LedgerCode'
        AND V.DESCRIPTION = i_vendor;
  ELSIF UPPER (i_payment_type) = 'GOTV'
    ........same select statement as above

C# codes:

using (DbCommand command = connection.CreateCommand())
  {
  try
    {
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "pkg_vendor_config.get_ledger_codes";
    command.AddParameter("i_vendor", DbType.String, vendor, ParameterDirection.Input);
    command.AddParameter("i_payment_type", DbType.String, paymentType, ParameterDirection.Input);

    command.AddParameter("o_unallocated_ledgercode", DbType.String, ParameterDirection.Output);
    command.AddParameter("o_allocated_ledgercode", DbType.String, ParameterDirection.Output);

    command.ExecuteNonQuery();

    var unallocated = (String)command.Parameters["o_unallocated_ledgercode"].Value;
    var allocated = (String)command.Parameters["o_allocated_ledgercode"].Value;

Answer 1

tl;dr; I think you just need to set the parameters' Size properties.

According to docs.oracle.com's page on the OracleParameter class object, when we click on the Size property we see that the default maximum size for a parameter type is 0!

When I set all of your Varchar2 parameters I don't get the null values anymore. I had set them to 50 but that will be dependent on your VENDOR_CONFIG_ITEM_VALUE.CONFIGVALUE property's limits are set to.

C# code:

using (DbCommand command = connection.CreateCommand())
{
    connection.Open();

    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "pkg_vendor_config.get_ledger_codes";

    OracleParameter[] yourParams = new[] 
    { 
        new OracleParameter("i_vendor", OracleDbType.Varchar2, "subs", ParameterDirection.Input), 
        new OracleParameter("i_payment_type", OracleDbType.Varchar2, "cold hard cash", ParameterDirection.Input),
        new OracleParameter("o_allocated_ledgercode", OracleDbType.Varchar2, ParameterDirection.Output), 
        new OracleParameter("o_unallocated_ledgercode", OracleDbType.Varchar2, ParameterDirection.Output)
    };

    foreach(var param in yourParams)
    {
         param.Size = 50;
    }

    command.Parameters.AddRange (yourParams);

    command.ExecuteNonQuery();

    var unallocated = (String)command.Parameters["o_unallocated_ledgercode"].Value.ToString();
    var allocated = (String)command.Parameters["o_allocated_ledgercode"].Value.ToString();

    connection.Close();
}

Oracle package:

CREATE OR REPLACE Procedure pkg_vendor_config.get_ledger_codes(i_vendor in varchar2,
                                i_payment_type in varchar2,
                                o_allocated_ledgercode out varchar2,
                                o_unallocated_ledgercode out varchar2) is
BEGIN
        SELECT 'Another value'
        INTO o_unallocated_ledgercode
        FROM dual;

        SELECT 'Some value'
        INTO o_allocated_ledgercode
        FROM dual;

    /* I'll assume this all behaved for you

    IF UPPER (i_payment_type) = 'SUBS' THEN
    SELECT CV.CONFIGVALUE
      INTO o_unallocated_ledgercode
      FROM VENDOR v
      JOIN VENDOR_CONFIG_ITEM_VALUE CV ON v.Id = CV.Vendor_Id
      JOIN CONFIG_ITEMS c ON CV.Config_Item_Id = c.Config_Item_Id
      WHERE C.ITEMNAME = 'Subs_Unallocated_LederCode'
        AND V.DESCRIPTION = i_vendor;

    SELECT CV.CONFIGVALUE
      INTO o_allocated_ledgercode
      FROM VENDOR v
      JOIN VENDOR_CONFIG_ITEM_VALUE CV ON v.Id = CV.Vendor_Id
      JOIN CONFIG_ITEMS c ON CV.Config_Item_Id = c.Config_Item_Id
      WHERE C.ITEMNAME = 'Subs_Payment_LedgerCode'
        AND V.DESCRIPTION = i_vendor;
  ELSIF UPPER (i_payment_type) = 'GOTV'
    ........same select statement as above
 */

end;

Disclaimer:

I can't seem recreate your C# setup exactly; may have all been made obsolete since it's been nearly 6 years. But this is confirmed working

Answer 2

You are only setting up the values of the variables. If you want to return something use function.

FUNCTION get_ledger_codes
( 
  i_vendor in varchar2
, i_payment_type in varchar2
) RETURN VARCHAR2
is
    o_allocated_ledgercode   varchar2(100);
    o_unallocated_ledgercode varchar2(100);
BEGIN
    IF UPPER (i_payment_type) = 'SUBS'  THEN
        SELECT CV.CONFIGVALUE
        INTO o_unallocated_ledgercode
        FROM VENDOR v
             JOIN VENDOR_CONFIG_ITEM_VALUE CV ON v.Id = CV.Vendor_Id
             JOIN CONFIG_ITEMS c ON CV.Config_Item_Id = c.Config_Item_Id
        WHERE     C.ITEMNAME = 'Subs_Unallocated_LederCode'
             AND V.DESCRIPTION = i_vendor;

        SELECT CV.CONFIGVALUE
        INTO o_allocated_ledgercode
        FROM VENDOR v
             JOIN VENDOR_CONFIG_ITEM_VALUE CV ON v.Id = CV.Vendor_Id
             JOIN CONFIG_ITEMS c ON CV.Config_Item_Id = c.Config_Item_Id
        WHERE     C.ITEMNAME = 'Subs_Payment_LedgerCode'
             AND V.DESCRIPTION = i_vendor;

    ELSIF UPPER (i_payment_type) = 'GOTV'
        ........same select statement as above

    RETURN o_allocated_ledgercode || ',' || o_unallocated_ledgercode;
END;