Everyone, who already created long-running PL/SQL procedures, knows about the problem: How can I determine the current status of my procedure? Is is close to finish, or is it hanging?

DBMS_OUTPUT is not really helpful - due to the fact, that invocations of DBMS_OUTPUT.PUT_LINE
actually "fill a buffer", and after the PL/SQL call finished, SQL*Plus will retrieve the
buffer contents with DBMS_OUTPUT.READ_LINE. At that time, the information will be visible on the screen.
The buffer is furthermore session-private, so we cannot access them from
another session.

Other alternatives would be to use autonomous transactions in order to perform INSERTs into
a table or to use UTL_FILE to write something into a file on the database servers' filesystem.
But the Oracle database already offers a very good tool for this purpose:
V$SESSION_LONGOPS. Oracle itself writes status information for long running SQL statements into
that view (you might check that during a long running CREATE TABLE AS SELECT or CREATE INDEX).
The PL/SQL package DBMS_APPLICATION_INFO contains the procedure SET_SESSION_LONGOPS, which allows to maintain own status
information within V$SESSION_LONGOPS. But its API is rather cumbersome and complex - you need to
pass a lot of parameters - and some of them are not easy to understand. Instrumenting own code
with direct invocations of SET_SESSION_LONGOPS would lead to confusing and hard-readable programs very quickly.

So I'd propose a wrapper package for DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS. The goal is to
provide an easy interface for PL/SQL developers.

DO_INIT initializes a new row in V$SESSION_LONGOPS - the current status of
that row would be zero. Subsequent invocations of DO_UPDATE lead to
updated status information. A long-running PL/SQL procedure, instrumented with
these calls would look like this:

This is easy, isn't it? And we reporting our status to a standard monitoring
infrastructure within Oracle. Database administrators seeking information on long running
jobs, typically look into V$SESSION_LONGOPS. When creating this package, my primary goal was
to create a simple API, so I did not use some of the SET_SESSION_LONGOPS parameters (such as the
TARGET_ID parameter). But if somebody likes, the package can be easily extended ...

The new database version Oracle12c contains, as always, many new functions which are not
highlighted all around - in all the presentations and whitepapers. One of these is the new PL/SQL package
DBMS_QOPATCH.
It allows to read the Oracle Software Repository ("Oracle Inventory") with SQL. If you want to
know which one-off patches are installed on the current system, you can now query this with
SQL - logging into the operating system is no longer required.

Honestly, this was already possible before Oracle12c - some years ago, I publised a
blog posting, which described how to achieve this with
a BFILE and some SQL/XML functions. Now, we have this out-of-the-box - so far, so good.

But finding something
within this plain text would be still cumbersome. We want structured
information - we are within a database.

Luckily, we can still use SQL/XML functions, work directly on the XML output
and project all the information
into columns of a SQL SELECT result. First, we want to see, which "oneoff" patches
have been installed.

Looking for a specific bug is now so easy, since this is a SQL result. Of course, these
SQL queries could be extended very easily - all you have to do is to look into the
XML documents in order to determine the XML tags actually being used. And in the next
step we could create some views like "DBA_INST_PATCHES" or "DBA_FIXED_BUGS" ...

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.