I got a StorageException (ORA-01460) while trying to update string fields in an Oracle 10 database. I tried the same scenario in Sql Server and the in-memory database, no problems there. The error occurs only in very specific circumstances, but I suppose it has something to do with a query parameter that is too small. I made a little app with a very simple model to try some variations. It has a very simple model:

[Serializable]
[HierarchyRoot]
public class MyEntity : Entity
{
    [Field, Key]
    public int Id { get; private set; }

    [Field(Length = 100)]
    public string Text { get; set; }
}

The database is recreated (upgradeMode="Recreate") when the application starts. First, the app create a thousand entities:

using (var transactionScope = Transaction.Open())
{
    for (int i = 1; i <= 1000; i++)
    {
        // new entities, starting with Id == 1
        new MyEntity { Text = "Text" + i.ToString() }; 
    }
    transactionScope.Complete();
}

Then it tries to change the text field of the entities:

// Fails:
using (var transactionScope = Transaction.Open())
{
    for (int i = 1; i <= 10; i++)
    {
        var entity = Query.All<MyEntity>()
                          .Where(e => e.Text == "Text" + i.ToString()).Single();
        entity.Text = "Changed" + i.ToString();
    }
    transactionScope.Complete();
}

This fails because Oracle says that “the requested conversion is not implemented or unreasonable”. The exception details says why:

Error 'Unknown' while executing query 'BEGIN
UPDATE "BOUWGWF"."MYENTITY" SET "TEXT" = :p1_0 WHERE ("MYENTITY"."ID" = :p1_1);
OPEN :p0_c FOR SELECT "a"."ID", "a"."TypeId", "a"."TEXT" FROM (SELECT "b"."ID", 107 AS "TypeId", "b"."TEXT", ROW_NUMBER() OVER(ORDER BY "b"."ID" ASC) AS "RowNumber0" FROM "BOUWGWF"."MYENTITY" "b" WHERE ("b"."TEXT" = :p0_0)) "a" WHERE ("a"."RowNumber0" <= 2);
END; 
[p1_0='Changed9';p1_1='9';p0_0='Text10';p0_c='']'. 
Original message: ORA-01460: Unimplemented or unreasonable conversion requested.

Interestingly, the error occurs for i == 10, and at that moment the length of p0_0 changes. .

Many variations of this scenario do not fail. The number of digits of the lowerbound of the loop has to be smaller than the number of digits of the upperbound.
So “for (int i = 1; i <= 9; i++)”, “for (int i = 10; i <= 99; i++)” and “for (int i = 100; i <= 999; i++)” all work correctly.

There’s also no problem if the loop counts down instead of up: “for (int i = 1000; i >= 1; i--)”

Likewise, if we don’t filter on the Text-property but on the Id, it all works well:

//Succeeds:
using (var transactionScope = Transaction.Open())
{
    for (int i = 1; i <= 1000; i++)
    {
        var entity = Query.All<MyEntity>().Where(e => e.Id == i).Single();
        entity.Text = "Changed" + i.ToString();
    }
    transactionScope.Complete();
}

Finally, unwinding the loop like this gives an exception (as expected):

// Fails
using (var transactionScope = Transaction.Open())
{
        int i = 7; 
        string s1 = "Text" + i.ToString(); string s2 = "Changed" + i.ToString();
        Query.All<MyEntity>().Where(e => e.Text == s1).Single().Text = s2;

        i = 8; 
        s1 = "Text" + i.ToString(); s2 = "Changed" + i.ToString();
        Query.All<MyEntity>().Where(e => e.Text == s1).Single().Text = s2;

        i = 9; 
        s1 = "Text" + i.ToString(); s2 = "Changed" + i.ToString();
        Query.All<MyEntity>().Where(e => e.Text == s1).Single().Text = s2;

        i = 10; 
        s1 = "Text" + i.ToString(); s2 = "Changed" + i.ToString();
        Query.All<MyEntity>().Where(e => e.Text == s1).Single().Text = s2;

        transactionScope.Complete();
}

But if we replace i, s1 and s2 by constants, it suddenly works:

// Succeeds
using (var transactionScope = Transaction.Open())
{
    Query.All<MyEntity>().Where(e => e.Text == "Text7").Single().Text = "Changed7";
    Query.All<MyEntity>().Where(e => e.Text == "Text8").Single().Text = "Changed8";
    Query.All<MyEntity>().Where(e => e.Text == "Text9").Single().Text = "Changed9";
    Query.All<MyEntity>().Where(e => e.Text == "Text10").Single().Text = "Changed10";

    transactionScope.Complete();
}

So, all in all a pretty obscure bug.

asked Nov 02 '10 at 09:29

JJLuursema's gravatar image

JJLuursema
15112

edited Nov 06 '10 at 05:36

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412


3 Answers:

See http://forums.oracle.com/forums/thread.jspa?threadID=507725

"This bug turned out to be a database issue. At this point, the Oracle dev team has put together a fix, but the fix hasn't been merged into a release yet. If you check with Oracle Support in a week, they'll probably know by then which database release will include this fix."

answered Nov 06 '10 at 05:29

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

Thank you for the investigation you've made. That's really weird behavior.

Are there anything that could be done by us to get rid of this or at least to minimize the probability of the case?

answered Nov 03 '10 at 05:29

Dmitri%20Maximov's gravatar image

Dmitri Maximov
22111211

Thanks for the quick response. I'll ask the Oracle guys here if they applied all the latest patches. Maybe not...

answered Nov 08 '10 at 03:48

JJLuursema's gravatar image

JJLuursema
15112

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA