EXPERT RESPONSE
It really depends on what you are trying to do. You can use DBMS_APPLICATION_INFO if you are running a long procedure and just want to see what it's doing. If you really need to log run times, you can write a simple program to do that.
First, create a log table:
CREATE TABLE PROC_LOG
(
PROC_INSTANCE NUMBER NOT NULL,
PROC_NAME VARCHAR2(100) NOT NULL,
IN_TIME TIMESTAMP(6) NOT NULL,
OUT_TIME TIMESTAMP(6),
COMMENTS VARCHAR2(1000),
PRIMARY KEY (PROC_INSTANCE)
)
/
And a sequence:
CREATE SEQUENCE PROC_INSTANCE
/
In the table, proc_instance is a sequence generated primary key so that you can identify unique runs of your procedure. Proc name can be the actual procedure you are running or a made up process name. In_time is the date and time that the run started and out_time is the date and time it ended. Comments are an optional clear text field.
And now a log package:
CREATE OR REPLACE PACKAGE proclog_pkg AS
FUNCTION init_log( p_proc_name IN VARCHAR2,
p_comments IN VARCHAR2 DEFAULT NULL )
RETURN proc_log.proc_instance%TYPE;
PROCEDURE end_log( p_proc_instance IN proc_log.proc_instance%TYPE );
END;
/
Init_log starts a record and end_log finishes it. See the package body:
CREATE OR REPLACE PACKAGE BODY proclog_pkg AS
FUNCTION init_log( p_proc_name IN VARCHAR2,
p_comments IN VARCHAR2 DEFAULT NULL )
RETURN proc_log.proc_instance%TYPE AS
v_proc_instance proc_log.proc_instance%TYPE;
BEGIN
INSERT INTO proc_log
(proc_instance, proc_name, in_time, comments)
VALUES ( proc_instance.nextval,
p_proc_name,
localtimestamp,
p_comments )
RETURNING proc_instance INTO v_proc_instance;
RETURN v_proc_instance;
END;
PROCEDURE end_log( p_proc_instance IN proc_log.proc_instance%TYPE ) AS
BEGIN
UPDATE proc_log
SET out_time = localtimestamp
WHERE proc_instance = p_proc_instance;
END;
END;
/
You run it like this:
DECLARE
v_proc_instance proc_log.proc_instance%TYPE;
i NUMBER := 0;
i_am_done BOOLEAN := FALSE;
BEGIN
-- Start logging
v_proc_instance := proclog_pkg.init_log('My Package Name', 'This is a comment' );
-- Run some random code
LOOP
EXIT WHEN i_am_done;
i := i + 1;
i_am_done := i > 10000;
END LOOP;
-- End logging
proclog_pkg.end_log( v_proc_instance );
END;
/
Now you can select from the proc_log table and see your values.
If you want it to always commit, you can modify the procedures by using pragma autonomous_transaction and always commit in this package.
|
