0

I'm looking for a way to use my Global Temp Table for multiple procedure calls... Some context:

Global Temp Table holds a list of IDs. It's created with an on commit delete rows; clause.

My package body has 1 function and 5-10 procedures. My first iteration had 6-11 functions until I found I couldn't return a cursor and then push it into another function and so on and so on...

So the procedures will save IDs to a GTT, each procedure having very different logic which cannot be cleanly done in a single query.

My problem is that when jumping between procedure calls (all procedures are called from within the first function), the transaction seems to be renewed in some way and the GTT is refreshed.

I could use a regular table and trunk the table at the start and end of each call to the function but there must be a better way.

Any advice?

Qbert
  • 102
  • 1
  • 11
  • There must be a `commit` or `rollback` happening somewhere. – GriffeyDog Sep 28 '15 at 15:18
  • 1
    either a commit is happening, or you're actually using multiple sessions (you don't elaborate much on how these multiple procedures are being called. Connection pool used?) – tbone Sep 28 '15 at 17:39

1 Answers1

0

You must have a commit in there somewhere then.

You could modify the table to preserve rows on commit, of course. Then you would explicitly remove the rows yourself, or when the session ends they would be cleared.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • edit; Apologies for formatting. There is no commit. I've checked many times. `procedure do_something as begin insert into gtt select * from etc; end do_something` I have `on commit delete rows;` at the end of the gtt but nowhere do I ever commit. This is how I know its doing that or ending the transaction. Trying to find out how to preserve the transaction now – Qbert Sep 28 '15 at 15:37
  • If you modify the GTT to preserve rows on commit, then if you still get this problem it's because you are connecting as a different session -- if the problem goes away, it's either an Oracle bug or an implicit commit (any DDL statements in your code?) – David Aldridge Sep 28 '15 at 18:30