Calling a function / store procedure from jdbc input pluging

Elastic Stack Logstash
1

Hello,
I'm trying to invoke a function from the jdbc input plugin.
I've read this

But I get the following error

[2025-03-07T14:43:00,004][WARN ][logstash.inputs.jdbc ][main][335bb609b009b4900e39c184c3e0c418f819a629827a54ef285b30935a6cbbf6] Exception when executing JDBC query {:exception=>Sequel::DatabaseError, :message=>"Java::JavaSql::SQLException: ORA-29478: Implicit result cannot be returned through this statement.\nORA-06512: at \"SYS.DBMS_SQL\", line 2877\nORA-06512: at \"SYS.DBMS_SQL\", line 2871\nORA-06512: at \"APPTEST.SPGETDATAFN\", line 7\n", :cause=>"#<Java::JavaSql::SQLException: ORA-29478: Implicit result cannot be returned through this statement.\nORA-06512: at \"SYS.DBMS_SQL\", line 2877\nORA-06512: at \"SYS.DBMS_SQL\", line 2871\nORA-06512: at \"APPTEST.SPGETDATAFN\", line 7\n>"}

My function is defined like this

create or replace function spGetDatafn
 return sys_refcursor
 is
   rc sys_refcursor;
  begin
   open rc for select * from billing;
         dbms_sql.return_result(rc);
  end;
/

and using this function code

create or replace function spGetDatafn
 return sys_refcursor
 is
   rc sys_refcursor;
  begin
   open rc for select * from billing;
                    return rc;
  end;
/

I get the following error

[2025-03-07T14:59:28,500][WARN ][logstash.inputs.jdbc ][main][335bb609b009b4900e39c184c3e0c418f819a629827a54ef285b30935a6cbbf6] Exception when executing JDBC query {:exception=>Sequel::DatabaseError, :message=>"Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=oracle.jdbc.driver.ForwardOnlyResultSet, simple name=ForwardOnlyResultSet", :cause=>"#<Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=oracle.jdbc.driver.ForwardOnlyResultSet, simple name=ForwardOnlyResultSet>"}
Has anyone suceeded with this? @ Wolfram_Haussig @raj.teaser
Thank you

2

Hello Anna,

The post you mentioned is for functions that return a single value, but you want to return a list of rows. Logstash tries to convert each value into one of the datatypes known to Elastic and it doesn't know how to convert ForwardOnlyResultSet.

Do you really need a sys_refcursor in your application? If you define it as a (pipelined) table function you should be able to use select * from TABLE(spGetDatafn).

Best regards
Wolfram

PS: Please don't @mention people that are not already in the discussion. I am reading and answering here in my spare time so let me please decide myself which posts I want to answer to.

1 Like
3

Thank you and my apologies