Stored Procedure starts logminer in same session every run

Question

I've created an oracle stored procedure to fetch data from logminer based on transaction rowid and timestamp. the procedure should try to select from logminer and if it fails as logminer is started on this session it should start it and select the data. The thing is every time the procedure starts the logminer on the same session

    create or replace PROCEDURE TRACK_TRACK (
CDC_TIMESTAMP IN VARCHAR2
,CDC_ROW_ID IN VARCHAR2
,SESSION_NUMBER OUT VARCHAR2
,SERIAL_NUMBER OUT VARCHAR2
,OS_USERNAME OUT VARCHAR2
,MACHINE_NAME OUT VARCHAR2
)
AS
V_LOG_NAME VARCHAR2 (256);
V_CDC_TIME DATE;
V_ROW_ID VARCHAR2 (18);
V_CDC_TIMESTAMP timestamp := to_timestamp (CDC_TIMESTAMP,'YYYYMMDDHH24MISSFF9');

BEGIN
SELECT session#,SERIAL#,'2',MACHINE_NAME,ROW_ID INTO SESSION_NUMBER,SERIAL_NUMBER,OS_USERNAME,MACHINE_NAME,V_ROW_ID FROM V$LOGMNR_CONTENTS 
Where ROW_ID=CDC_ROW_ID and TIMESTAMP=V_CDC_TIMESTAMP;
  EXCEPTION
  WHEN others
  THEN
        BEGIN
DBMS_LOGMNR.START_LOGMNR (STARTTIME=> SYSDATE-1,ENDTIME=>SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG 
                                                                                            + DBMS_LOGMNR.CONTINUOUS_MINE);
                                             
                                              
SELECT session#,SERIAL#,'1',MACHINE_NAME,ROW_ID INTO SESSION_NUMBER,SERIAL_NUMBER,OS_USERNAME,MACHINE_NAME,V_ROW_ID FROM V$LOGMNR_CONTENTS 
Where ROW_ID=CDC_ROW_ID and TIMESTAMP=V_CDC_TIMESTAMP;END;
  
    
END TRACK_TRACK;

how to control this so that the logminer doesnt start everytime?

Answer 1

EXCEPTION WHEN others

Please try to replace it with exception ORA-1306 and test few times, since it probably raises other errors internally:

CREATE OR REPLACE PROCEDURE TRACK_TRACK (
    CDC_TIMESTAMP IN VARCHAR2
   ,CDC_ROW_ID IN VARCHAR2
   ,SESSION_NUMBER OUT VARCHAR2
   ,SERIAL_NUMBER OUT VARCHAR2
   ,OS_USERNAME OUT VARCHAR2
   ,MACHINE_NAME OUT VARCHAR2
)
AS
--   V_LOG_NAME VARCHAR2 (256);
--   V_CDC_TIME DATE;
   V_ROW_ID VARCHAR2 (18);
   V_CDC_TIMESTAMP timestamp := to_timestamp (CDC_TIMESTAMP,'YYYYMMDDHH24MISSFF9');

   E_LOGNR_START exception;
   pragma exception_init(E_LOGNR_START, -1306);

   cursor cr is
      SELECT session#,SERIAL#,'2',MACHINE_NAME,ROW_ID
      FROM V$LOGMNR_CONTENTS
      Where ROW_ID=CDC_ROW_ID
        and TIMESTAMP=V_CDC_TIMESTAMP;

   procedure p is
   begin
      open cr;
      fetch cr INTO SESSION_NUMBER,SERIAL_NUMBER,OS_USERNAME,MACHINE_NAME,V_ROW_ID;
      close cr;
   exception when others then
      close cr;
      raise;
   end p;
BEGIN
   p();
EXCEPTION
   WHEN E_LOGNR_START THEN
       BEGIN
           DBMS_LOGMNR.START_LOGMNR (
              STARTTIME => SYSDATE-1,
              ENDTIME   => SYSDATE,
              OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE
           );
           p();
       END;
END TRACK_TRACK;
/