This forum is currently locked. You can't register or post questions at this time. (read more)

Detect missing records in a table and send mail

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

sellyh19
Senior Member
Posts: 67
Joined: Wed Mar 02, 2011 2:24 pm

Detect missing records in a table and send mail

Postby sellyh19 » Wed Apr 23, 2014 11:11 am

Good day Tim,

I need your assistance as I don't know where I am going wrong in my procedure.

The aim with this procedure is to check on a daily basis only if there are missing records for the previous day. I first create a copy of the previous day’s data in the table: MISSING_REC_TB

This procedure is not working properly, As soon as it detects a missing record, it quits the procedure instead of printing the missing record in the message mail. It seems like it is not entering the loop at all. To test if its working i deleted a record in a table and there's no notification/mail send out.

Code: Select all

CREATE OR REPLACE PROCEDURE MISSING_REC (g_start_date IN DATE,g_end_date   IN DATE) IS


/*
***************************************************************************************************
      This procedure check for missing records for the date range passed.
      Notify by mail & sms if missing numbers occurred
***************************************************************************************************     
*/


/************************************* Variable declaration **************************************/

  v_startdate_c varchar2(18) := to_char(trunc(g_start_date), 'dd-mon-rrrr') ||
                                ' 000000';
  v_enddate_c   varchar2(18) := to_char(trunc(g_end_date), 'dd-mon-rrrr') ||
                                ' 235959';
  v_start_date   timestamp := to_date(v_startdate_c, 'dd-mon-rrrr hh24miss');
  v_end_date     timestamp := to_date(v_enddate_c, 'dd-mon-rrrr hh24miss');

   v_min_rec              number(9) := 0;
   v_max_rec              number(9) := 0;
  v_err_count            NUMBER (2);
  v_trans_date           cdruser.cdrgsm_prosp_tb.trans_date%type;
  v_record_nr            cdruser.cdrgsm_prosp_tb.record_nr%TYPE;
  v_find_record_nr       cdruser.cdrgsm_prosp_tb.record_nr%TYPE; 
  v_file_name            cdruser.cdrgsm_prosp_tb.file_name%TYPE;
  v_search_date          TIMESTAMP;
  v_search_date_start    TIMESTAMP;
  v_search_date_end      TIMESTAMP;
  a                      VARCHAR2 (20) := '';
  b                      VARCHAR2 (120) := '';
  c                      VARCHAR2 (4000) := '';
  d                      VARCHAR2 (150) := '';
  v_missing              VARCHAR2 (1) := 'F';
  v_find_count           NUMBER := 0;
  v_first                VARCHAR2 (1) := 'F';

BEGIN

/************************************************************************************************
      The gsm_missing_rec_copy_tb table is already created.  Check for missing records
      from this table.
************************************************************************************************/


/************************************************************************************************
      get min record nr of the day from table daily_cdrgsm_copy_sp
************************************************************************************************/

 v_search_date_end := v_start_date + 30/1440;
 select /*+ PARALLEL(MISSING_REC_TB,12) */
     /*+ index(MISSING_REC_TB D_GSM_PROSP_NRECORDNR_IDX)*/
    trans_date, record_nr
     into v_start_date, v_min_rec
      from MISSING_REC_TB
    where record_nr =
    (
      select min(record_nr) from (
        (select /*+ PARALLEL(MISSING_REC_TB,12) */
            /*+ index(MISSING_REC_TB D_GSM_PROSP_NRECORDNR_IDX)*/
               trans_date, record_nr
                from MISSING_REC_TB
           WHERE trans_date >= v_start_date
             AND trans_date <= v_search_date_end
           order by record_nr)
        ))
    and trans_date >= v_start_date
    and trans_date <= v_search_date_end;
    dbms_output.put_line(to_char('v_start_date and min record no.: ' || v_start_date || ' ' || v_min_rec));     
--    dbms_output.put_line(to_char('v_end_date.: ' || v_end_date));     


/************************************************************************************************
      get max record nr from table huaweigsm_daily_recnr_tmp_tb
************************************************************************************************/
  v_search_date_end := v_end_date - 30/1440;
 
    select /*+ PARALLEL(MISSING_REC_TB,12) */
     /*+ index(MISSING_REC_TB D_GSM_PROSP_NRECORDNR_IDX)*/
    trans_date, record_nr
      into v_end_date, v_max_rec
      from MISSING_REC_TB
    where record_nr =
    (
      select max(record_nr) from (
        (select /*+ PARALLEL(MISSING_REC_TB,12) */
             /*+ index(MISSING_REC_TB D_GSM_PROSP_NRECORDNR_IDX)*/
               trans_date, record_nr
                from MISSING_REC_TB
           WHERE trans_date >= v_search_date_end
             AND trans_date <= v_end_date
            order by record_nr desc )
    ))
    and trans_date >= v_search_date_end
    AND trans_date <= v_end_date;



    v_record_nr   := v_min_rec;
    v_trans_date  := v_start_date;
   
   -- dbms_output.put_line(to_char('v_end_date and max record no.: ' || v_end_date || ' ' || v_max_rec));
 
 
