Executing an Oracle Stored Procedure from Sender JDBC adapter

[Former Member]

I could really use some help from someone who had done this before.

I've read the help about using the JDBC sender adapter, but it's not helping enough.

I found this line: "Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.

The expression must correspond to the SQL variant supported by the relevant JDBC driver. It can also contain table JOINs."

That's definately what we want to do, but we can't figure out the syntax.

The procedure in oracle looks like this:

CREATE OR REPLACE PROCEDURE test_ref_cursor 
( cur_generic IN OUT result_sets.cur_generic)
as

BEGIN
Open cur_generic for
 select
   proposal_number,
   to_char(sequence_number),
   column_name,
   column_value,
   update_timestamp,
   update_user
   from
   coeus.sap_test;
   
   
END test_ref_cursor;
/

And we have tried every kind of statement we can think of, but the file adapter always gives us an "invalid sql statement" error.

Does anyone know what syntax we need to put in the "Query SQL Statement" in the JDBC sender adapter in order to call this procedure? Or is there something wrong with the procedure that is causing the error?

<i>I will absolutely return and give points, but PLEASE read my whole post before answering and do not just link me to or quote the help for configuring a sender JDBC adapter or blogs that are about the JDBC adapter in general but do not deal with the issues I am having. Thank you.</i>

[former_member189324 · Accepted solution]

Hi Vanda,

Unfortunately, the sender JDBC adapter does not support Oracle's store procedure/function. Unlike stored procedures from other database vendors, Oracle returns a cursor, not a resultset. The sender JDBC adapter must send a resultset to XI.

There are 2 possible ways you can accomplish this:

1. Use BPM and call the Oracle stored procedure using a receiver adapter via a asynch-synch bridge.

2. Develop a user-module for the adapter, which can be used with a sender adapter.

Thanks

Prasad

[bhavesh_kantilal · Accepted solution]

Hi Prasad,

<i>Unlike stored procedures from other database vendors, Oracle returns a cursor, not a resultset. The sender JDBC adapter must send a resultset to XI.</i>

Great info. Something I was not aware of.

Thanks,

Bhavesh

[Former Member · Accepted solution]

Prasad,

Your answer was very help full.Even i am facing the same issue like this.Can you please explain me about Asyn-Syn bridge.

[Former Member]

Yes, thank you VERY much. I wish they'd just put a little footnote about this in the help for the jdbc adapter, it would have saved several people here several days of work.