Page 1 of 1
Strange errors with OracleLoader
Posted: Tue 15 Sep 2015 00:16
by varunb
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
Re: Strange errors with OracleLoader
Posted: Tue 15 Sep 2015 02:05
by varunb
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.
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
Posted: Tue 15 Sep 2015 02:22
by varunb
Some more info. By process of elimination i know that if I change the following line of code
from
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result);
to
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result ?? "-");
it all works fine. Very very strange bug. Please help for a proper solution as I cannot deploy this hack
Re: Strange errors with OracleLoader
Posted: Tue 15 Sep 2015 15:56
by Pinturiccio
Please create and
send us a complete test sample with corresponding DDL/DML scripts for reproducing the issue.
Re: Strange errors with OracleLoader
Posted: Wed 16 Sep 2015 00:24
by varunb
Done, please get back ASAP
Re: Strange errors with OracleLoader
Posted: Fri 18 Sep 2015 11:30
by Pinturiccio
Thank you for the test project. However, we could not reproduce the issue in our environment. Your sample runs successfully even if we replace:
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result ?? "-");
with
Code: Select all
loader.SetValue("QUERY_RESULT", entry.Query.Result);
Please tell us what should we change in your sample to reproduce the issue. Please also tell us the version of your Oracle server.
Re: Strange errors with OracleLoader
Posted: Tue 22 Sep 2015 02:28
by varunb
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
Re: Strange errors with OracleLoader
Posted: Wed 23 Sep 2015 13:20
by Pinturiccio
varunb wrote:Do you get a null in the database for the QUERY_RESULT field?
Yes, we have null values in the QUERY_RESULT column.
Please provide us Oracle server version and dotConnect for Oracle version.
Re: Strange errors with OracleLoader
Posted: Thu 24 Sep 2015 04:42
by varunb
Devart version 5.0.1256.0
Oracle version: 11g
Re: Strange errors with OracleLoader
Posted: Tue 29 Sep 2015 04:19
by varunb
HI Guys,
Any update on this?
Thanks
Re: Strange errors with OracleLoader
Posted: Tue 29 Sep 2015 07:24
by Pinturiccio
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
Posted: Wed 21 Oct 2015 23:25
by varunb
Guys,
Any update? It's been open for a while now and we would really like to get it resolved
Thanks
Re: Strange errors with OracleLoader
Posted: Fri 23 Oct 2015 14:50
by Pinturiccio
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.
Re: Strange errors with OracleLoader
Posted: Wed 08 Feb 2017 15:01
by Pinturiccio
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.
Re: Strange errors with OracleLoader
Posted: Thu 09 Feb 2017 17:19
by Pinturiccio
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