Search code examples
oracle-databaseplsqltibco

Invoke a stored procedure from tibco bw with cursor as inputs

I have a provided stored procedure (SP) that i should execute in tibco, which has the following signature:

PROCEDURE  GET_STOCK_QTY(
  WarehouseId Number,
  LineItem ref_lines,
  ResponseCode OUT Number,
  ResponseDesc OUT Varchar2,
  RespLineItems OUT sys_refcursor
);

The *ref_lines* is defined as follows:

TYPE items_record is RECORD(
  ItemCode varchar2(15),
  ItemQuantity number
);
type ref_lines IS REF CURSOR RETURN items_record;

So, as you've already noticed i have a cursor as an input and output of the SP. I've already been googling for this kind of problems and found out that tibco does not suport these type of in and outputs, i've also found out that it does not even support boolean native type, which is weird... :S Eitherway, for what I've read, two possible solutions are on the way:

  1. SQL Direct
  2. Java Custom Function

Now, my doubts.

If I'm going to use solution number 1 I have to build the cursor to provide as input in pure sql, but how I'm a going to do it? Do I have to create a temporary table with the values that i have for the input and then open a cursor for that table? Is this the only way to do this?

I haven't explored solution number two yet, but i thought that Java could support these kind of inputs and outputs and i just declare arrays. Is this assumption right? Will it be this easy, or I'll have invoke sql as i would in solution number 1?

PS: Are this 2 possible solutions the only ones? Is there any tibco guru with a workaround to make BW support this WEIRD inputs and outputs? :P

Thanks in advance, Tiago Brunhoso Nunes

Solution

  • After a lot of googling the only way I managed to overcome this issue was to create a wrapper package on the database that encapsulates the inputs and the outputs that tibco could not recognize into something that it can.

    What I've done for now was:

    1. Created two new tables ( 1 for the input cursor and another for the output);
    2. Created a wrapper SP that accepts as inputs and outputs only the simple datatypes - without the cursors;
    3. Tibco inserts the input array of values into the created table;
    4. The wrapper SP opens a cursor to this table and invokes the first SP with the cursors;
    5. Finally it fills the output table with the values from the output cursor and Tibco reads this table;

    (I know that probably it isn't the best way to do this... :S)

    PS: It came to my knowledge that oracle pl sql supports natively xml inputs and I could have gone to that solution, which seems to be a much cleaner one, but it would take me too long to implement... If anyone has any knowledge on this, I would appreciate some tips! :)