ORA-01460在select语句中触发不一致 [英] ORA-01460 triggers inconsistent on select statement

问题描述

大家好，



当我在我的asp.net MVC项目中执行下面的select语句时，我正试图弄清楚出了什么问题。

Hi all,

I'm trying to get my head around what's going wrong when I'm executing the below select statement in my asp.net MVC project.

public static Vessel GetVesselDetails(string vesselId, string outbound, string callnumber)
        {
            var vessel = new Vessel {Id = vesselId};
            
            using (var connection = new OracleConnection(_connectionString))
            {
                const string query = @"SELECT * FROM OPS_REPORT.SHIP_VOYAGE WHERE SHIP_ID= :1 AND SHIP_VOYAGE.OUT_VOY_NBR= :2 AND SHIP_VOYAGE.OUT_CALL_NBR = :3";
                var command = new OracleCommand(query) { Connection = connection };
                
                command.Parameters.Add("1", OracleDbType.NVarchar2, 5).Value = vesselId;
                command.Parameters.Add("2", OracleDbType.NVarchar2, 5).Value = outbound;
                command.Parameters.Add("3", OracleDbType.NVarchar2, 5).Value = callnumber;

                try
                {
                    connection.Open();
                    OracleDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        vessel.Name = dr["NAME"].ToString();
                        vessel.LineOperator = dr["LINE_ID"].ToString();
                        vessel.Service = dr["SERVICE_ID"].ToString();
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }
            }
            return vessel;
        }

传递的参数会触发以下错误，但是他们这样做了不一致。

The parameters passed trigger the below error, however they're doing this not consistently.

ORA-01460: unimplemented or unreasonable conversion

例如当我使用以下参数运行上述操作时，它运行没有任何问题。

For example when I run the above with the following parameters, it runs without any issues.

string vesselId = "DES";
string outbound = "16C1";
string callnumber = "1";

但是参数更改为如下所示，它返回ORA-01460异常...

However the parameters change to the below, it returns the ORA-01460 exception...

string vesselId = "CGVG";
string outbound = "078E";
string callnumber = "1";

当我将上述值直接通过Toad执行到数据库视图时，两个select语句都能正常工作。所以这让我更加困惑。



我确定我忽略了一些简单的事情，或者以不推荐的方式处理参数声明。任何指向正确方向的人都非常感激。



我尝试过的事情：



我在浏览ORA-01460错误消息的一些信息后尝试改变我设置参数的方式。

Both select statements work perfectly when I execute them with the above values directly via Toad onto the database view. So that puzzles me even more.

I'm sure I'm overlooking something simple, or handling the parameter declaration in a way that's not recommended. Any pointers towards the right way are much appreciated.

What I have tried:

I have tried changing the way I set the parameters after browsing some information in regards to the ORA-01460 error message.

command.Parameters.Add(new OracleParameter("1", vesselId));
command.Parameters.Add(new OracleParameter("2", outbound));
command.Parameters.Add(new OracleParameter("3", callnumber));

当发送两者时，我收到错误消息会发生什么我在上面的问题摘要中描述的参数集。



我也检查了表格上的数据类型，它们是：

SHIP_ID - > VARCHAR2（4字节）

OUT_VOY_NBR - > VARCHAR2（5字节）

OUT_CALL_NBR - > VARCHAR2（1个字节）

所有这些都不是Nullable。

What happens than is that I get the error message when sending in both sets of parameters I described in the Problem summary above.

I checked the Datatypes on the tables as well, these are:
SHIP_ID -> VARCHAR2 (4 Byte)
OUT_VOY_NBR -> VARCHAR2 (5 Byte)
OUT_CALL_NBR -> VARCHAR2 (1 Byte)
All of them are not Nullable.

推荐答案

你应该学会尽快使用调试器。而不是猜测你的代码在做什么，现在是时候看到你的代码执行并确保它完成你期望的。



调试器允许你跟踪执行逐行检查变量，你会看到它有一个停止做你期望的点。

调试器 - 维基百科，免费的百科全书 [ ^ ]

掌握Visual Studio 2010中的调试 - A初学者指南 [ ^ ]



使用调试器确保一切都符合预期，跟踪每一行上的所有变量，直到出现意外情况。

你的问题是dat一个依赖，所以必须在服务器上跟踪问题的根源。

如果你不能调试，使用 Console.WriteLine 作为检查点使用变量上的值记录代码正在做什么。



[更新]

You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

Use the debugger to make sure everything is as expected, track all variables on each line until something unexpected append.
Your problem is data dependent, so one must be on server to track the root of problem.
If you can't debug, use Console.WriteLine as checkpoint to log what the code is doing with what values on variables.

[Update]

引用：

ORA-01460：未实现或不合理的转换

ORA-01460: unimplemented or unreasonable conversion

而不是使用 string s ，我宁愿尝试 char array s。看起来Oracle无法将收到的参数转换为处理查询所需的类型。

Rather than using strings, I would rather try char arrays. Looks like Oracle is unable to convert the parameters it receive to the type it needs to process the query.

这篇关于ORA-01460在select语句中触发不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！