Whenever you have PL/SQL programs (procedures) that you want to check, you can integrate this code into your objects so you can:
- Monitor long-running jobs in real time while the are executing ✅
- Log exact date and time from start to finish ✅
- Assist in program debugging 🪲 by logging an exception and the aproximate line in code where the error occurred ✅
This utility is (currently) designed to run as a sandbox for objects within the same schema, so you will need permissions to create sequences, stored proceures and tables. You may also need additinal permissions to access DBMS_UTILITY and DBMS_LOCK system packages from your desired schema. Please contact your beloved DBA 😒 for assistance (where applicable).
1️⃣ Install sequence
2️⃣ Install job table
3️⃣ Install logging table
4️⃣ Install stored procedure
Please see "SQL" folder with source code for prerequisites
git clone https://github.com/yllanos/oralog.git
--------------------------------------------------------
-- DDL for Procedure P_SAMPLE
--------------------------------------------------------
CREATE OR REPLACE PROCEDURE "P_SAMPLE" AS
/********************************************************************************************
DESCRIPTION:
IN PARAMETERS: N/A
OUT PARAMETERS: N/A
DATE CREATED: 2017-09-05
AUTHOR: Yamil Llanos (yamil.llanos@gmail.com)
Example use:
*********************************************************************************************/
--Variables
v_job_name varchar2(30) := 'P_SAMPLE'; --< IMPORTANT: This should match name of procedure above >
v_start_date date := sysdate;
v_end_date date;
v_comment_ varchar2(255);
v_successfull varchar2(1) :='N';
v_total_readed_rows number:=0;
v_total_inserted_rows number:=0;
v_total_discarded_rows number:=0;
v_total_update_rows number:=0;
v_usuario_bd varchar2(30) := user;
v_job_owner varchar2(30) := 'CRMEF'; --< Set this to whatever DB user schema will run this procedure >
v_etl number(1) := 1;
v_job_type varchar2(30) := 'PL/SQL';
v_where varchar2(200) ;
P_DATE date := sysdate;
BEGIN
v_end_date := sysdate;
v_comment_ := 'Starting '||to_char(P_DATE,'YYYY-MM-DD');
p_oralog (v_job_name
,v_start_date
,v_end_date
,v_comment_
,v_successfull
,v_total_readed_rows
,v_total_inserted_rows
,v_total_discarded_rows
,v_total_update_rows
,v_usuario_bd
,v_job_owner
,v_etl
,v_job_type
,P_DATE);
-- So, this is where the action starts to happen
dbms_lock.sleep( 120 );
-- And this is the start of the end
v_end_date := sysdate;
v_successfull := 'Y';
v_comment_ := 'Ended OK '||to_char(P_DATE,'YYYY-MM-DD');
p_oralog (v_job_name
,v_start_date
,v_end_date
,v_comment_
,v_successfull
,v_total_readed_rows
,0
,v_total_discarded_rows
,v_total_update_rows
,v_usuario_bd
,v_job_owner
,v_etl
,v_job_type
,P_DATE);
COMMIT;
exception when others then
v_end_date := sysdate;
v_comment_ := (v_comment_ ||DBMS_UTILITY.format_error_backtrace || ' ' || sqlcode || ' ' || sqlerrm);
p_oralog (v_job_name
,v_start_date
,v_end_date
,v_comment_
,v_successfull
,v_total_readed_rows
,v_total_inserted_rows
,v_total_discarded_rows
,v_total_update_rows
,v_usuario_bd
,v_job_owner
,v_etl
,v_job_type
,P_DATE);
ROLLBACK;
end;
/
Right after the BEGIN statement, you should assign to a variable the current sysdate
and execute a call to p_oralog
. This will log to oralog
table a new job and the exact time it started running.
💡 You can repeat the process every time you need to set a "checkpoint" on your program code. Don't worry, p_oralog
is smart enough to update the record for this specific job instance running every time a checkpoint is added 😃
You should also include a call before the end of your procedure to allow for logging of last execution time. Also, as you see on example, there is an additional call is an exception if raised by the DB engine, this will log the current time and the aproximate line where the error was raised, very useful for long and complex programs!
NOTE: DB lock is there to simulate a long running operation or group of operations.
If you run the sample above or your own procedures with the suggested code, you only need to:
SELECT *
FROM ORALOG
WHERE JOB_NAME = 'YOUR_PROCEDURE_NAME'
Or
SELECT JOB_NAME, START_DATE, END_DATE, COMMENT_, SUCCESSFULL
FROM ORALOG
WHERE JOB_NAME = 'YOUR_PROCEDURE_NAME'
ORDER BY 2 DESC;
Ideally, you should do this while your procedure is runnig. It´s safe to also query afterwards anyway.
It doesn't work so well when subprograms are called from your main procedure (unless you also implement this on your subprograms 😉 )
Please 🙏 report any issues you may encounter