OLE DB Data Source Monitor error: ORA-00900 invalid SQL statement

 

There are no free management packs for monitoring Oracle databases in SCOM 2012. However, a free solution is to use the OLE DB Data Source monitor and install Oracle client on the watcher node. There are several blogs about how to set this up, so I’m not going to write about that.

 

What I am going to write about is the “ORA-00900 invalid SQL statement” error one might receive from these monitors. Some weeks ago I was at a costumer who wanted to monitor some Oracle databases and I configured the monitor without a query, which worked fine. I then put in the query and I got the following error:

clip_image001

(in Danish, sorry)

 

Depending on which program you use when executing a stored procedure you simply put ”exec” or ”execute” before the query, but both of these failed in SCOM.

 

I then starting investigating with the local Oracle guy and we found that you use BEGIN and END when executing the stored procedure;

BEGIN

your_stored_procedure;

END;


In the query you cannot use line breaks, so you need to make the query a one-liner:

BEGIN your_stored_procedure; END

clip_image003

 

I made this change and the monitor turned green right away!

 

Conclusion:

When you want to execute an Oracle query you need to put in BEGIN your_stored_procedure; END;

Happy monitoring your Oracle databases for free!

By | 2014-02-10T16:08:23+00:00 February 10th, 2014|Operations Manager (SCOM)|1 Comment

About the Author:

Michael Skov
Yet another guy loving the System Center products. My primary focus is on Operations Manager, which i have worked with for some years now - still loving it.Certifications:Microsoft Certified Solution Expert Private Cloud (MCSE)Microsoft Certified Solutions Associate Windows Server 2008 (MCSA)Microsoft Certified Technology Specialist Windows Server 2008 R2, Server Virtualization

One Comment

  1. fgh October 9, 2014 at 15:26 - Reply

    fgh

Leave A Comment