EntityFramework with Oracle Managed Access DateTime issue

Question

I have stored procedure to perform search based on the input parameters.

-- Search clients by specified input parameters.
create or replace procedure p_get_searchclients(i_iin             in  number default null,
                                            i_full_name       in  varchar default null,
                                            i_created_at_from in  timestamp default null,
                                            i_created_at_to   in  timestamp default null,
                                            result_cursor     out sys_refcursor) as
query_str varchar(200);
no_params_value exception;
begin
-- Check input parameters.
if i_iin is null and i_full_name is null and i_created_at_from is null and i_created_at_to is null
then
  raise no_params_value;
end if;

-- Base query string.
query_str := 'select * from t_client where 1=1 ';

-- Add search by IIN column to the query.
if i_iin is not null
then
  query_str := query_str || 'and iin like ''%' || i_iin || '%'' ';
end if;

-- Add search by FULL_NAME column to the query.
if i_full_name is not null
then
  query_str := query_str || 'and lower(full_name) like ''%' || lower(i_full_name) || '%'' ';
end if;

-- Add search range by the CREATED_AT column to the query.
if i_created_at_from is not null and i_created_at_to is not null
then
  query_str :=
  query_str || 'and created_at >= ' || i_created_at_from || ' and created_at <= ' || i_created_at_to || ' ';
elsif i_created_at_from is not null and i_created_at_to is null
  then
    query_str := query_str || 'and created_at >= ' || i_created_at_from || ' ';
elsif i_created_at_from is null and i_created_at_to is not null
  then
    query_str := query_str || 'and created_at <= ' || i_created_at_to;
end if;

-- Execute statement.
open result_cursor for query_str;

exception
when no_params_value
then
  raise_application_error(-20100, 'Необходимо указать как минимум один параметр для поиска.');
when others
then
  rollback;
end p_get_searchclients;

In my application I'm using EntityFramework 5.0 and "EF Designer from Database" approach. Auto-generated code for my stored procedure in C# looks like this:

        public virtual ObjectResult<P_GET_SEARCHCLIENTS_Result> P_GET_SEARCHCLIENTS(Nullable<decimal> i_IIN, string i_FULL_NAME, Nullable<System.DateTime> i_CREATED_AT_FROM, Nullable<System.DateTime> i_CREATED_AT_TO)
    {
        var i_IINParameter = i_IIN.HasValue ?
            new ObjectParameter("I_IIN", i_IIN) :
            new ObjectParameter("I_IIN", typeof(decimal));

        var i_FULL_NAMEParameter = i_FULL_NAME != null ?
            new ObjectParameter("I_FULL_NAME", i_FULL_NAME) :
            new ObjectParameter("I_FULL_NAME", typeof(string));

        var i_CREATED_AT_FROMParameter = i_CREATED_AT_FROM.HasValue ?
            new ObjectParameter("I_CREATED_AT_FROM", i_CREATED_AT_FROM) :
            new ObjectParameter("I_CREATED_AT_FROM", typeof(System.DateTime));

        var i_CREATED_AT_TOParameter = i_CREATED_AT_TO.HasValue ?
            new ObjectParameter("I_CREATED_AT_TO", i_CREATED_AT_TO) :
            new ObjectParameter("I_CREATED_AT_TO", typeof(System.DateTime));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<P_GET_SEARCHCLIENTS_Result>("P_GET_SEARCHCLIENTS", i_IINParameter, i_FULL_NAMEParameter, i_CREATED_AT_FROMParameter, i_CREATED_AT_TOParameter);
    }

As you can see, I have created_at column of type timestamp in the DB, which is responsible for storing date of creation of a record. So, in my application, I form DateTime parameters (from timestmap to DateTime convertion), which should be sent to the SP.

DateTime? i_createdAtFrom = null;
DateTime? i_createdAtTo = null;
if (long.TryParse(createdAtFrom, out long parsedFromDate))
    i_createdAtFrom = DateTimeOffset.FromUnixTimeSeconds(parsedFromDate).LocalDateTime;
if (long.TryParse(createdAtTo, out long parsedToDate))
    i_createdAtTo = DateTimeOffset.FromUnixTimeSeconds(parsedToDate).LocalDateTime;

The procedure works as excepted without any errors, but when I send any of the createdAtFrom or createdAtTo parameters, the DB throws the following exception.

"ORA-00604: error occurred at recursive SQL level 1\nORA-01003: no statement parsed"

I think this is somehow connected with the process of convertion C# DateTime type to the Oracle timestamp, but I couldn't debug it on my own.

Any ideas?

My configuration:
Visual Studio 2017
EntityFramework 5.0
Oracle client 11g 32bit

Answer 1

You should use prepared statements and bind parameters. In order to handle NULL values you could do it like this:

-- Base query string.
query_str := 'select * from t_client where ';

-- Add search by IIN column to the query.
if i_iin is not null then
  query_str := query_str || 'iin like :iin ';
else
  query_str := query_str || ':iin IS NULL ';
end if;

-- Add search by FULL_NAME column to the query.
if i_full_name is not null then
  query_str := query_str || 'and lower(full_name) like :i_full_name ';
else
  query_str := query_str || 'and :i_full_name IS NULL ';
end if;

-- Add search range by the CREATED_AT column to the query.
if i_created_at_from is not null and i_created_at_to is not null then
  query_str :=
  query_str || 'and created_at >= :i_created_at_from and created_at <= :i_created_at_to ';
elsif i_created_at_from is not null and i_created_at_to is null then
    query_str := query_str || 'and created_at >= :i_created_at_from AND :i_created_at_to IS NULL ';
elsif i_created_at_from is null and i_created_at_to is not null then
    query_str := query_str || 'and created_at <= :i_created_at_to AND :i_created_at_from IS NULL ';
else
    query_str := query_str || 'and :i_created_at_from IS NULL AND :i_created_at_to IS NULL ';
end if;

-- Execute statement.
open result_cursor 
   for query_str 
   USING '%'||iin||'%', '%'||LOWER(i_full_name)||'%', i_created_at_from, i_created_at_to;