Using OracleParameter in ExecuteStoreQuery Error Msg : " Value does not fall within the expected range."

Question

In order to prevent SQLinjection. I need to change my code from simple String to OracleParameter. Also I need to bypass LINQ because of its limitation in my WHERE CLAUSE as you can see below and last for better performance. By the way this code is working before I use OracleParameter.

I got this error message "Value does not fall within the expected range". What's wrong or missing on my code, specially on OracleParameter and ExecuteStoreQuery part?

I'm using the following:

1. VS 2010
2. Oracle 11gR2
3. C#
4. EntityFramework 4.3.1

---

Code(.cs)

public List<ProgramAssetCodeViewModel> GetAssetToList(long _channelID, DateTime _startDate, DateTime _endDate, DateTime _startTime, DateTime _endTime, string _programName, bool isActualAiring)
{
    List<ProgramAssetCodeViewModel> assetList = new List<ProgramAssetCodeViewModel>();

    try
    {
        string qry = @"SELECT D.CHANNEL_NAME CHANNEL_NAME, TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') AIRING_DT, A.PROG_NAME, C.PROG_BODY_ASSET_CD, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) START_TIME, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL',TO_DATE('01/01/2001' || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) END_TIME, ASCII(UPPER(SUBSTR(C.PROG_BODY_ASSET_CD, -1)))-64 PROG_BODY_NO FROM PROGRAM_MSTR A, PROG_SKED_DTL B, PROG_BODY_MSTR C, CHANNEL_MSTR D WHERE B.CHANNEL_ID = D.CHANNEL_ID AND A.PROGRAM_ID = B.PROGRAM_ID AND B.PROG_SKED_ID = C.PROG_SKED_ID(+) AND B.CHANNEL_ID = :CHANNEL_ID AND A.PROG_NAME LIKE ('%' || :PROG_NAME ||'%') AND A.PROG_NAME NOT LIKE 'BS%' AND A.PROG_NAME NOT LIKE 'B/S%' AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') >= TO_DATE(:START_DATE,'MM/DD/YYYY') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) >= TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI')) AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') <= TO_DATE(:END_DATE,'MM/DD/YYYY') AND ((TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI') < TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI')) OR (TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI') >= TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/02/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI')))) ORDER BY CHANNEL_NAME, AIRING_DT, START_TIME, END_TIME, PROG_BODY_NO";
        string qryActual = @"SELECT D.CHANNEL_NAME CHANNEL_NAME, TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') AIRING_DT, A.PROG_NAME, C.PROG_BODY_ASSET_CD, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B2.ACTUAL_START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) START_TIME, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL',TO_DATE('01/01/2001' || TO_CHAR( B2.ACTUAL_END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) END_TIME, ASCII(UPPER(SUBSTR(C.PROG_BODY_ASSET_CD, -1)))-64 PROG_BODY_NO FROM PROGRAM_MSTR A, PROG_SKED_DTL B, PROG_SKED_ACTUAL B2, PROG_BODY_MSTR C, CHANNEL_MSTR D WHERE B.CHANNEL_ID = D.CHANNEL_ID AND B.CHANNEL_ID = B2.CHANNEL_ID(+) AND B.PROGRAM_ID = B2.PROGRAM_ID(+) AND B.START_DT = B2.START_DT(+) AND B.START_TIME = B2.START_TIME(+) AND B.PROG_AIR_DT = B2.PROG_AIR_DT(+) AND A.PROGRAM_ID = B.PROGRAM_ID AND B.PROG_SKED_ID = C.PROG_SKED_ID(+) AND B.CHANNEL_ID = :CHANNEL_ID  AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') >= TO_DATE(:START_DATE,'MM/DD/YYYY') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B2.ACTUAL_START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) >= TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI'))  AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') <= TO_DATE(:END_DATE,'MM/DD/YYYY')   AND ((TO_DATE ('01/01/2001 ' || :ACTUAL_START_TIME, 'MM/DD/YYYY HH24:MI') < TO_DATE ('01/01/2001 ' || :ACTUAL_END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B2.ACTUAL_END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI')) OR (TO_DATE ('01/01/2001 ' || :ACTUAL_START_TIME, 'MM/DD/YYYY HH24:MI') >= TO_DATE ('01/01/2001 ' || :ACTUAL_END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B2.ACTUAL_END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/02/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI'))))  AND A.PROG_NAME LIKE ('%' || :PROG_NAME ||'%') AND A.PROG_NAME NOT LIKE 'BS%' AND A.PROG_NAME NOT LIKE 'B/S%' ORDER BY CHANNEL_NAME, AIRING_DT, START_TIME, END_TIME, PROG_BODY_NO";

        StringBuilder sb = isActualAiring ? sb = new StringBuilder(qryActual) : sb = new StringBuilder(qry);

        OracleParameter paramChannel = new OracleParameter() { ParameterName = "CHANNEL_ID", Value = _channelID.ToString(), OracleDbType = OracleDbType.Decimal, Size = 15, Direction = System.Data.ParameterDirection.Input };



        //sb.Replace(":CHANNEL_ID", _channelID.ToString());
        sb.Replace(":START_DATE", String.Format("'{0:MM/dd/yyyy}'", _startDate));
        sb.Replace(":END_DATE", String.Format("'{0:MM/dd/yyyy}'", _endDate));
        sb.Replace(":START_TIME", String.Format("'{0:HH:mm}'", _startTime));
        sb.Replace(":END_TIME", String.Format("'{0:HH:mm}'", _endTime));
        sb.Replace(":PROG_NAME", String.Format("'{0}'", _programName));

        using (var ctx = new FITSEntities())
        {
            foreach (var obj in ctx.GetObjectContext().ExecuteStoreQuery<ProgramAssetCodeWrapper>(sb.ToString(), new { paramChannel }))
            {
                assetList.Add(new ProgramAssetCodeViewModel
                {
                    IsChecked = false,
                    ChannelName = obj.CHANNEL_NAME,
                    AirDate = obj.AIRING_DT.Value,
                    ProgramName = obj.PROG_NAME,
                    AssetCode = obj.PROG_BODY_ASSET_CD,
                    StartTime = obj.START_TIME,
                    EndTime = obj.END_TIME,
                    ProgramBodyNo = obj.PROG_BODY_NO.HasValue ? (Int32)obj.PROG_BODY_NO.Value : 0
                });
            }
        }
    }
    catch
    {

    }

    return assetList;
}

