Temporary tables and JDBC channel.

[Former Member]

Hello everyone.

I have external system and i need to call oracle stores procedure and get data from temporary tables which fills after procedure executed.

How can i do this??

[prateek]

Temporary tables are only visible to the current session of transaction. It is dropped once that session is closed. So if u think that XI will call these tables when ur session is open, then it will recognize these tables using the JDBC communication channels. Otherwise it will throw some error like 'table name not found'.

Regards,

Prateek

[Former Member]

Prateek, you right that tables visible only in the current session and if i will use standard JDBC channel, tables will be empty :(.

I want try BPM process for solving this problem.

If anyone have ideas, please wright to this message, I'll be wait

Regards,

Ilya.

[Former Member]

Iliya, this is difficult..

first of all a question: isn't it possible to develop a DB function, which calls the stored proc and returns the data?

I think, it can't be done cos:

to execute the procedure you'll use a send step into target.. but to retreive the data, you'll need JDBC sender - and receive step in BPM. I'm not sure, if these two can operate within one DB session.. I'd say no ..but I'm not sure

However - you can try:

receive step (starts BPM)

send step (to execute stored proc)

receive step (for data sent by JDBC sender from temp table)

send step (send data somewhere).

Peter

Peter

[Former Member]

Hi Ilya!

You can use receiver JDBC adapter and standard call of stored procedure in the target DB.

Peter