ORA-01403:尝试使用ODP.NET运行存储过程时，在第1行找不到任何数据

本文关键字：在第 1行 找不到 数据 任何 存储过程 运行 NET ODP ORA-01403 | 更新日期: 2023-09-27 18:20:47

我正在构建一个接口，该接口将通过.NET Winforms应用程序从Oracle数据库运行存储过程。我能够成功地连接到Oracle数据库，并且可以使用ODP.NET API来检索我选择的任何存储过程的所有存储过程和所有参数的列表。

当我尝试运行存储过程时，问题就存在了。当我尝试执行时，它会中断并返回Oracle错误，告诉我没有记录，但当我从sql developer运行存储过程时，它返回的正是我想要的。以下是存储过程：

    create or replace procedure sp_get_emp_info 
    (
       empid in INTEGER,
       m_UserName  out VARCHAR2,
       m_Email out VARCHAR2,
       m_IsActive out INTEGER
    ) as 
    begin
     SELECT 
       UserName,
       Email,
       IsActive
     INTO
       m_UserName,
       m_Email,
       m_IsActive
     FROM
       MICHAELTABLE
     WHERE ID = empid;
    end sp_get_emp_info;

这是我写的一个测试类，只是为了尝试在C#中阅读：

    using Oracle.ManagedDataAccess.Client;
    using System;
    namespace OracleTester
    {
    class Program
    {
        static void Main(string[] args)
        {                      
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = "User Id=apps;Password=apps;Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Orchid64)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = MVP)));";
            Console.BackgroundColor = ConsoleColor.Black;
            Console.ForegroundColor = ConsoleColor.DarkYellow;
            //string procs = "SELECT object_name FROM dba_objects WHERE object_type = 'PROCEDURE' AND owner = 'APPS'";
            //OracleCommand cmd = new OracleCommand(procs, conn);
            //cmd.CommandType = System.Data.CommandType.Text;
            //cmd.Connection.Open();
            //OracleDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            //int i = 1;
            //while (reader.Read())
            //{
            //    Console.WriteLine(i + ". " +reader.GetString(0));
            //    i++;
            //}
            OracleCommand comm = new OracleCommand();
            comm.CommandText = "SP_GET_EMP_INFO";
            comm.CommandType = System.Data.CommandType.StoredProcedure;
            comm.Connection = conn;
            OracleParameter user = new OracleParameter("m_UserName", OracleDbType.Varchar2, 25);
            user.Direction = System.Data.ParameterDirection.Output;
            OracleParameter email = new OracleParameter("m_Email", OracleDbType.Varchar2, 50);
            email.Direction = System.Data.ParameterDirection.Output;
            OracleParameter active = new OracleParameter("m_IsActive", OracleDbType.Double, 38);
            active.Direction = System.Data.ParameterDirection.Output;
            comm.Parameters.Add(user);
            comm.Parameters.Add(email);
            comm.Parameters.Add(active);
            OracleParameter empID = new OracleParameter("empid", OracleDbType.Double, 38);
            empID.Direction = System.Data.ParameterDirection.Input;
            empID.SourceColumn = "empid";
            empID.Value = 1;
            comm.Parameters.Add(empID);

            comm.Connection.Open();
            OracleDataReader rdr = comm.ExecuteReader();
            while (rdr.Read())
            {
                Console.WriteLine(rdr["m_UserName"].ToString());
            }
            Console.WriteLine("Server Version: " + conn.ServerVersion);
            conn.Dispose();
            Console.ReadLine();
        }
    }
}

当我尝试运行这个时，这里是我收到的异常：

Oracle.ManagedDataAccess.Client.OracleException was unhandled
  DataSource=""
  ErrorCode=-2147467259
  HResult=-2147467259
  IsRecoverable=false
  Message=ORA-01403: no data found
ORA-06512: at "APPS.SP_GET_EMP_INFO", line 9
ORA-06512: at line 1
  Number=1403
  Procedure=""
  Source=Oracle Data Provider for .NET, Managed Driver
  StackTrace:
       at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
       at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF)
       at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
       at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
       at OracleTester.Program.Main(String[] args) in C:'TestProjects'OracleTester'OracleTester'Program.cs:line 54
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

任何帮助都将不胜感激！！

ORA-01403:尝试使用ODP.NET运行存储过程时，在第1行找不到任何数据

来自文档：

默认情况下，OracleCommand.CommandType=Text命令的ODP.NET参数由位置绑定。OracleCommand类提供了一个BindByName属性--将此属性设置为true以按名称而不是按位置绑定参数。

您正在创建命名参数，但它们实际上是按位置添加的，这意味着您有效地为m_IsActive提供了一个值（尽管它是OUT参数），并使empid为空，就过程而言。

您可以将comm.BindByName=true设置为@b_levit在评论中指出的内容。或者，如果您重新排序参数分配以匹配正式的参数顺序，它将获得正确的值：

OracleParameter empID = new OracleParameter("empid", OracleDbType.Double, 38);
empID.Direction = System.Data.ParameterDirection.Input;
empID.SourceColumn = "empid";
empID.Value = 1;
comm.Parameters.Add(empID);
comm.Parameters.Add(user);
comm.Parameters.Add(email);
comm.Parameters.Add(active);
comm.Connection.Open();
OracleDataReader rdr = comm.ExecuteReader();

当CommandType属性设置为StoredProcedure时，文档似乎不太清楚返回值将包含什么，如果您正在调用函数，它可能会有一些有用的内容；但是对于过程调用，没有相关的结果集，所以这可能是空的。在上面循环找不到任何东西：

while (rdr.Read())
{
    Console.WriteLine(rdr["m_UserName"].ToString());
}

相反，您可以直接参考OUT参数的值：

Console.WriteLine(user.value);
相关文章：