This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Solarwinds SAM Oracle User Experience Component Monitor Running PL/SQL (and returning results)

Many of our monitoring checks in our current product that we are converting to Solarwinds heavily utilize inline PL/SQL to perform the analysis on the Oracle databases they monitor.

One of the challenges we have with our current product is that we are forced to run the PL/SQL in Oracle's SQL*PLUS program. We run Bash scripts that execute SQL*PLUS which then runs the inline PL/SQL block.

In order for us to run SQL*PLUS, we are forced to manually install and edit custom environment files on each monitored target. This is required, because in LINUX environments, you have to know the location of the SQL*PLUS program.

Solarwinds provides an Oracle User Experience component which allows us to embed SQL statements that access the database directly. Since the calls originate from the SAM environment, there is no need to source any other programs (like SQL*PLUS).

We attempted to embed an inline PL/SQL block into the Oracle User Experience Monitor. Oracle interprets the PL/SQL correctly, but we are unable to return the values generated by the PL/SQL program back to Solarwinds for analysis. The message being returned is "no rows returned". We tried DBMS_OUTPUT, RETURN, PRINT and OUT variables. None worked.

We are unable to rely upon any language being installed on the monitored targets, so there is no way that we can rely upon Perl, or any other language being available to execute the PL/SQL. BASH will be available but iit can only use SQL*PLUS to access the Oracle database.

The question we have is - is there ANY way we can return values back from an inline PL/SQL program to Solarwinds using the Oracle User Experience Monitor?

  • According to Oracle User Experience Monitor you have to return only one numerical value.

    And if that did not help you there should be a "Oracle Database" application template which contains some Oracle User Experience Monitors. So you can edit the template and go to the components and see how it is done there. (e.g. "select sum(bytes/1024/1024) "Data_files_space, MB" from dba_data_files" from "Data files size (MB)")

  • We have the same problem, there are instances that we need to run pl/sql due to bind variables on partition parameters on a select statement that can't be process on a regular select statement.

    We only produce one value coming from DBMS_OUTPUT, RETURN, PRINT and OUT variables as chrisfoot mentioned. The only way is to use a shell script and call sqlplus and pass the code.

    Is there another way for us to do this?

  • Have you tried using pipelined PL/SQL Functions so that you can access the result as a basic select from type query?

    e.g.  (From Oracle docs - its only a simple column in this example, but it could easily be a record type)

    CREATE OR REPLACE PACKAGE pkg1 AS

      TYPE numset_t IS TABLE OF NUMBER;

      FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;

    END pkg1;

    /

    CREATE PACKAGE BODY pkg1 AS

      -- FUNCTION f1 returns a collection of elements (1,2,3,... x)

      FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS

      BEGIN

      FOR i IN 1..x LOOP

      PIPE ROW(i);

      END LOOP;

      RETURN;

      END f1;

    END pkg1;

    /

    Use pipelined table function in FROM clause of SELECT statement:

    SELECT * FROM TABLE(pkg1.f1(5));

    Result:

    COLUMN_VALUE
    ------------
               1
               2
               3
               4
               5
  • Unfortunately, creating stored procedures isn't an option for me. What I did was a shell script

    ------------------

    #!/bin/sh

    export ORACLE_HOME=/orabin/app/oracle/product/11.2.0.3/dbhome_1

    export lv_statistic=`/orabin/app/oracle/product/11.2.0.3/dbhome_1/bin/sqlplus -s << EOF   ##keep in mind that "`" starts here after lv_statistic=

    username/password@ServicenameorSID ## this can be a variable passed on the command line

    SET SERVEROUTPUT ON

    set pagesize 0 feedback off verify off heading off;

    DECLARE

    ## xxx

    lv_statistic VARCHAR(100)

    BEGIN

    ## xxx - I've used dynamic sql codes here due to some query variables

    lv_statistic := 'Statistic.Name1: ' || yourvariableresulthere;  #this is for Solarwinds formatting for statistics

    DBMS_OUTPUT.put_line (lv_statistic);

    END;

    /

    exit

    EOF'       ## the "`" ends here after EOF

    echo $lv_statistic

    -------------------

    LV_statistic will be used for threshold.