Oracle Procedure monitoring progress

Solve Oracle PLSQL code monitoring;

Monitor code done via:

DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'Anything/Proc name',  action_name => 'Anything');
DBMS_APPLICATION_INFO.SET_ACTION(action_name => 'Anything/Progress');

See Sample:
Monitoring session as DBA:
08:56:43 SQL> l
  1* select Module,action from v$session where sid=263
08:56:47 SQL> /

MODULE                                                           ACTION
---------------------------------------------------------------- ----------------------------------------------------------------
SQL*Plus

Note: Session id is 263. Note Module is default and action is blank, after executing following code in session 263.

SQL> DECLARE
  CURSOR c1 IS
    SELECT * FROM emp;
BEGIN
  FOR PC IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || PC.ename || ', Job = ' || PC.job);
  END LOOP;
END;
/  2    3    4    5    6    7    8    9   10   11
Name = SMITH, Job = CLERK
Name = ALLEN, Job = SALESMAN
Name = WARD, Job = SALESMAN
Name = JONES, Job = MANAGER
Name = MARTIN, Job = SALESMAN
Name = BLAKE, Job = MANAGER
Name = CLARK, Job = MANAGER
Name = SCOTT, Job = ANALYST
Name = KING, Job = PRESIDENT
Name = TURNER, Job = SALESMAN
Name = ADAMS, Job = CLERK
Name = JAMES, Job = CLERK
Name = FORD, Job = ANALYST
Name = MILLER, Job = CLERK

PL/SQL procedure successfully completed.

NOW little modification:

SQL> DECLARE
  CURSOR c1 IS
    SELECT * FROM emp;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'TESTCode',  action_name => 'BeforeCursor');/* Only at start */
  FOR PC IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || PC.ename || ', Job = ' || PC.job);
          DBMS_APPLICATION_INFO.SET_ACTION(action_name => c1%rowcount);
  END LOOP;
END;
/  2    3    4    5    6    7    8    9   10   11   12   13
Name = SMITH, Job = CLERK
Name = ALLEN, Job = SALESMAN
Name = WARD, Job = SALESMAN
Name = JONES, Job = MANAGER
Name = MARTIN, Job = SALESMAN
Name = BLAKE, Job = MANAGER
Name = CLARK, Job = MANAGER
Name = SCOTT, Job = ANALYST
Name = KING, Job = PRESIDENT
Name = TURNER, Job = SALESMAN
Name = ADAMS, Job = CLERK
Name = JAMES, Job = CLERK
Name = FORD, Job = ANALYST
Name = MILLER, Job = CLERK

PL/SQL procedure successfully completed.

See session monitor difference:

9:01:38 SQL> /

MODULE                                                           ACTION

---------------------------------------------------------------- ----------------------------------------------------------------

TESTCode                                                         14

09:01:39 SQL> 

You can use DBMS_APPLICATION_INFO.SET_ACTION(action_name => c1%rowcount);

to display session progress like:

1. Cursor Open next

2. Procession rows

3. Last error

4. Procession complete

5. Anything and everything you fathom......Enjoy!!!