/************************************************************************************************
      Perform while loop to check for each record number in the date range specified. 
      If a record number is not found, notify via mail and SMS that missing records exists.     
************************************************************************************************/

   WHILE (v_err_count <= 30 or v_trans_date <= v_end_date)
  LOOP
    --dbms_output.put_line(to_char('v_record_no v_err_count and v_trans_date .: ' || v_record_nr || ' '|| v_err_count || ' '||  v_trans_date));

   -- v_record_nr := v_find_record_nr;
    v_find_record_nr := 0;
   
  --  v_search_date_start := v_trans_date - 50/1440;
  --  v_search_date_end   := v_trans_date + 60/1440;
   
   
    v_search_date_start := v_trans_date - 50/1440;
    v_search_date_end   := v_trans_date + 30/1440;

   

      -- check if the next record can be found in the time range of the previous record timestamp
    -- less 50 minutes and previous record timestamp plus 1 hour.
 
      select /*+ PARALLEL(MISSING_REC_TB,12) */
        /*+ index(MISSING_REC_TB D_GSM_PROSP_NRECORDNR_IDX)*/
        trans_date, record_nr, file_name
      INTO v_trans_date, v_find_record_nr, v_file_name
       from MISSING_REC_TB
        where record_nr = v_record_nr
       and trans_date >= v_search_date_start
       and trans_date <= v_search_date_end;

      if v_find_record_nr = 0 then  -- record not found   

      -- could not find record - create error mail

dbms_output.put_line(to_char('record not found in if v_record_nr = 0  last variable values: v_find_record_nr v_search_date_start
   and v_search_date_end .: ' || v_find_record_nr || ' '|| v_search_date_start || ' '||  v_search_date_end));


      if v_first = 'F' then
        b := '';
        -- d := '';
        d := (TO_CHAR(   ' Daily Huawei Missing Record Nrs CDRGSM_RECORDCHECK_DAILY_SP from ' || v_record_nr
                              || ' ' || v_trans_date || ' ' || 'on file ' || v_file_name));
        v_first := 'T';
            v_err_count := v_err_count + 1;
            v_missing   := 'T';
      else
        b :=
        (TO_CHAR(   'Record Nr ' || v_record_nr || ' missing on file ' || v_file_name));
        c := (TO_CHAR (c || CHR (10) || b || CHR (10)));
            v_err_count := v_err_count + 1;
           v_missing   := 'T';
      end if;
    end if;

   
    v_record_nr := v_record_nr + 1;
    IF v_record_nr > 9999999
      THEN
      v_record_nr := 1;
    END IF;
   
   end loop;

      if v_missing = 'T' then
      a := TO_CHAR (sysdate, 'YYYY-MM-DD HH24:MI:SS');
      c := (TO_CHAR (a || CHR (10) || c));
      --d := (TO_CHAR (a || CHR (10) || d));

         quickmail ('sshikonde@mtc.com.na', ' Daily Huawei GSM Missing CDRs', c);

      end if;


EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
   dbms_output.put_line(to_char(' Daily No data found:  v_record_nr v_trans_date .: ' || v_record_nr || ' ' || v_trans_date ));
   NULL;
END MISSING_REC;


Thanks a lot for your support in advance!!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Detect missing records in a table and send mail

Postby Tim... » Wed Apr 23, 2014 11:59 am

Hi.

1) Shouldn't the first query just be a top-n query really?

Code: Select all

/************************************************************************************************
      get min record nr of the day from table daily_cdrgsm_copy_sp
************************************************************************************************/

 v_search_date_end := v_start_date + 30/1440;
 
 select trans_date, record_nr
 into v_start_date, v_min_rec
 from   (select trans_date, record_nr
         from   missing_rec_tb
         where trans_date >= v_start_date
         and trans_date <= v_search_date_end
         order by record_nr)
 where rownum = 1;


2) Looks like the same issue with the second query. Should be just a top-n right? Same query as before, with a different order.

/************************************************************************************************
get max record nr from table huaweigsm_daily_recnr_tmp_tb
************************************************************************************************/
v_search_date_end := v_end_date - 30/1440;

select trans_date, record_nr
into v_start_date, v_min_rec
from (select trans_date, record_nr
from missing_rec_tb
where trans_date >= v_start_date
and trans_date <= v_search_date_end
order by record_nr desc)
where rownum = 1;

3) Regarding your issue itself, add trace messages into the code, you you can see exactly what it is doing. You should be able to see what all parameter values are before you use them.

4) You have "SELECT ... INTO" in the loop, with no exception handler for NO_DATA_FOUND or TOO_MANY_ROWS. If the NDF fires, the code will jump out of the loop and go directly to the exception hander at the bottom...

Ignoring my comments on the top-n queries for the moment, you problem here is you are not putting trace messages (instrumentation) into your code, so you can;t see what is happening and therefore can't figure out why it is going wrong. Tom Kyte suggests that 50% of your code (every other line) should be a trace message. I think you need to read this...

http://www.oracle-base.com/articles/mis ... l-code.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest

cron