I am pulling and writing data to an Oracle table with a procedure using

 
ON TABLE HOLD AS &TempTableName FORMAT SQLORA
 

My question is, Does this pull the data back to the Reporting Server and then send it back to Oracle? I was thinking there is some syntax to keep all the processing on Oracle, but I can't remember if it's in Webfocus or in Oracle. I am using SQL Pass-thru to do this.

This message has been edited. Last edited by: MattC,

On table hold means Oracle->WFRS->Oracle.

If you want Oracle only, then use pass-thru.

Thanks Dhagen. I think I will just do that. Do a INSERT with a SELECT statement.

quote:

...I was thinking there is some syntax to keep all the processing on Oracle...

I've been kicking the tires on FORMAT SAME_DB.

Maybe that is what you are thinking of?

We are using the Oracle Adapter too.

So far it looks promising, and is working fine.

Doing a SQL trace I can see the WF generated Oracle SQL SELECT/INSERT, along with a CREATE GLOBAL TEMPORARY TABLE...

I've subsequently joined to/from the temporary table to/from a regular Oracle table, with the join effective on the Oracle side AOK.

I am not a SAME_DB/GTT geek, so if anyone has any thoughts/experiences, I'd be all ears.

This message has been edited. Last edited by: David Briars,

I've used SAME_DB, and it does work, and all processing is on ORACLE.

But we found an issue with HOLD FORMAT SAME_DB PERSISTENCE GLOBAL_TEMPORARY, when used twice in the same proc. The second table ended up empty. There is a case and it is in Product division.