Skip to Content
avatar image
Former Member

Executing an Oracle Stored Procedure from Sender JDBC adapter

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>

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 10, 2006 at 09:13 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Bhavesh Kantilal

      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.

  • avatar image
    Former Member
    Apr 11, 2006 at 11:15 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded