title: Passing Null/empty string to Oracle stored procedure from asp.net
body: <p>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 <a href="http://ora-01084.ora-code.com/" rel="nofollow noreferrer">ORA-01084</a> error, which indicates to me that call is failing before the procedure is actually run.</p>
  
  <p>Here's the procedure, minus some logic that I believe is not relevant:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Here's the invocation of <code>CreateReport</code> from the web service:</p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>As an experiment, I modified the web service to pass <code>null</code> rather than an empty strings.  When <code>null</code> is passed in, I see an error indicating "wrong number or types of arguments in call to 'CREATEREPORT."</p>
  
  <p>I also tried passing <code>DBNull.Value</code> whenever the params were null/empty, but that resulted in the error </p>
  
  <p><code>Parameter 'p_column_id': No size set for variable length data type: String.</code> </p>
  
  <p>(Of course, p_column_id was the empty parameter in this case).</p>
  
  <p>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.</p>