ORA-01483 and DateTime values

[object]

Hi,

I've found an old thread about ORA-01483 error ("invalid length for DATE or NUMBER"), but it seems that the error has been fixed long ago. We are moving our applications from stage to production machine, and on a new machine we received this error:

[OracleException (0x80004005): ORA-01483: ugyldig lengde på DATE- eller NUMBER-bindingsvariabelen]
Devart.Data.Oracle.ap.d() +543
Devart.Data.Oracle.y.m() +78
Devart.Data.Oracle.y.c() +2413
Devart.Data.Oracle.v.a(Int32 A_0, bf A_1) +2243
Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery) +3379
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery) +451
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +6
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
Devart.Data.Oracle.Entity.e.a(CommandBehavior A_0) +229
Devart.Data.Oracle.Entity.e.b(CommandBehavior A_0) +37
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479
System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +736
System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +149
System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator() +44
System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +315


The failed statement (generated from EF profiler) like like this:

SELECT "Project3".C1 AS C1,
"Project3".PROGRAMME_ID AS PROGRAMME_ID,
"Project3".PI_PROG_ID AS PI_PROG_ID,
"Project3".C2 AS C2,
"Project3".C3 AS C3,
"Project3".C4 AS C4,
"Project3".C5 AS C5,
"Project3".C6 AS C6,
"Project3".C7 AS C7,
"Project3".C8 AS C8,
"Project3".C9 AS C9,
"Project3".C10 AS C10,
"Project3".C11 AS C11,
"Project3".C12 AS C12,
"Project3".C13 AS C13,
"Project3".C14 AS C14,
"Project3".C15 AS C15,
"Project3".PUBLISHED_TIME AS PUBLISHED_TIME,
"Project3".PI_CHANNEL_ID AS PI_CHANNEL_ID,
"Project3".C16 AS C16,
"Project3".C17 AS C17,
"Project3".SEASON_ID AS SEASON_ID,
"Project3".SERIE_ID AS SERIE_ID,
"Project3".C18 AS C18
FROM (SELECT "Project1".PUBLISHED_TIME AS PUBLISHED_TIME,
"Project1".ACTUAL_START AS ACTUAL_START,
"Project1".PROGRAMME_ID AS PROGRAMME_ID,
"Project1".PI_PROG_ID AS PI_PROG_ID,
"Project1".SEASON_ID AS SEASON_ID,
"Project1".SERIE_ID AS SERIE_ID,
"Project1".PI_CHANNEL_ID AS PI_CHANNEL_ID,
1 AS C1,
CASE
WHEN "Project1".TITLE2 IS NULL THEN "Project1".TITLE3
ELSE "Project1".TITLE2
END AS C2,
CASE
WHEN "Project1".IMAGE_ID IS NULL THEN 0
ELSE "Project1".IMAGE_ID
END AS C3,
CASE
WHEN "Project1".IMAGE_ID IS NULL THEN N''
ELSE "Project1".EXTERNAL_ID
END AS C4,
CASE
WHEN "Project1".IMAGE_ID IS NULL THEN 0
ELSE "Project1".X_COORDINATE
END AS C5,
CASE
WHEN "Project1".IMAGE_ID IS NULL THEN 0
ELSE "Project1".Y_COORDINATE
END AS C6,
CASE
WHEN "Project1".IMAGE_ID IS NULL THEN 0
ELSE "Project1".RELATIVE_WIDTH
END AS C7,
CASE
WHEN "Project1".IMAGE_ID IS NULL THEN 0
ELSE "Project1".RELATIVE_HEIGHT
END AS C8,
CASE
WHEN "Project1".IMAGE_ID1 IS NULL THEN 0
ELSE "Project1".IMAGE_ID1
END AS C9,
CASE
WHEN "Project1".IMAGE_ID1 IS NULL THEN N''
ELSE "Project1".EXTERNAL_ID1
END AS C10,
CASE
WHEN "Project1".IMAGE_ID1 IS NULL THEN 0
ELSE "Project1".X_COORDINATE1
END AS C11,
CASE
WHEN "Project1".IMAGE_ID1 IS NULL THEN 0
ELSE "Project1".Y_COORDINATE1
END AS C12,
CASE
WHEN "Project1".IMAGE_ID1 IS NULL THEN 0
ELSE "Project1".RELATIVE_WIDTH1
END AS C13,
CASE
WHEN "Project1".IMAGE_ID1 IS NULL THEN 0
ELSE "Project1".RELATIVE_HEIGHT1
END AS C14,
"Project1".TRANSMISSION_DATE AS C15,
CASE
WHEN "Project1".OBJECT_ID IS NOT NULL THEN 1
WHEN "Project1".OBJECT_ID IS NULL THEN 0
END AS C16,
CASE
WHEN "Project1".OBJECT_ID1 IS NOT NULL THEN 1
WHEN "Project1".OBJECT_ID1 IS NULL THEN 0
END AS C17,
CASE
WHEN "Project1".TITLE IS NULL THEN "Project1".TITLE1
ELSE "Project1".TITLE
END AS C18
FROM (SELECT "Extent1".TRANSMISSION_DATE AS TRANSMISSION_DATE,
"Extent1".PUBLISHED_TIME AS PUBLISHED_TIME,
"Extent1".ACTUAL_START AS ACTUAL_START,
"Extent2".PROGRAMME_ID AS PROGRAMME_ID,
"Extent2".PI_PROG_ID AS PI_PROG_ID,
"Extent2".MEDIUM AS MEDIUM,
"Extent2".DELETED_IN_PI AS DELETED_IN_PI,
"Extent3".SEASON_ID AS SEASON_ID,
"Extent3".SERIE_ID AS SERIE_ID,
"Extent6".ON_DEMAND_CHANNEL AS ON_DEMAND_CHANNEL,
"Extent6".PI_CHANNEL_ID AS PI_CHANNEL_ID,
"Extent4".TITLE AS TITLE,
"Extent5".TITLE AS TITLE1,
"Extent7".TITLE AS TITLE2,
"Extent8".TITLE AS TITLE3,
"Extent9".IMAGE_ID AS IMAGE_ID,
"Extent9".EXTERNAL_ID AS EXTERNAL_ID,
"Extent9".RELATIVE_WIDTH AS RELATIVE_WIDTH,
"Extent9".RELATIVE_HEIGHT AS RELATIVE_HEIGHT,
"Extent9".X_COORDINATE AS X_COORDINATE,
"Extent9".Y_COORDINATE AS Y_COORDINATE,
"Extent10".IMAGE_ID AS IMAGE_ID1,
"Extent10".EXTERNAL_ID AS EXTERNAL_ID1,
"Extent10".RELATIVE_WIDTH AS RELATIVE_WIDTH1,
"Extent10".RELATIVE_HEIGHT AS RELATIVE_HEIGHT1,
"Extent10".X_COORDINATE AS X_COORDINATE1,
"Extent10".Y_COORDINATE AS Y_COORDINATE1,
"Join10".OBJECT_ID AS OBJECT_ID,
"Join12".OBJECT_ID AS OBJECT_ID1
FROM TRANSMISSIONS "Extent1"
INNER JOIN PROGRAMMES "Extent2"
ON "Extent1".PROGRAMME_ID = "Extent2".PROGRAMME_ID
INNER JOIN SEASONS "Extent3"
ON "Extent2".SEASON_ID = "Extent3".SEASON_ID
LEFT OUTER JOIN SERIES_TITLES "Extent4"
ON (("Extent3".SERIE_ID = "Extent4".SERIE_ID)
AND ("Extent4".TITLES_GROUP_TYPE = 'mainTitle' /* @p__linq__2 */))
AND ("Extent4".REFERENCE = N'ODA')
LEFT OUTER JOIN SERIES_TITLES "Extent5"
ON (("Extent3".SERIE_ID = "Extent5".SERIE_ID)
AND ("Extent5".TITLES_GROUP_TYPE = 'mainTitle' /* @p__linq__3 */))
AND ("Extent5".REFERENCE = N'PI')
INNER JOIN CHANNELS "Extent6"
ON "Extent1".CHANNEL_ID = "Extent6".CHANNEL_ID
LEFT OUTER JOIN PROGRAMMES_TITLES "Extent7"
ON ((("Extent2".PROGRAMME_ID = "Extent7".PROGRAMME_ID)
AND ("Extent7".TITLES_GROUP_TYPE = 'secondaryTitle' /* @p__linq__4 */))
AND ("Extent7".LABEL = 'onDemand' /* @p__linq__5 */))
AND ("Extent7".REFERENCE = N'ODA')
LEFT OUTER JOIN PROGRAMMES_TITLES "Extent8"
ON (("Extent2".PROGRAMME_ID = "Extent8".PROGRAMME_ID)
AND ("Extent8".TITLES_GROUP_TYPE = 'mainTitle' /* @p__linq__6 */))
AND ("Extent8".REFERENCE = N'PI')
LEFT OUTER JOIN PROGRAMMES_IMAGES "Extent9"
ON ("Extent2".PROGRAMME_ID = "Extent9".PROGRAMME_ID)
AND ("Extent9".LABEL = 'illustration' /* @p__linq__7 */)
LEFT OUTER JOIN SERIES_IMAGES "Extent10"
ON ("Extent3".SERIE_ID = "Extent10".SERIE_ID)
AND ("Extent10".LABEL = 'illustration' /* @p__linq__8 */)
LEFT OUTER JOIN (SELECT "Extent11".LIST_ID AS LIST_ID1,
"Extent11".OBJECT_ID AS OBJECT_ID,
"Extent11".OBJECT_TYPE AS OBJECT_TYPE,
"Extent11".SEQUENCE AS SEQUENCE,
"Extent11".METADATA_POSITION AS METADATA_POSITION,
"Extent11".IS_ACTIVE AS IS_ACTIVE,
"Extent11".PUBLISH_START_TIME AS PUBLISH_START_TIME,
"Extent11".PUBLISH_END_TIME AS PUBLISH_END_TIME,
"Extent11".TIME_INTERVAL_START AS TIME_INTERVAL_START,
"Extent11".TIME_INTERVAL_END AS TIME_INTERVAL_END,
"Extent11".CREATED AS CREATED1,
"Extent11".CHANGED AS CHANGED1,
"Extent11".START_INDEX_ID AS START_INDEX_ID,
"Extent11".LIST_OBJECT_MAP_ID AS LIST_OBJECT_MAP_ID,
"Extent12".LIST_ID AS LIST_ID2,
"Extent12".LIST_TYPE AS LIST_TYPE,
"Extent12".DISPLAY_TYPE AS DISPLAY_TYPE,
"Extent12".MEDIUM AS MEDIUM,
"Extent12".DESCRIPTION AS DESCRIPTION,
"Extent12".MAX_OBJECTS AS MAX_OBJECTS,
"Extent12".CREATED AS CREATED2,
"Extent12".CHANGED AS CHANGED2
FROM LISTS_OBJECTS_MAP "Extent11"
INNER JOIN LISTS "Extent12"
ON "Extent11".LIST_ID = "Extent12".LIST_ID) "Join10"
ON ("Extent2".PROGRAMME_ID = "Join10".OBJECT_ID)
AND ((LOWER("Join10".MEDIUM)) = (LOWER('TV' /* @p__linq__9 */)))
LEFT OUTER JOIN (SELECT "Extent13".LIST_ID AS LIST_ID3,
"Extent13".OBJECT_ID AS OBJECT_ID,
"Extent13".OBJECT_TYPE AS OBJECT_TYPE,
"Extent13".SEQUENCE AS SEQUENCE,
"Extent13".METADATA_POSITION AS METADATA_POSITION,
"Extent13".IS_ACTIVE AS IS_ACTIVE,
"Extent13".PUBLISH_START_TIME AS PUBLISH_START_TIME,
"Extent13".PUBLISH_END_TIME AS PUBLISH_END_TIME,
"Extent13".TIME_INTERVAL_START AS TIME_INTERVAL_START,
"Extent13".TIME_INTERVAL_END AS TIME_INTERVAL_END,
"Extent13".CREATED AS CREATED3,
"Extent13".CHANGED AS CHANGED3,
"Extent13".START_INDEX_ID AS START_INDEX_ID,
"Extent13".LIST_OBJECT_MAP_ID AS LIST_OBJECT_MAP_ID,
"Extent14".LIST_ID AS LIST_ID4,
"Extent14".LIST_TYPE AS LIST_TYPE,
"Extent14".DISPLAY_TYPE AS DISPLAY_TYPE,
"Extent14".MEDIUM AS MEDIUM,
"Extent14".DESCRIPTION AS DESCRIPTION,
"Extent14".MAX_OBJECTS AS MAX_OBJECTS,
"Extent14".CREATED AS CREATED4,
"Extent14".CHANGED AS CHANGED4
FROM LISTS_OBJECTS_MAP "Extent13"
INNER JOIN LISTS "Extent14"
ON "Extent13".LIST_ID = "Extent14".LIST_ID) "Join12"
ON (("Extent2".PROGRAMME_ID = "Join12".OBJECT_ID)
AND ((LOWER("Join12".MEDIUM)) = (LOWER('TV' /* @p__linq__10 */))))
AND ("Join12".LIST_TYPE = 'plug' /* @p__linq__11 */)
WHERE ("Extent1".TRANSMISSION_DATE >= '2011-11-03T00:00:00.00' /* @p__linq__0 */)
AND ("Extent1".TRANSMISSION_DATE 1))
AND (EXISTS (SELECT 1 AS C1
FROM RIGHTS "Extent15"
WHERE "Extent15".PROGRAMME_ID = "Project1".PROGRAMME_ID)))
AND ("Project1".ON_DEMAND_CHANNEL = 1)) "Project3"
ORDER BY "Project3".ACTUAL_START ASC

