DBMS_OUTPUT and GTT issues running PL/SQL programs in Snaplogic

[wpenfold]

We are having issues with output when running our PL/SQL programs from Snaplogic.

Many of our existing pl/sql packages use DBMS_OUTPUT to write some information about the execution of the job–like row counts, any warning messages etc. When run from within Oracle, the DBMS_OUTPUT buffer gets automatically written to a log file. When run from Snaplogic, no log file is written.

PL/SQL programs that produce a more detailed report, use a Global Temporary Table, to build the report during processing, then write the data from the GTT to the report file at the end of the program. This doesn’t work in Snaplogic either, because instead of each job running in it’s own session, all of the oracle jobs that snaplogic runs use the same session–hence they share the same instance of the GTT. If multiple programs are running at the same time the reports become gobbledegook.

The end result is, every legacy PL/SQL program that we want to run from Snaplogic has to be rewritten.

Anyone else having this issue? What have you done about it?

[wpenfold]

To handle the GTT reporting issue at this point. I’ll try adding a job submission identifier to the GTT, so that each program can query for it’s own output.

[mbowen]

Thank you for logging a ticket about DBMS_OUTPUT.

The GTT issue may also require a separate ticket. Most of the time having snaps refer to same account is just fine and is what you want to do, but that doesn’t seem to be the case here. We don’t seem to have this logged as an issue, but I think it is.

Accounts cache the datasource and use a connection pool under-the-hood. We may want to investigate invalidating this cache in certain cases, such as this. This needs further investigation.

Kudos for your workarounds.