DBMS_APPLICATION_INFO is a Oracle supplied package which can be helpful in debugging, to track the progress of a long running procedure or to set any useful information in v$session view, which can be useful for Programmers, DBAs.
Consider a scenario where you are executing some statements (DML) in a loop. Lets assume that the loop iterations are more than 10 million. Now if something fails (say unique constraint error) in middle of loop, it becomes difficult to get to the exact record due to which it failed.
One way is to use DBMS_OUTPUT for each iteration, but that would fill up the stack.
Another way is use autonomous error logging procedure which would write to a separate error table. But this is not efficient way of doing since performance will slow down and also the unnecessarily we are filling up the error table.
In situations like these, Application_Info package comes handy.
This package has a procedure called SET_CLIENT_INFO, this will set values in column CLIENT_INFO of the table v$session. This value will be immediately visible to other sessions and we need not even commit.
Psuedo Code:
Session1:
for rec in (some select which has 10 million records)
loop
-- ...Some calculations
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('Program is executing - ' || rec.col1 || '-' || rec.col2);
--- DMLs
end loop;
When the above program is running, we can query v$session to monitor the progress.
select client_info from v$session where sid := :x;
It can also be used to set the Bind variable values being used in a dynamic query.
The other procedures inside the package are below:
SET_MODULE/GET_MODULE : This allows you to set MODULE and ACTION columns in v$session.
MODULE column should be name of the Process
ACTION column should be set to name of the procedure you are executing in a Package.
SET_SESSION_LONGOPS : use this to monitor progress of any long running routine. Set the value for any routine which will take more than few seconds to.