And the offending lines are the lines with date/time values, if I change the format, the statement executes fine.

Here's the change:
WHERE ("Extent1".TRANSMISSION_DATE >= '3-NOV-2011' /* @p__linq__0 */)
AND ("Extent1".TRANSMISSION_DATE < '6-NOV-2011' /* @p__linq__1 */)) "Project1"

Any ideas what this can be? We are using dotConnect with Entity Framework.


Here are some NLS settings:
SELECT * FROM nls_session_parameters where parameter like 'NLS_%';

NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RRRR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RRRR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY Kr.
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Obviously this don't match the query, but why is the statement generated so?

[object]

I did more tests and the problem does not seem to be caused by datetime columns. In brief: strange things happen when a query contains a string literal, e.g.

s == MyClass.SomeStringConstant

If I add ToLower() to both parts of the comparison, everything works as it should.

Please refer to this thread for detailed description:

http://www.devart.com/forums/viewtopic.php?t=22492

[Shalex]

Is ORA-01483 gone? If not, give us the following information:
1) a small test application with the corresponding DDL/DML script;
2) the NLS_DATE_FORMAT parameter of your database;
3) the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\%HomeName% > NLS_LANG setting of your Oracle client;
4) the regional settings of your machine: Control Panel > Reginal and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs.

[object]

The ORA-01483 goes away if we apply ToLower conversion to both sides of string comparison expressions that contain literal strings as I described in the other bug report. So apparently this is not date/time-related error but something about string conversion on a database with UTF8 setup.

So I suggest we focus on that issue (http://www.devart.com/forums/viewtopic.php?t=22492), I will collect more information that can better describe the situation.

[hendrysmith]

This is really great explanation.It contains a lot of valuable information.