title: Unable to retrieve DBMS_ALERT for a C# application
body: <p>I am trying to use <code>DBMS_ALERT</code> to provide feedback to a C# application from an Oracle 11g database.  In testing the stored procedures in the oracle tool TOAD, I am unable to retrieve any alerts.</p>
  
  <p>I'll show the stored procedures that I have written.</p>
  
  <p>My reason for doing this is to provide feedback to a progress bar on a winform. </p>
  
  <p>Am I misapplying the use of <code>DBMS_ALERT</code>?  I am new to Oracle, my background is with MSSQL server. </p>
  
  <p>Here is my stored procedure to generate an alert:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE HOWMET.DB_ALERT  (iMsg VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      DBMS_ALERT.SIGNAL('ALERT', iMsg);
      COMMIT;
    END DB_ALERT;
  </code></pre>
  
  <p>Here is my stored procedure to read an alert:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE HOWMET.GET_DB_ALERT_MSG(oMsg    OUT VARCHAR2,
                                                  oStatus OUT INTEGER)
  AS
    BEGIN
      DBMS_ALERT.REGISTER('ALERT');
      DBMS_ALERT.WAITONE('ALERT', oMsg, oStatus,5);
      DBMS_ALERT.REMOVE('ALERT');
  END;
  </code></pre>
  
  <p>Here is the main stored procedure that I am using to test the use of <code>DBMS_ALERT</code>:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE HOWMET.GETDATAWITHALERTS(EmployeeCur           OUT SYS_REFCURSOR,
                                                   EmployeeGridFormatCur OUT SYS_REFCURSOR)
  AS
  BEGIN
  
  DB_ALERT('Starting Point 1');  &lt;- calling an external sproc to signal the alert
  DBMS_LOCK.SLEEP(5);  
  
  OPEN EmployeeCur FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         EMAIL,
         HIRE_DATE,
         DEPARTMENT_ID,
         CAST(SALARY AS INT) AS "SALARY"
    FROM HR.EMPLOYEES;
  
  DB_ALERT('Mid Point 2'); &lt;- calling an external sproc to signal the alert
  DBMS_LOCK.SLEEP(5);
  
  OPEN EmployeeGridFormatCur FOR
  SELECT 'Employee ID|Hidden' AS "Employee_ID",
         'First Name' AS "First_Name",
         'Last Name|Width:90' AS "Last_Name",
         'Email|Width:80' AS "Email",
         'Hire Date|Width:80' AS "Hire_Date",
         'Dept Id|Width:65' AS "DEPARTMENT_ID",
         'Salary|Right|Fill|FMT:#`##0.00' AS "Salary"
    FROM DUAL;
  
  DB_ALERT('End 3'); &lt;- calling an external sproc to signal the alert
  DBMS_LOCK.SLEEP(5);
  
  END GETDATAWITHALERTS;
  </code></pre>
  
  <p>Here's the C# code in where I am experiencing an error.  I keep getting error -> "PL/SQL: numeric or value error".</p>
  
  <pre><code>string Message = "";
  int Status = -1;
  bool bStatus;
  try
  {
    using (OracleConnection oCnn = new OracleConnection(_connectionString))
    using (OracleDataAdapter oDataAdapter = new OracleDataAdapter())
    using (OracleCommand dbmsAlert = new OracleCommand("GET_DB_ALERT_MSG", oCnn))
    {
       dbmsAlert.CommandType = CommandType.StoredProcedure;
              // create output parameters
       dbmsAlert.Parameters.Add("oMsg", OracleDbType.Varchar2, 1800, ParameterDirection.Output);
       dbmsAlert.Parameters.Add("oStatus", OracleDbType.Int32, ParameterDirection.Output);
       dbmsAlert.Connection.Open();
       dbmsAlert.ExecuteNonQuery();
  
       bStatus = int.TryParse(dbmsAlert.Parameters["oStatus"].Value.ToString(), out Status);
       Message = dbmsAlert.Parameters["oMsg"].Value.ToString();
  
       if (Status == 0)  //0 = Alert Received, 1 = Timed out
       {
          if (Message != null &amp;&amp; Message != String.Empty &amp;&amp; Message != "null")
          {
             MessageBox.Show(Message, "Feedback");
          }
          else
          {
             MessageBox.Show(System.String.Format("No Message! Status:{0} Msg:{1}", Status, Message), "Feedback");
          }
       }
    }
  }
  catch
  {
    throw;
    //raise an error
  }
  </code></pre>