Skip to Content
0
Former Member
Apr 10, 2006 at 08:49 PM

Executing an Oracle Stored Procedure from Sender JDBC adapter

221 Views

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>