execute package stored proc that INSERTS to temp table, then query tmp tble

Toad for Oracle
1

Toad for Oracle - version 9.6.1.1
Oracle 11g

I have a package stored proc that INSERT’s into a global temporary table - I’d like to execute that stored proc, and then be able to query the temp table myself with some adhoc queries, but I can’t seem to find a way to do that in Toad. I can execute the stored proc and that works fine, and the cursor it opens at the end looks fine. But after it executes, when I try to query the temp table that is populated by the stored proc, the temp table contains no rows. It there any way to execute/debug the stored proc such that I can query the temp table afterwards?

Thanks for any info.

2

Hi. I have recently had a similar problem whereby I have created multiple global temp tables and inserted rows into each table in one plsql staement but found when I query the temp tables after the query had completed there was no data in them but the script showed that rows had been inserted. Turned out that I had “threaded queries” enabled in toad which was causing my issue.

3

Hi,

This is more oracle then Toad question …

Data in GT tables may be captured only through bulk collection and outer autonomous transaction, which will allow other sessions to query data (even GTT session ends).

I have made a blog topic which describe solution for your needs.

Brg,

Damir Vadas