It really depends on what you are trying to do. You can use DBMS_APPLICATION_INFO if you are running a long procedure...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Lewis Cunningham
I need a step-by-step procedure for importing text to an Oracle database using a stored procedure.continue reading
How can I tell if a column is a NUMBER or VARCHAR in PL/SQL? Is there a function to do this?continue reading
Is it possible that we can hide some objects in a particular schema, so that users can't see them or I have control in some way that if users can see...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.