执行存储过程odp.net时发生异常 [英] Exception while executing stored procedure odp.net

问题描述

我已经用下面的代码执行了大约5个存储过程(参数将随存储过程而变化).除了下面的SP之外，这些都工作正常. 以下代码的输出是: p_out = 2 p_msg ='执行时例外'

I've executed around 5 stored procedures with the below code (parameters will vary with respect to Stored procedure). Those are working fine except the below SP. output of the below code is: p_out=2 p_msg= 'exception while execution'

在SP中，它将转到异常块.有人可以让我知道确切的问题吗?

In SP, it going to exception block. Can anybody let me know the exact issue.

代码:

  Database db = DatabaseFactory.CreateDatabase("OracleDBConnectionString");       
   OracleCommand oracleCommand = new OracleCommand();       
   oracleCommand.CommandType = CommandType.StoredProcedure;       
   oracleCommand.CommandText = "PCK_ADMIN.PROC_VALIDATE_USER";       
   oracleCommand.Parameters.Add("P_USERNAME", OracleDbType.Varchar2).Value = "ddd";         
   oracleCommand.Parameters.Add("P_out", OracleDbType.Int32).Direction =   ParameterDirection.Output;      
   oracleCommand.Parameters.Add("P_msg", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

db.ExecuteNonQuery(oracleCommand);
string outValue = oracleCommand.Parameters["P_out"].Value.ToString();
string outMsg = oracleCommand.Parameters["P_msg"].Value.ToString();

存储过程为:

create or replace
PACKAGE BODY PCK_ADMIN AS

PROCEDURE PROC_VALIDATE_USER
                          (P_USERNAME IN USR_USER.USERNAME%TYPE,
                           P_out  OUT NUMBER,
                           P_msg  OUT VARCHAR2) AS

 v_cnt      NUMBER(5):=0;

BEGIN

P_OUT := 0;

Select count(USERID) into v_cnt from usr_user
where username = P_USERNAME;

IF v_cnt = 0 then
  p_out := 0;
  p_msg := 'Record doesnot exists';
ELSE
  p_out := 1;
  p_msg := 'Record already exists';
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
 p_out := 2;
 p_msg := 'exception while execution';
 END PROC_VALIDATE_USER;
 END PCK_ADMIN;

usr_user的表结构为

Table Structure of usr_user is

 USERNAME   VARCHAR2(20 BYTE)
 USERID     NUMBER

推荐答案

我为P_msg输出参数指定了大小，并且可以正常工作.

I specified size for P_msg output parameter and it worked.

oracleCommand.Parameters.Add("P_msg", OracleDbType.Varchar2, 50).Direction = ParameterDirection.Output;

这篇关于执行存储过程odp.net时发生异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！