SQL Statement to Oracle error


#1

Hi,

i have configured a SQL statement to an oracle database in WATO. I get this error:

UNKNOWN - Error while processing result of SQL statement: tuple index out of range

the statement is:

select count(*) from XXX.XXX td where td.XXXXXX = ‘pending’

what can i do? i have read in old monitoring portal forum that the answer has to have three columns. But how do i achive that?

regards,


(Philipp Näther) #2

I guess you are referring to this thread:

https://monitoring-portal.org/woltlab/index.php?thread/41425-abfrage-an-mysql-schlägt-fehl/&pageNo=2

If yes, the solution is given there. You have to get a set of three columns of your slq query back. First column is state (0,1,2,3) the second is the status detail, the third is performance data.


#3

Hi,

yes exactly. The problem is i only get one column.

when i do a select 0,0,count(*) the sate is always ok. but the state should change according its warning and critical values (10, 20).

regards,


(Philipp Näther) #4

It is all mentioned in the old thread… It is even stated in the help text of the rule:

The SQL-statement or procedure name which is executed on the DBMS. It must return a result table with one row and at least two columns. The first column must be an integer and is interpreted as the state (0 is OK, 1 is WARN, 2 is CRIT). Alternatively the first column can be interpreted as number value and you can define levels for this number. The second column is used as check output. The third column is optional and can contain performance data.


#5

Hi,

so you mean that would be the solution for me:

select count( * ), “pending”, count( * ) …

regards,


(Philipp Näther) #6

If you set the lower and upper levels in the rule aswell, yes.


#7

How do you ensure that the first value equals the correct status?


(Philipp Näther) #8

It is not a status if the levels are set. Then it is a range and cmk calculates the status with the value of the first column and the levels by itself.


#9

is it also possible to leave the lower levels blank? i mean not to set lower levels, only upper levels.

regards,


(Philipp Näther) #10

I don’t know. Just try it.


#11

Hi,

UNKNOWN - Error while executing SQL statement/procedure: ORA-00904: “Pending Transactions”: invalid identifier

with this statement:

select count( * ),“Pending Transactions”,count( * ) from XXXXX.XXXXXXX td where td.XXXXXXXXX = ‘pending’

Whats wrong? i thought i could give a string at position 2.

regards,


(Philipp Näther) #12

Maybe single quotes for prending transactions. For mysql it works with double quotes there, maybe oracle does not like that.


#13

Hi,

with single quotes it is working. but now i get this output:

OK - 0.0 Pending Transactions

i think when there are 1 pending transaction there will be an output like 1.1. thats not realy good becuase it is NOT 1.1, it is 1 pending transaction. know what i mean?

regards,


#14

How about acutally checking that instead of thinking? That’s most probably a float value so you will have a value of n.0 for a number of n Transactions.


#15

Hi,

no it is definetly NOT a float value:

/opt/check_oracle_health --environment ORACLE_HOME=/vg01lv00/oracle/12.1.0 --method sqlplus --connect XXXX --user check_mk --password XXXXXXXXX --mode sql --name “select count(*) from XXXX.XXXX td where td.XXXX = ‘pending’” --name2 count
OK - count: 0 | ‘count’=0;1;5

i checked already :wink:

regards


#16

It’s not a float value when directly calling the plugin. That doesn’t mean that CheckMK will not alter the output.


#17

Hi,

no it is not. it is a result from from select count( * ),“Pending Transactions”,count( * )…

the first count is 0 and the second count is 0 too. so it shows 0.0. i think so :slight_smile:

regards,


(Philipp Näther) #18

Instead of using another plugin to test it, I would run sqlplus straight on command line with the sql statement you defined in the WATO rule. That is imho the most logical way to test what your oracle database provides. Because 0.0 definitely looks like a float value. I am not an oracle guy but it is possible sqlplus provides things differently than mysql (or a third party plugin) does for example.


#19

As you can see the output differs, among other things, depending on the option “--name2”. Unless you’re trying to compare things which are comparable, we’ll continue to argue.


(Philipp Näther) #20

I just tested it on my site and it is, as expected and stated by Wolfgang, due to check_mk altering the value to float. I assume it does that, because it uses also float values for the upper and/or lower warn/crit levels and does the calculation stuff with it.

So nothing to worry about.