This discussion is archived
3 Replies Latest reply: Nov 1, 2012 3:13 PM by Mark Williams-Oracle RSS

Instant Client+ORA-01460: unimplemented or unreasonable conversion requeste

870565 Newbie
Currently Being Moderated
Hi,

I am developing a C#/.NET 4.0 application that needs to retrieve data from an Oracle database 10g (10.2.0.4.0). We are using the instant client 11.2.

I have a very simple query that looks like that:

select distinct
round(s.field1, 5) as Field1,
p.field2 as Field2
from
tableA@dblink a
JOIN tableB@dblink b on a.id = b.d
where
a.filter1= :filter1
and a.filter2 = :filter2

From times to times (completely random), I have the following exception:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-02063: preceding line from dblink

If I run the query in Toad directly then it works fine. So I am guessing the instant client must have a role in it?

To solve it, I can either wait (5min to 1h) or I can simply change my query by adding/removing a space somewhere:
select distinct
round ( s.field1 ,                 5                          ) as Field1 ,
p.field2 as Field2
from
tableA@dblink a
JOIN tableB@dblink b on a.id = b.d
where
a.filter1= :filter1
and a.filter2 = :filter2

I tried to search for this exception but it really doesn't make any sense in my case (simple query, completely random, solved by adding/removing a space).
If you have ever experienced something similar or have any idea, please feel free to share!
  • 1. Re: Instant Client+ORA-01460: unimplemented or unreasonable conversion requeste
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Perhaps the following My Oracle Support note would be of interest:

    Intermittent ORA-1460 or ORA-1461 Accessing Database Link Using Bind Variables with ODP.NET [ID 745005.1]

    EDIT1:

    While the title of the above mentioned note may seem to imply that there is an ODP.NET issue at play here, there is not.

    The underlying issue described in the note is resolved via a database patch.

    Edited by: Mark Williams on Oct 31, 2012 1:15 PM
  • 2. Re: Instant Client+ORA-01460: unimplemented or unreasonable conversion requeste
    870565 Newbie
    Currently Being Moderated
    Hi Mark,

    Many thanks for the very quick reply.

    Where can I find more information about this oracle support note you refer to? I tried to google it but I don't seem to have access to the link (https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=745005.1).
    If you could share your source so that I can read a description of this issue and its solution it would be much appreciated! I am really looking forward to having a look at it!

    Also, and this is what really confuses me, I have a lot of database link (in almost all of my queries). However only this specific request throws that specific exception (randomly). Moreover the fact that adding/deleting a random space in the query solves the issue doesn't make any sense to me... Finally it would be on the DB side (and a patch is necessary) then I would expect Toad to throw a similar exception as well?

    But again, I would be more than happy to look into the note.

    Again thanks a lot for your help.
  • 3. Re: Instant Client+ORA-01460: unimplemented or unreasonable conversion requeste
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    In order to access My Oracle Support you would need a valid support contract and login. If you have support, perhaps a DBA or similar in your environment can either access the note or provide you with a login?

    The resolution is to apply a database patch for Bug 6960874 (either a one-off or a patchset that contains this fix).

    The issue revolves around a situation that can occur if subsequent binds in SQL using a database link are larger in size than the binds that were used during original parsing of the SQL. This may explain why adding a space, as you note, seems to resolve the issue for you. By doing that you are increasing the length of the bind value and/or forcing the SQL to be reparsed depending on the specifics.

    Here's a quick and dirty test using a 10.2.0.4 database that consistently reproduces the issue in a local environment for me:
    using System;
    using System.Data;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    
    namespace UnreasonableConversionTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                string constr = "User Id=hr;" +
                                "Password=hr;" +
                                "Data Source=v102;" +
                                "Statement Cache Size=20;" +
                                "Enlist=false;" +
                                "Pooling=false";
    
                // Granted "create database link" to hr as a dba user,
                // then database link created as hr user as follows:
                // create database link hr_test connect to hr identified by hr using 'V102';
                string sql = "select     e.employee_id " +
                             "from       employees@hr_test e " +
                             "where      e.first_name = :p_first_name " +
                             "and        e.last_name = :p_last_name";
    
                OracleDataReader dr = null;
    
                OracleConnection con = new OracleConnection(constr);
                con.Open();
    
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = sql;
    
                // It is critical for this test that these values be smaller/shorter
                // than the subsequent values used below.
                OracleParameter p_first_name = new OracleParameter("p_first_name",
                                                                   OracleDbType.Varchar2,
                                                                   32,
                                                                   "Bruce",
                                                                   ParameterDirection.Input);
    
                OracleParameter p_last_name = new OracleParameter("p_last_name",
                                                                  OracleDbType.Varchar2,
                                                                  32,
                                                                  "Ernst",
                                                                  ParameterDirection.Input);
    
                cmd.Parameters.Add(p_first_name);
                cmd.Parameters.Add(p_last_name);
    
                try
                {
                    dr = cmd.ExecuteReader();
                }
                catch (OracleException ex)
                {
                    Console.WriteLine(ex.Message);
                }
    
                // It is critical for this test that these values be longer/larger
                // than the previous bind/parameter values.
                p_first_name.Value = "Alberto";
                p_last_name.Value = "Errazuriz";
    
                dr.Dispose();
    
                // If the database link references a system affected by the issue
                // described in My Oracle Support Note 745005.1
                // (Intermittent ORA-1460 or ORA-1461 Accessing Database Link
                //  Using Bind Variables with ODP.NET),
                // then this should raise the ORA-1460 exception.
                // This behavior is not specific to ODP.NET and not resolved by
                // an ODP.NET patch, but it is convenient to reproduce via an ODP.NET
                // application.
                try
                {
                    dr = cmd.ExecuteReader();
                }
                catch (OracleException ex)
                {
                    Console.WriteLine(ex.Message);
                }
    
                dr.Dispose();
    
                p_last_name.Dispose();
                p_first_name.Dispose();
    
                cmd.Dispose();
                con.Dispose();
    
                Console.WriteLine();
                Console.Write("ENTER to terminate... ");
                Console.ReadLine();
            }
        }
    }
    I've never used Toad so I can't speak to trying to replicate this issue in that tool. However, I do believe that if the SQL is reparsed or the associated cursor can't be (re)used for whatever reason that the issue would not reproduce under such circumstances.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points