Yes, it would be very helpful if the stored procedure snap could capture the DBMS_OUTPUT. In the meantime, I’ve written a procedure that writes to a file, that we can use to replace the DBMS_OUTPUT calls. But it does mean substantial changes to each oracle program we want to run from Snaplogic.
Also, It would be helpful regarding the global temp table report issue, if Snaplogic didn’t run multiple pl/sql procedure calls concurrently within the same Oracle session. Right now, if program A and program B are run concurrently from 2 different pipelines, they both share the same Oracle session–therefore the same temporary table. Both write report data to the same instance of the temporary table. Whichever program ends first, produces a report containing mixed output from both A and B.
Enabling and capturing DBMS_OUTPUT looks very doable, but it is some work. If you would like this feature, it would great if you could log a support ticket requesting this.
Thanks for clarifying the GTT issue. Actually, you were pretty clear in your first post. The stored procedure snap is associated with an Account which will try to re-use its Connection object (this maps to a db session in Oracle).
When you describe the mixed output, I’m suspecting that the StoredProcedure snaps in different pipelines refer to the same account. If that’s true, you could experiment with cloning the account (ex: Account2) and then referring to that account in one of the pipelines. If GTT data is private to a db session, then concurrent pipeline runs should write to different GTT tables?
Thanks. I’ll submit a ticket about supporting DBMS_OUTPUT.
As far as the GTT issue, we have over 100 pipelines running now, and many more to convert from our current scheduling software. I don’t think that creating and using a different account for each pipeline would be feasible.
I don’t know if Snaplogic could handle db connections any differently…? We have multi-user PHP web applications using connection pooling that successfully call Pl/SQL packages using GTTs without collision, so it seems like it ought to be possible.
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.
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.
