ODP.NET

I have a stored procedure GetByTariffBookId (see below). When this procedure is called with parameters (242,null,null) I get quite different results depending on the way it is called. When called from PL/SQL developer (test procedure), it returns 3 rows (as expected). But when called from C# using ODP.NET, I get ora-01722 (invalid number) error.

Does anyone have any clue?

PROCEDURE GetByTariffBookId (
p_tariff_book_id IN NUMBER,
date_from IN DATE,
date_to IN DATE,
return_value OUT NUMBER,
RC1 OUT sys_refcursor
)
is
statement_str VARCHAR2(2000);
from_date_str VARCHAR2(2000);
to_date_str VARCHAR2(2000);
date_condition VARCHAR2(2000);
error_str VARCHAR2(2000);
begin

return_value := 0;

date_condition := ' 1=1 '; --if which sets this date_condition is not important for this problem

statement_str := '
select distinct
d.service service_id,
d.tariff_zone tariff_zone_id,
d.start_date start_date,
d.end_date end_date,
d.billing_date billing_date,
d.billing_period_code billing_period_code,
nvl(d.discount_type,''STD'') discount_type,
d.discount_price discount_price,
d.price_description price_description,
d.lower_limit lower_limit,
d.upper_limit upper_limit,
s.service_name service_name
--,z.tariff_zone_name tariff_zone_name
from
interbilling_offline_discount d,
service s,
tariff_zone z
where --d.OPPOSITE_TBID is null and
d.service = s.service_id and
d.tariff_zone like ''%''||to_char(z.tariff_zone_id)||''%'' and
z.tariff_book_id = ' || p_tariff_book_id || ' and
' || date_condition || '
order by
s.service_name,
--z.tariff_zone_name,
d.start_date
';

OPEN RC1 FOR statement_str;

EXCEPTION
WHEN OTHERS THEN
return_value := SQLCODE;
error_str := SQLERRM;
OPEN RC1 FOR SELECT -1, error_str FROM dual;

end GetByTariffBookId;

This is C# code which calls procedure:

public static int GetDiscounts(
object dateFrom,
object dateTo,
out DataTable table)
{
Connect();

command.Parameters.Clear();
command.Parameters.Add("p_tariff_book_id", tariffBookId);
command.Parameters.Add("date_from", dateFrom);
command.Parameters.Add("date_to", dateTo);
command.Parameters.Add("return_value", OracleDbType.Int32, ParameterDirection.Output);

table = GetDataTableByProcedureName("GetByTariffBookId");
int returnValue = ((OracleDecimal)command.Parameters["return_value"].Value).ToInt32();
if (returnValue != 0)
throw new Exception("Oracle error " + returnValue.ToString());
return 0;
}

public static DataTable GetDataTableByProcedureName(String StoredProcedureName)
{
DataTable table = null;
using (connection)
{
if (StoredProcedureName.Contains("."))
command.CommandText = StoredProcedureName;
else
command.CommandText = PackageName + "." + StoredProcedureName;

command.Parameters.Add("RC1", OracleDbType.RefCursor, ParameterDirection.Output);

table = new DataTable(command.CommandText);
OracleDataReader reader = command.ExecuteReader();
table.Load(reader);

Disconnect();
}
return table;
}