Here's an interesting issue I ran into:

When I tried using the AddDays function with Oracle, I got the error ORA-01036: illegal variable name/number. Apparently, Oracle doesn't like seeing substitution variables inside single quotes, which is how the AddDays/Hours/Minutes/etc. functions are all mapped. This was in LLBLGen 3.1, but I didn't see anything in the change logs about a fix for this in later versions. I'm running Oracle server Standard One 11.2.0.4.0, and Oracle.DataAccess.Dll 4.112.3.0.

To work around this, I put in replacement function mappings for my LinqMetaData initializer:

var _FixDateFunctionStore = new FunctionMappingStore();
 _FixDateFunctionStore.Add(new FunctionMapping(typeof(DateTime), "AddDays", 1, "({0} + NUMTODSINTERVAL({1},'DAY'))"));
 _FixDateFunctionStore.Add(new FunctionMapping(typeof(DateTime), "AddHours", 1, "({0} + NUMTODSINTERVAL({1},'HOUR'))"));
 _FixDateFunctionStore.Add(new FunctionMapping(typeof(DateTime), "AddMinutes", 1, "({0} + NUMTODSINTERVAL({1},'MINUTE'))"));
 _FixDateFunctionStore.Add(new FunctionMapping(typeof(DateTime), "AddSeconds", 1, "({0} + NUMTODSINTERVAL({1},'SECOND'))"));
_FixDateFunctionStore.Add(new FunctionMapping(typeof(DateTime), "AddMonths", 1, "({0} + NUMTOYMINTERVAL({1},'MONTH'))"));
 _FixDateFunctionStore.Add(new FunctionMapping(typeof(DateTime), "AddYears", 1, "({0} + NUMTOYMINTERVAL({1},'YEAR'))"));

The {1} substitution variables are no longer in quotes, and everything works fine. The NUMTODSINTERVAL and NUMTOYMINTERVAL functions were added back in Oracle 8i, so they _should _be safe for general use.

Hi AHenry,

• What is the LLBLGen version and runtime library version that you are using?
• Did this work before?
• What is the generated SQL for the failed query?
• Also please post the exact exception message and full stack trace.

