CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Listening to DBMS_ALERT

    Hi

    I need to be able to listen to DBMS_ALERT messages from oracle from within c#.

    Does anyone know how to do this?

    I need to be notified when a stored procedure terminates and then act on it from within the application.

    thanks

    Cheers

  2. #2
    Join Date
    Feb 2011
    Location
    United States
    Posts
    1,016

    Re: Listening to DBMS_ALERT

    From what I gather, you can do this with Database Change Notification using ODP.NET. This tutorial looks better than most and might be related:

    http://developergeeks.com/article/84...e-11g-database

    N.B.: I have no experience doing this myself.

    Hope that helps!
    Best Regards,

    BioPhysEngr
    http://blog.biophysengr.net
    --
    All advice is offered in good faith only. You are ultimately responsible for effects of your programs and the integrity of the machines they run on.

  3. #3
    Join Date
    Jan 2013
    Posts
    1

    Re: Listening to DBMS_ALERT

    Here is the code snippet
    Code:
    privateThread DBMSAlertThread;
    
    private void DBMSAlert(bool Register)
            {
                try
                {
                    string sSql;
                    if (Register)
                       sSql = "call dbms_alert.register('XYZ')";
                    else
                       sSql = "call dbms_alert.remove('XYZ')";
                    dbmsAlert = new OracleCommand();
                    dbmsAlert.CommandText = sSql;
                    dbmsAlert.ExecuteNonQuery();  
    
                    if (Register) //start the background thread
                   {
                       DBMSAlertThread = new Thread(AlertEvent);
                       DBMSAlertThread.IsBackground = true;
                       DBMSAlertThread.Start();
                   }
                }
                catch (Exception LclExp)
                {
                    //Show error or capture in eventlog
                }            
            }
    
    private void AlertEvent(object sender) 
    {
        while (true)
        {
            string Message = "";
            int Status = -1;
            bool bStatus;
            OracleParameter param;
            try
            {
                OracleCommand dbmsAlert = new OracleCommand();
                dbmsAlertScan.SQL.Add("call dbms_alert.WaitOne('XYZ', :Message, :Status, 0)"); //Last parameter indicate wait time
                param = new OracleParameter("Message", OracleDbType.Varchar2, ParameterDirection.Output);
                dbmsAlert.Parameters.Add(param); 
                param = new OracleParameter("Status", OracleDbType.Varchar2, ParameterDirection.Output);
                dbmsAlert.Parameters.Add(param); 
                OracleParameter.ExceuteNonQuery();
    
                Message = dbmsAlert.Parameters["Message"].Value.ToString();
                bStatus = int.TryParse(dbmsAlert.Parameters["Status"].Value.ToString(), out Status);
    
                if (Status == 0) //0 = Alert Received, 1 = Timed out
                {
                    //notify or do ur stuff
                }
            }
            catch (Exception Exp)
            {
                //raise an error
            }
        }
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured