How do I ensure my 2 procedures update the same record during the same session

Question

I have two procedures PROC_BEGIN and PROC_END that are meant to log the performance of stored procedures used to create Aggregate tables in our warehouse. I am running Oracle Version: Oracle Database 19c

CREATE OR REPLACE PACKAGE BODY SCHEMA.MY_PACKAGE AS
  PROCEDURE PROC_DO_SOMETHING IS
  
BEGIN
  
  PKG_ETL_LOGGER.PROC_BEGIN(parameters); --gets the start time, name of called procedure and inserts into a log table

--Execute my code that 
  
   PKG_ETL_LOGGER.PROC_END(parameters);--gets the end time, counts number of records and inserts into the log table
  
  END;
 
 END;

How do I ensure that the two procedures update the same record for the same session?

I have tried updating the record by using similar columns like the date and procedure name, but I am getting duplicate records from other users running the same code

UPDATE USER_NAME.LOG_TABLE
     SET ROW_COUNT = v_row_count,
         PROC_END_TIME = v_end_time,
         PROC_NAME = v_owner||'.'||v_caller,
         RUN_TIME_SECS = (v_end_time - v_start_time )*24*60*60,
         RCRD_INSRT_DATE = sysdate
    WHERE ID_DATE = p_date AND PROC_NAME = v_owner||'.'||v_caller AND TABLE_NAME = P_TABLE_NAME ;

Answer 1

Your code snippets suggests 2 separate log records are being created but your question indicates it's creating the log in PROC_BEGIN and updating that same log record with additional information in PROC_END. I'm assuming that is what you're asking. One way to do it is to have an OUT parameter in PROC_BEGIN with the primary key of the created log record. Then pass that value as an IN parameter into PROC_END. To get the primary key of a record that on insert use the RETURNING INTO clause.

So in PROC_BEGIN the statement would be something like (shortened version - change as needed)

INSERT INTO USER_NAME.LOG_TABLE (proc_name, rcrd_insrt_date)
  VALUES (v_owner||'.'||v_caller, SYSDATE)
RETURNING id INTO l_id;

PROC_BEGIN would have an OUT parameter with value of l_id assigned

PROC_END would take id as in parameter (p_id) would update the actual record

UDPATE USER_NAME.LOG_TABLE  
  SET ...
WHERE id = p_id;