title: How to monitor execution of long running stored procedure in Oracle DB_
body: <p>I have a stored procedure that is preparing data for staging area for further use. This procedure has several steps that each last several minutes. It is invoked from C# front end. Here is simplified procedure flow:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE PROCEDURE SP1
  AS
  BEGIN
    INSERT INTO T1 SELECT ... FROM T2;
    INSERT INTO P1 SELECT ... FROM P2;
    INSERT INTO Q1 SELECT ... FROM Q2;
  END;
  </code></pre>
  
  <p>It is very simple to invoke this in c# code (OracleConnection, OracleCommannd, ExecuteNonQuery...).
  However, since this procedure will be executed from front-end by user, it would be very informative if he/she can monitor progress of this procedure. What I have found so far is OracleConnection.InfoMessage event and RAISE_APPLICATION_ERROR function. 
  I am adding </p>
  
  <pre class="lang-sql prettyprint-override"><code>BEGIN 
    RAISE_APPLICATION_ERROR (-20001, 'My message text');
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  </code></pre>
  
  <p>in my stored procedure with hope that InfoMessage event will be raised which is not. Here is InfoMessage event handler which is set during OracleConnection initialization (Connection.InfoMessage += OnInfoMessage):</p>
  
  <pre><code>private void OnInfoMessage(object sender, OracleInfoMessageEventArgs e)
  {
    foreach (OracleError err in e.Errors)
    {
      ShowSomeText(err.Message);
    }
  }
  </code></pre>
  
  <p>After removing BEGIN EXCEPTION block error is caught in C# code but InfoMessage has not been fired in this case too. 
  What I am doing wrong in this case? 
  I have used similar technique for MsSql server and it works smoothly. Do I miss some session/connection related setting?</p>
  
  <p>PS</p>
  
  <p>I am trying to avoid usage of another connection that might query some system objects or user log tables. This would be used as fallback scenario.</p>