---

SQL (qry)

SELECT D.CHANNEL_NAME CHANNEL_NAME,
  TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') AIRING_DT,
  A.PROG_NAME,
  C.PROG_BODY_ASSET_CD,
  FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) START_TIME,
  FN_TR_CONVERT_UTC_TIME ('TO_LOCAL',TO_DATE('01/01/2001'
  || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) END_TIME,
  ASCII(UPPER(SUBSTR(C.PROG_BODY_ASSET_CD, -1)))-64 PROG_BODY_NO
FROM PROGRAM_MSTR A,
  PROG_SKED_DTL B,
  PROG_BODY_MSTR C,
  CHANNEL_MSTR D
WHERE B.CHANNEL_ID = D.CHANNEL_ID
AND A.PROGRAM_ID   = B.PROGRAM_ID
AND B.PROG_SKED_ID = C.PROG_SKED_ID(+)
AND B.CHANNEL_ID   = :CHANNEL_ID
AND A.PROG_NAME LIKE ('%'
  || :PROG_NAME
  ||'%')
AND A.PROG_NAME NOT LIKE 'BS%'
AND A.PROG_NAME NOT LIKE 'B/S%'
AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') >= TO_DATE(:START_DATE,'MM/DD/YYYY')
AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) >= TO_DATE ('01/01/2001 '
  || :START_TIME, 'MM/DD/YYYY HH24:MI'))
AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') <= TO_DATE(:END_DATE,'MM/DD/YYYY')
AND ((TO_DATE ('01/01/2001 '
  || :START_TIME, 'MM/DD/YYYY HH24:MI') < TO_DATE ('01/01/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI')
AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/01/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI'))
OR (TO_DATE ('01/01/2001 '
  || :START_TIME, 'MM/DD/YYYY HH24:MI') >= TO_DATE ('01/01/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI')
AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/02/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI'))))
ORDER BY CHANNEL_NAME,
  AIRING_DT,
  START_TIME,
  END_TIME,
  PROG_BODY_NO

Answer 1

I got an example on usage of context.ExecuteStoreQuery<TElement> here.

And it seems like the second argument must be an array of parameters.

So this code :

ExecuteStoreQuery<ProgramAssetCodeWrapper>(sb.ToString(), new { paramChannel })

must be:

ExecuteStoreQuery<ProgramAssetCodeWrapper>(sb.ToString(), new OracleParameter[]{ paramChannel })

Answer 2

To accommodate multiple parameters, you can do like this:

OracleParameter[] queryParams = new OracleParameter[6];
queryParams[0] = new OracleParameter("START_DATE", OracleDbType.Date, 2, strStartDate, System.Data.ParameterDirection.Input);
queryParams[1] = new OracleParameter("END_DATE", OracleDbType.Date, 4, strEndDate, System.Data.ParameterDirection.Input);
queryParams[2] = new OracleParameter("START_TIME", OracleDbType.TimeStampLTZ, 2, strStartTime, System.Data.ParameterDirection.Input);
queryParams[3] = new OracleParameter("END_TIME", OracleDbType.TimeStampLTZ, 2, strEndTime, System.Data.ParameterDirection.Input);
queryParams[4] = new OracleParameter("PROG_NAME", OracleDbType.Varchar2, 50, strProgName, System.Data.ParameterDirection.Input);
queryParams[5] = new OracleParameter("CHANNEL_ID", OracleDbType.Int32, 50, strChannelID, System.Data.ParameterDirection.Input);

Updated: with proposed proper datatype.

And run the query like this:

var result = ctx.Database.SqlQuery<Object>(qry, queryParams);

Answer 3

If someone wants to add parameters dynamically, here's what I did:

List<OracleParameter> params = new List<OracleParameter>();
params.Add(new OracleParameter("ID", 123));

string query = "SELECT * FROM USERS WHERE ID = :ID";
UserDto = context.ExecuteStoreQuery<UserDto>(query, params.ToArray()).First();

Notice that the List<OracleParameter> is converted to Array by doing params.ToArray(), so initially you don't have to know the amount of parameters for the Array.