I would like a way to show progress for a long running funciton (which may take several minutes to run).
I am aware of techniques such as using this package (to show progress via the v$session):
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO( client_info => 'Mikes Test App'); --64 char max as of 12c
exec DBMS_APPLICATION_INFO.SET_ACTION('Step 1 of 1 - Starting Engine...'); --64 char max as of 12c
select sid, serial#, osuser, username, module,
to_char(sql_exec_start,'yyyymmdd-hhmm') sql_exec_start,
to_char(logon_time,'yyyymmdd-hhmm') logon_time,
client_info, action
from V$SESSION where osuser='memyselfandI' order by sql_exec_start desc;
I am also aware I can store job logging info/messages into a log file (such as on a shared DIRECTORY object folder)
However, I would like to see if there is a way to use DBMS_OUTPUT.put_line... to show progress.
So something like this:
CREATE OR REPLACE FUNCTION NESTED_BLOCKS(parm1 IN VARCHAR)
RETURN VARCHAR2 AS
BEGIN
BEGIN
DBMS_OUTPUT.put_line ('Staring process. Progress will be written to \\orasvr\DIRECTORY_SHARE\log.txt');
END;
BEGIN
/* perform some long running process which updates/indicates progress to log.txt */
END;
BEGIN
DBMS_OUTPUT.put_line ('Ending process. See \\orasvr\DIRECTORY_SHARE\log.txt');
END;
RETURN 'something';
END NESTED_BLOCKS;
I wanted to know if anyone has thoughts or ideas.