Strange errors with OracleLoader
Strange errors with OracleLoader
Hi,
The OraclelLoader class is giving me some very strange errors. The error message is completely misleading. I believe the problem is somewhere else in the driver while the error reported is wrong.
In my case i have the followng lines of code (cut down version)
//loader.SetValue("GEOIP_CITY_NAME", entry.Geoip.NullSafeGet(a => a.CityName));
loader.SetValue("GEOIP_COUNTRY_CODE2", entry.Geoip.NullSafeGet(a => a.CountryCode2));
loader.SetValue("GEOIP_COUNTRY_NAME", entry.Geoip.NullSafeGet(a => a.CountryName));
loader.SetValue("GEOIP_LATITUDE", entry.Geoip.NullSafeGet(a => a.Latitude));
loader.SetValue("GEOIP_LOCATION", null);
loader.SetValue("GEOIP_LONGITUDE", entry.Geoip.NullSafeGet(a => a.Longitude));
//loader.SetValue("GEOIP_REAL_REGION_NAME", entry.Geoip.NullSafeGet(a => a.RealRegionName));
If I add back the commented out code, it breaks. If I leave it commented it works.
Error I get
Additional information: ORA-01461: can bind a LONG value only for insert into a LONG column
The GEOIP_CITY_NAME is 64 byte varchar2 and GEOIP_REAL_REGION_NAME is 128 byte varchar2. The values I am passing is no more then 20 characters. Please help! There is a definite bug in your code
The OraclelLoader class is giving me some very strange errors. The error message is completely misleading. I believe the problem is somewhere else in the driver while the error reported is wrong.
In my case i have the followng lines of code (cut down version)
//loader.SetValue("GEOIP_CITY_NAME", entry.Geoip.NullSafeGet(a => a.CityName));
loader.SetValue("GEOIP_COUNTRY_CODE2", entry.Geoip.NullSafeGet(a => a.CountryCode2));
loader.SetValue("GEOIP_COUNTRY_NAME", entry.Geoip.NullSafeGet(a => a.CountryName));
loader.SetValue("GEOIP_LATITUDE", entry.Geoip.NullSafeGet(a => a.Latitude));
loader.SetValue("GEOIP_LOCATION", null);
loader.SetValue("GEOIP_LONGITUDE", entry.Geoip.NullSafeGet(a => a.Longitude));
//loader.SetValue("GEOIP_REAL_REGION_NAME", entry.Geoip.NullSafeGet(a => a.RealRegionName));
If I add back the commented out code, it breaks. If I leave it commented it works.
Error I get
Additional information: ORA-01461: can bind a LONG value only for insert into a LONG column
The GEOIP_CITY_NAME is 64 byte varchar2 and GEOIP_REAL_REGION_NAME is 128 byte varchar2. The values I am passing is no more then 20 characters. Please help! There is a definite bug in your code
Re: Strange errors with OracleLoader
Ok, I have further details. The problem happen in the following function when both
QUERY_NETWORKIDLIST and QUERY_RESULT are null. The columns that are inserted get shifted to right by 1 column place and i start seeing errors.
QUERY_NETWORKIDLIST and QUERY_RESULT are null. The columns that are inserted get shifted to right by 1 column place and i start seeing errors.
Code: Select all
public void WriteToStorage(IList<ApacheLog> logEntries)
{
using (var connection = new OracleConnection(_connectionString))
{
connection.Open();
using (var loader = new OracleLoader(string.Format("{0}.EVENT", _trackingSchemaName), connection))
{
loader.Error += new OracleLoaderErrorEventHandler(loader_Error);
loader.CreateColumns();
loader.Open();
foreach (var entry in logEntries)
{
loader.SetValue("ID", entry.Id);
loader.SetValue("USER_AGENT", entry.UseragentString.TruncateLongString(500));
loader.SetValue("CLIENTIP", entry.Clientip);
loader.SetValue("TRUECLIENTIP", entry.Trueclientip);
loader.SetValue("TRACKING_SERVER", entry.Host);
loader.SetValue("ITEMSYSTEM", entry.ItemSystem);
loader.SetValue("ITEMTYPE", entry.Itemtype);
loader.SetValue("LOGFILE_PATH", entry.Path);
loader.SetValue("DATE_CREATED", entry.TimestampUTC.ToLocalTime());
if (entry.Query != null)
{
loader.SetValue("ITEMNETWORKID", entry.Query.NetworkId);
loader.SetValue("QUERY_DEVICETYPE", entry.Query.DeviceType);
loader.SetValue("QUERY_DOMAINNAME", entry.Query.DomainName);
if (entry.Query.EventTypes != null)
{
if (entry.Query.EventTypes.Count == 1)
{
loader.SetValue("QUERY_EVENTTYPE", entry.Query.EventTypes[0]);
}
else
{
}
}
loader.SetValue("QUERY_ITEM", entry.Query.Item);
loader.SetValue("QUERY_MEDIATYPE", entry.Query.MediaType);
loader.SetValue("QUERY_NETWORKIDLIST", entry.Query.NetworkIdList);
loader.SetValue("QUERY_RESULT", entry.Query.Result);
loader.SetValue("QUERY_SEARCHITEMSPERPAGE", entry.Query.SearchItemsPerPage);
loader.SetValue("QUERY_SEARCHRESULTCOUNT", entry.Query.SearchResultCount);
if (!string.IsNullOrEmpty(entry.Query.SearchTS))
{
var timestamp = SafeParseTimestamp(entry.Query.SearchTS);
if (timestamp.HasValue)
{
loader.SetValue("QUERY_SEARCHTS", timestamp);
}
else
{
Console.WriteLine("Count not parse timestamp");
}
}
else
{
loader.SetValue("QUERY_SEARCHTS", null);
}
loader.SetValue("QUERY_SEQ", entry.Query.Seq);
loader.SetValue("QUERY_SERVERNAME", entry.Query.ServerName);
loader.SetValue("QUERY_SESSIONID", entry.Query.SessionId);
loader.SetValue("QUERY_USERID", entry.Query.UserId);
if (!string.IsNullOrEmpty(entry.Query.SaleId))
{
loader.SetValue("QUERY_SALE_ID", entry.Query.SaleId.TruncateLongString(40));
}
loader.SetValue("QUERY_VISITORID", entry.Query.VisitorId);
}
loader.SetValue("REFERRER_DOMAIN", entry.ReferrerDomain);
loader.SetValue("RESPONSE", entry.Response);
loader.SetValue("EVENT_TS", entry.TimestampUTC.ToLocalTime());
if (entry.Geoip != null)
{
loader.SetValue("GEOIP_CITY_NAME", entry.Geoip.CityName.TruncateLongString(8));
//loader.SetValue("GEOIP_COUNTRY_CODE2", entry.Geoip.CountryCode2);
loader.SetValue("GEOIP_COUNTRY_NAME", entry.Geoip.CountryName);
loader.SetValue("GEOIP_LATITUDE", entry.Geoip.Latitude);
//loader.SetValue("GEOIP_LOCATION", entry.Geoip.Location); // This is null
loader.SetValue("GEOIP_LONGITUDE", entry.Geoip.Longitude);
//loader.SetValue("GEOIP_REAL_REGION_NAME", entry.Geoip.RealRegionName.TruncateLongString(3));
}
loader.SetValue("VERB", entry.Verb.TruncateLongString(3));
//loader.SetValue("SPOT_ID", entry.TimestampUTC); // This should be null
//loader.SetValue("REDBOOK_CODE_LEGACY") = null); // This should be null
loader.SetValue("REDBOOK_CODE", entry.RedbookCode);
if (entry.Useragent != null)
{
loader.SetValue("UA_NAME", entry.Useragent.Name);
loader.SetValue("UA_VER_MINOR", entry.Useragent.Minor);
loader.SetValue("UA_VER_MAJOR", entry.Useragent.Major);
loader.SetValue("UA_PATCH", entry.Useragent.Patch);
loader.SetValue("UA_BUILD", entry.Useragent.Build);
loader.SetValue("UA_OS", entry.Useragent.Os);
loader.SetValue("UA_OS_NAME", entry.Useragent.OsName);
loader.SetValue("UA_OS_VER_MAJOR", entry.Useragent.OsMajor);
loader.SetValue("UA_OS_VER_MINOR", entry.Useragent.OsMinor);
loader.SetValue("UA_DEVICE", entry.Useragent.Device.NullSafeGet(a => a.TruncateLongString(100)));
}
if (entry.ReferrerMap != null)
{
loader.SetValue("REFERRER_DRIVER", entry.ReferrerMap.WebTrendsDriverParam);
}
loader.NextRow();
}
loader.Close();
connection.Close();
}
}
}
Re: Strange errors with OracleLoader
Some more info. By process of elimination i know that if I change the following line of code
from
to
it all works fine. Very very strange bug. Please help for a proper solution as I cannot deploy this hack
from
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result);
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result ?? "-");
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Strange errors with OracleLoader
Please create and send us a complete test sample with corresponding DDL/DML scripts for reproducing the issue.
Re: Strange errors with OracleLoader
Done, please get back ASAP
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Strange errors with OracleLoader
Thank you for the test project. However, we could not reproduce the issue in our environment. Your sample runs successfully even if we replace:
with
Please tell us what should we change in your sample to reproduce the issue. Please also tell us the version of your Oracle server.
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result ?? "-");
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result);
Re: Strange errors with OracleLoader
Do you get a null in the database for the QUERY_RESULT field? This is quite strange as I can reproduce the error on my end
Thanks
Varun
Thanks
Varun
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Strange errors with OracleLoader
Yes, we have null values in the QUERY_RESULT column.varunb wrote:Do you get a null in the database for the QUERY_RESULT field?
Please provide us Oracle server version and dotConnect for Oracle version.
Re: Strange errors with OracleLoader
Devart version 5.0.1256.0
Oracle version: 11g
Oracle version: 11g
Re: Strange errors with OracleLoader
HI Guys,
Any update on this?
Thanks
Any update on this?
Thanks
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Strange errors with OracleLoader
Sorry for the delay. We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Re: Strange errors with OracleLoader
Guys,
Any update? It's been open for a while now and we would really like to get it resolved
Thanks
Any update? It's been open for a while now and we would really like to get it resolved
Thanks
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Strange errors with OracleLoader
We are still investigating the issue. We will post here when we get any results, but we can't tell any timeframe at the moment.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Strange errors with OracleLoader
We have fixed the bug with inserting null values via OracleLoader in the Direct mode to a UTF8 server. We will post here when the corresponding build of dotConnect for Oracle is available for download.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Strange errors with OracleLoader
New build of dotConnect for Oracle 9.2.205 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34950
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34950