Track a deployments progress

I wanted to code a basic procedure to allow app team to track change in a deployment or general and came up with below

TIMESTAMP used for display and DATE can be used in future to purge table.

I’ve also added option to COMMIT (p_commit) the insert or not in case the COMMIT may interfere with deployment and you still want to commit outside of the procedure, default is not to commit.

I’ve also added option to dbms_output (p_out), to display output to screen

DB name is sourced from sys_context as well to add so if restoring db to another env we can see which comments are valid for that env.

PROCEDURE DEPLOYMENT_AUDIT_PROC
Argument Name Type In/Out Default?

P_DESC VARCHAR2 IN DEFAULT
P_COMMIT BOOLEAN IN DEFAULT
P_OUT BOOLEAN IN DEFAULT

CREATE TABLE DEPLOYMENT_AUDIT_TAB(db_name varchar2(32), step number, DESCRIPTION varchar2(512), RUN_DATE_CHAR varchar2(64), RUN_DATE date);

CREATE SEQUENCE DEPLOYMENT_AUDIT_SEQ;

CREATE OR REPLACE PROCEDURE DEPLOYMENT_AUDIT_PROC(p_desc VARCHAR2 default 'Not Specified', p_commit BOOLEAN default FALSE, p_out BOOLEAN default FALSE)
-- psammy 21122023
as
v_step number := DEPLOYMENT_AUDIT_SEQ.nextval;
BEGIN

INSERT INTO DEPLOYMENT_AUDIT_TAB values (SYS_CONTEXT('USERENV', 'DB_NAME'),  v_step,  p_desc, to_char(systimestamp), sysdate);

IF p_commit = TRUE
THEN
	COMMIT;
END IF;


IF p_out = TRUE
THEN
	dbms_output.put_line(v_step ||':'||p_desc);
END IF;

END DEPLOYMENT_AUDIT_PROC;
/


exec DEPLOYMENT_AUDIT_PROC(p_desc=>'This is test')

set serverout on
exec DEPLOYMENT_AUDIT_PROC(p_desc=>'This is test2',p_out=>TRUE)
set serverout off
-- output: 2:This is test2


-- The above updates will be lost with rollback
rollback;

exec DEPLOYMENT_AUDIT_PROC(p_desc=>'This is test2',p_out=>TRUE,p_commit=>TRUE)

BEGIN
	DEPLOYMENT_AUDIT_PROC(p_desc=>'This is test3',p_commit=>TRUE);
END;
/

exec DEPLOYMENT_AUDIT_PROC('This is test3')
commit;

set lines 220
col db_name for a12
col change_code for a20
col description for a40
col run_date_char for a40

select * From DEPLOYMENT_AUDIT_TAB;

DB_NAME STEP DESCRIPTION RUN_DATE_CHAR RUN_DATE

TEST2 3 This is test2 21-DEC-23 10.50.06.123307 AM +00:00 21-DEC-23
TEST2 4 This is test3 21-DEC-23 10.50.06.126272 AM +00:00 21-DEC-23
TEST2 5 This is test3 21-DEC-23 10.50.06.128911 AM +00:00 21-DEC-23