从asp.net传递null /空字符串到Oracle存储过程 [英] Passing Null/empty string to Oracle stored procedure from asp.net

问题描述

我们有一个调用我们的DB（的Oracle 11g）的存储过程的ASP.NET Web服务。问题是，在调用程序的每一个空字符串作为一个参数传递时炸毁。我们看到一个 ORA-01084错误的，这表明，我认为呼叫在手术前没有实际运行。

We have an ASP.NET web service that invokes a stored procedure on our DB (Oracle 11g). The problem is that the call to the procedure blows up every time an empty string is passed as one of the parameters. We're seeing an ORA-01084 error, which indicates to me that call is failing before the procedure is actually run.

下面是过程，减去一些逻辑，我认为是不相关的：

Here's the procedure, minus some logic that I believe is not relevant:

PROCEDURE CreateReport
( 
  from_dt IN NVARCHAR2,
  to_dt IN NVARCHAR2,
  p_table_id   IN NVARCHAR2,
  p_column_id  IN NVARCHAR2,
  device_name IN NVARCHAR2,
  ret_cursor OUT t_cursor
)
AS
  v_cursor t_cursor;
  dateStr NVARCHAR2(200);
  sqlStmt VARCHAR2(10000);
  extraColumns NVARCHAR2(10000);
BEGIN

    /* SNIP Cut logic that builds the query statement based on inputs */

    OPEN v_cursor FOR sqlStmt;
    ret_cursor := v_cursor;

END CreateReport;

下面的 CREATEREPORT 从Web服务调用：

Here's the invocation of CreateReport from the web service:

public DataSet CreateReport(string fromDt, string toDate, string tableNames, 
                            string columnNames, string deviceName)
{
    DataSet dataSet;

    string fmDateStr = String.IsNullOrEmpty(fromDt) ? String.Empty : fromDt.Trim();
    string toDateStr = String.IsNullOrEmpty(toDate) ? String.Empty : toDate.Trim();

    OracleCommand oleDBCmd = new OracleCommand();

    oleDBCmd.CommandType = CommandType.StoredProcedure;
    oleDBCmd.CommandText = "Web_Pkg.CreateReport";

    oleDBCmd.Parameters.Add(new OracleParameter("from_dt", OracleType.NVarChar));
    oleDBCmd.Parameters["from_dt"].Value = fmDateStr;
    oleDBCmd.Parameters["from_dt"].Direction = ParameterDirection.Input;

    oleDBCmd.Parameters.Add(new OracleParameter("to_dt", OracleType.NVarChar));
    oleDBCmd.Parameters["to_dt"].Value = toDateStr;
    oleDBCmd.Parameters["to_dt"].Direction = ParameterDirection.Input;

    oleDBCmd.Parameters.Add(new OracleParameter("p_table_id", OracleType.NVarChar));
    oleDBCmd.Parameters["p_table_id"].Value = tableNames;
    oleDBCmd.Parameters["p_table_id"].Direction = ParameterDirection.Input;

    oleDBCmd.Parameters.Add(new OracleParameter("p_column_id", OracleType.LongVarChar));
    oleDBCmd.Parameters["p_column_id"].Value = columnNames;
    oleDBCmd.Parameters["p_column_id"].Direction = ParameterDirection.Input;

    oleDBCmd.Parameters.Add(new OracleParameter("device_name", OracleType.LongVarChar));
    oleDBCmd.Parameters["device_name"].Value = deviceName;
    oleDBCmd.Parameters["device_name"].Direction = ParameterDirection.Input;


    oleDBCmd.Parameters.Add(new OracleParameter("ret_cursor", OracleType.Cursor));
    oleDBCmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;

    try
    {
        // Throws exception if any of the paramters are String.Emtpy
        dataSet = DB.SQLSelect(oleDBCmd, connStr);
    }
    catch (Exception exp)
    {
        return null;
    }
    finally
    {
        if (oleDBCmd != null)
        {
            oleDBCmd.Dispose();
        }
    }

    return dataSet;
}

作为一个实验，我修改了Web服务通过空，而不是一个空字符串。当传递空，我看到指示错误错号码或类型的呼叫'CREATEREPORT论点。

As an experiment, I modified the web service to pass null rather than an empty strings. When null is passed in, I see an error indicating "wrong number or types of arguments in call to 'CREATEREPORT."

我也试过路过 DBNull.Value 每当PARAMS是空/空的，但是这导致误差

I also tried passing DBNull.Value whenever the params were null/empty, but that resulted in the error

参数'p_column_id'：没有为可变长度的数据类型的大小设置：字符串

（当然，p_column_id在这种情况下，空的参数）。

(Of course, p_column_id was the empty parameter in this case).

那么，怎样才能成功通过空字符串作为参数传递给我的存储过程？我们一定要允许p_column_id参数是空的。

So, how can I successfully pass empty strings as parameters to my stored procedure? We definitely want to allow the p_column_id parameter to be empty.

推荐答案

您可以做任何空的参数的下面。

You can do the following for any nullable parameters.

oleDBCmd.Parameters.Add(new OracleParameter("to_dt", OracleType.NVarChar));
if(string.IsNullOrEmpty(toDateStr)) {
    oleDBCmd.Parameters["to_dt"].Value = DBNull.Value;
} else {
    oleDBCmd.Parameters["to_dt"].Value = toDateStr;
}
oleDBCmd.Parameters["to_dt"].Direction = ParameterDirection.Input;

这样的话，你不依赖于字符串 - >由Oracle适配器转换为空

That way, you're not relying on string -> null conversion by the oracle adapter.

编辑：如果不能解决这个问题，这是最有可能的类型不匹配，检查的NVarChar VS的VarChar

If this doesn't fix the issue, it is most possibly a mismatch between types, check NVarChar vs VarChar

这篇关于从asp.net传递null /空字符串到Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！