Hi Daelmo, This is the first time that I have tried the AddHours function in LINQ. I suspect that if it does ever work, it is with a different version of Oracle than what I am using. Here is the exception that came up. I don't have specific code to reproduce the issue since this came out of a dynamically generated query.

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code
  HResult=-2146232832
  Message=An exception was caught during the execution of a retrieval query: ORA-01036: illegal variable name/number. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
  RuntimeBuild=03112011
  RuntimeVersion=3.1.0.0
  QueryExecuted=
    Query: SELECT * FROM (SELECT "LPA_L1"."BULK" AS "Bulk", "LPA_L1"."BYPASS" AS "Bypass", "LPA_L1"."CAPTION" AS "Caption", "LPA_L1"."CLIENT_SYSTEM" AS "ClientSystem", "LPA_L1"."COMMENTS" AS "Comments", "LPA_L1"."COUPON" AS "Coupon", "LPA_L1"."CREDIT_FLAG" AS "CreditFlag", "LPA_L1"."DBROWNUM" AS "Dbrownum", "LPA_L1"."DESCRIPTION" AS "Description", "LPA_L1"."DESTINATION_LOCATION_ID" AS "DestinationLocationId", "LPA_L1"."EXPECTED_ARRIVAL_DATE" AS "ExpectedArrivalDate", "LPA_L1"."ISSUE_DATE" AS "IssueDate", "LPA_L1"."ISSUE_ID" AS "IssueId", "LPA_L1"."ITEM_CODE" AS "ItemCode", "LPA_L1"."ITEM_GROUP_ID" AS "ItemGroupId", "LPA_L1"."ITEM_ID" AS "ItemId", "LPA_L1"."ITEM_MASTER_DESCRIPTION" AS "ItemMasterDescription", "LPA_L1"."ITEM_MASTER_ID" AS "ItemMasterId", "LPA_L1"."ITEM_TYPE_CODE" AS "ItemTypeCode", "LPA_L1"."ITEM_TYPE_ID" AS "ItemTypeId", "LPA_L1"."LAST_RECEIPT_ID" AS "LastReceiptId", "LPA_L1"."LENGTH" AS "Length", "LPA_L1"."MATERIAL_ORDER_ID" AS "MaterialOrderId", "LPA_L1"."ORDER_QUANTITY" AS "OrderQuantity", "LPA_L1"."PUBLICATION_CODE" AS "PublicationCode", "LPA_L1"."PUBLICATION_GROUP_CODE" AS "PublicationGroupCode", "LPA_L1"."PUBLICATION_GROUP_ID" AS "PublicationGroupId", "LPA_L1"."PUBLICATION_ID" AS "PublicationId", "LPA_L1"."READABLE_PAGES" AS "ReadablePages", "LPA_L1"."RECEIPT_DATE" AS "ReceiptDate", "LPA_L1"."RECEIPT_STATUS" AS "ReceiptStatus", "LPA_L1"."RECONCILED_MO_COUNT" AS "ReconciledMoCount", "LPA_L1"."RECORD_TYPE" AS "RecordType", "LPA_L1"."REFERENCE" AS "Reference", "LPA_L1"."SALES_REP" AS "SalesRep", "LPA_L1"."SPECIAL_INSTRUCTIONS" AS "SpecialInstructions", "LPA_L1"."THICKNESS" AS "Thickness", "LPA_L1"."TOTAL_GROSS_WEIGHT" AS "TotalGrossWeight", "LPA_L1"."TOTAL_RECEIVED" AS "TotalReceived", "LPA_L1"."TOTAL_UNITS_RECEIVED" AS "TotalUnitsReceived", "LPA_L1"."UPDATE_SOURCE" AS "UpdateSource", "LPA_L1"."WAREHOUSE_CODE" AS "WarehouseCode", "LPA_L1"."WAREHOUSE_ID" AS "WarehouseId", "LPA_L1"."WEIGHT" AS "Weight", "LPA_L1"."WEIGHT_MODE_CODE" AS "WeightModeCode", "LPA_L1"."WIDTH" AS "Width" FROM "V_RECEIVING_SEARCH" "LPA_L1" WHERE ( ( ( ( ( ( ( "LPA_L1"."PUBLICATION_ID" = :p1) AND ( (TRUNC("LPA_L1"."RECEIPT_DATE") + INTERVAL ':p5' HOUR) = :p3)) AND ( ( "LPA_L1"."WAREHOUSE_ID" IS NULL) OR ( "LPA_L1"."WAREHOUSE_ID" = :p6))))))) ORDER BY "LPA_L1"."ITEM_CODE" ASC) WHERE rownum <= 1000
    Parameter: :p1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 24.
    Parameter: :p5 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.
    Parameter: :p3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/5/2014 12:00:00 AM.
    Parameter: :p6 : Decimal. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.

  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteEntityProjection(QueryExpression toExecute)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()
       at System.ServiceModel.DomainServices.Server.DomainService.Enumerate[T](IEnumerable enumerable, Int32 estimatedResultCount)
       at System.ServiceModel.DomainServices.Server.DomainService.Query(QueryDescription queryDescription, IEnumerable`1& validationErrors, Int32& totalCount)
       at MMUS.SAMTracks.RIA.Services.SAMTracksDataService.Query(QueryDescription queryDescription, IEnumerable`1& validationErrors, Int32& totalCount) in c:\Projects\SAMTracks11\Source\SAMTracks.RIA.Services\Services\SAMTracksDataService.cs:line 149
       at System.ServiceModel.DomainServices.Hosting.QueryProcessor.Process[TEntity](DomainService domainService, DomainOperationEntry queryOperation, Object[] parameters, ServiceQuery serviceQuery, IEnumerable`1& validationErrors, Int32& totalCount)
       at System.ServiceModel.DomainServices.Hosting.QueryOperationBehavior`1.QueryOperationInvoker.InvokeCore(Object instance, Object[] inputs, Object[]& outputs)
  InnerException: Oracle.DataAccess.Client.OracleException
       HResult=-2147467259
       Message=ORA-01036: illegal variable name/number
       Source=Oracle Data Provider for .NET
       ErrorCode=-2147467259
       DataSource=MMMS462-8DB5:1521/dev11
       Number=1036
       Procedure=""
       StackTrace:
            at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
            at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
            at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
            at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       InnerException: 

The important part is the

INTERVAL ':p5' HOUR

part of the SQL which triggers the ORA-01036 error.

daelmo wrote:

What is the LLBLGen version and runtime library version that you are using?

Walaa wrote:

daelmo wrote:

What is the LLBLGen version and runtime library version that you are using?

AHenry wrote:

RuntimeBuild=03112011 RuntimeVersion=3.1.0.0

Bug is still present in 4.2.

We'll look into it. It's indeed interesting how this ever ended up in the code and that no-one ran into this since 2.6 when they were introduced!

(edit) INTERVAL <param> doesn't work...:

WHERE ( ( ("LPA_L1"."HIREDATE" + INTERVAL :p4 DAY) = :p2))

gives ORA-00920: invalid relational operator, while p2 is a datetime / date value. Looking at the docs this is logical, as it seems INTERVAL <value> <operand> is seen as a literal, so you can't specify a parameter there.

We'll go with the functions you provided which indeed accept a parameter.

Fixed in v3.1 and up. I've attached the fixed v3.1 DQE for Oracle ODP.NET. Thanks for reporting this issue!