Unable to configure oracle stored procedure in logstash jdbc pipeline

[Sreenivas1 (Sreenivas A)]

Hi all,

I'm trying to call stored procedure created in oracle database using logstash jdbc pipeline but even I tried with many ways to pass stored procedure in statement it's getting failed with sql error exceptions . please help me with correct way to configure this.
Note: I'm able to execute stored procedure in sql developer client and it's working fine.

tried all the commented statements one after other but no one is working

input {
  jdbc {
jdbc_validate_connection => true 
jdbc_driver_library => "<path>\ojdbc8.jar"
jdbc_connection_string => "jdbc:oracle:thin:@<host>:1521/<db>"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_user => "<user>"
jdbc_password => "<pwd>"
sql_log_level => "debug"
schedule => "*/1 * * * *"

# statement => [ "EXEC <package>.<storedprocedure>()" ]
# statement => [ "EXEC <package>.<storedprocedure>();" ]
# statement => [ "exec <package>.<storedprocedure>()" ]
# statement => "CALL <package>.<storedprocedure>"
# statement => "{call <package>.sp_elk_sync_ptnr_cntct}"
#statement => "{call <storedprocedure>}"
# {call PKGNAME.STOREDPROCNAME(?)}
# e.g.
# statement => "{call <package>.<storedprocedure>()}"
  }
}

[Wolfram_Haussig]

Hi,

What are you intending to do? A jdbc input only makes sense when the procedure really is a function and returns something that can be processed lateron. In this case, you could use something like statement => "Select <package>.<storedprocedure>() FROM DUAL"

Best regards
Wolfram

[Sreenivas1 (Sreenivas A)]

Hi Wolfram,

Thanks for your reply.

In my case Stored procedure will filter records from one table and keep it in a temporary table, which I'm fetching in subsequent Jdbc inputs.

And I tried with statement you have provided but it's giving
below error

 [2023-08-09T12:39:03,689][WARN ][logstash.inputs.jdbc     ][main][6d730e488867f8c827ad66de6f57cf4778a2b3702141cd39337059e0c734f861] Exception when executing JDBC query {:exception=>"Java::JavaSql::SQLSyntaxErrorException: ORA-00904: \"<package>\".\"<procedure>\": invalid identifier\n"}

Thanks,
Sreenivas A

[Wolfram_Haussig]

Hello Sreenivas,

Okay, I understand. In my opinion, there are 3 options:

1. Convert your procedure into a function which retuns a dummy value so that you can use select procedure() from dual to call it from within logstash.
2. Remove the stored procedure and use the SQL used in it directly in the jdbc input. Depending on the complexity, this might not be possible.
3. Do not trigger the procedure in Logstash and run it as a database scheduler instead.

I would prefer (3) and create a scheduler that calls the procedure regularly and thus updates your temporary table. Logstash can then rely on the table always being up-to-date without worrying about refreshing the table.

Best regards
Wolfram