Reading from a oracle temp table in a separate procedure than the one it was populted in

Question

I have a 2 stored procedures, the first creates an oracle temp table and the second reads from it. The temp table only has scope for that session. I'm calling the procedures from .Net and the second procedure never returns any results. However if I use the same sprocs and parameters in SQL*Plus it works fine.

I've tried creating an Oracle Transaction object and had hoped I'd be able to read the tables in while still using the same transaction - trying to emulate an SQL Plus type of single connection environment.

Any ideas what I'm doing wrong??

Answer 1

There are a few ways to connect to Oracle from .Net, but in general I assume that at some point you're creating a Connection object and using that to generate commands, etc. You need to re-use that Connection object between the two calls. This may mean passing the Connection object around, or using some kind of singleton pattern to retrieve it.

Answer 2

Use PRESERVE ON COMMIT when creating your GLOBAL TEMPORARY TABLE, otherwise the table will be cleared after each commit.