Logstash 6.2 not able to execute oracle database procedures

Elastic Stack Logstash
1

Hi

Logstash 6.2
Oracle database 12.1.0.2.0
Using ojdbc6.jar drivers to connect to oracle database (have tried also ojdbc7.jar). Connection attempt succeeds with both drivers, since select statements run successfully, for example

"select sysdate from dual"

But problem is, that I can't seem to execute oracle database procedures through logstash, it is giving me error:

invalid SQL statement: "actual sql statment"

Though statement's syntax itself is fine, since I have tested the same sql through sqlplus.
At first this procedure had a single parameter also, but then I removed it just to simplify this procedure as much as I can.

I have tried (statement must end without semi-colon, that what I have understood):

  • execute sys.truncate_audit
  • exec sys.truncate_audit
  • call sys.truncate_audit
  • sys.truncate_audit
  • begin sys.truncate_audit end
  • ..

Logstash conf is as follows:

jdbc {
jdbcdriverlibrary => "/etc/logstash/driver/oracle/ojdbc6.jar"
jdbcdriverclass => "Java::oracle.jdbc.driver.OracleDriver"
jdbcconnectionstring => "jdbc:oracle:thin:@//hostname.domain.com:1521/zzzzz"
jdbcuser => "YYYYYYYYY"
jdbcpassword => "XXXXXXXXXXXXX"
#parameters => { "param" => 'aud$'}
#statement => "execute sys.truncateaudit(:param)"
#statement => "execute sys.truncateaudit" <-- Tried this
statementfilepath => "/root/dbaudit/test1/statement.txt" <-- And I also tried putting this statement into separate file, same error though
trackingcolumn => "timestamp"
}
}
output {
stdout {
codec => rubydebug
}
}

Regards
Raul

2

I also have tried logstash 6.4 with ojdbc8.jar, still same error.

Regards
Raul

3

I finally found a way.
I created a function for that, since there seems to be no such possibility to execute procedures. It's a same though..
With functions I can call it via select statement, i.e. "select function from dual"

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.