Monitoring a table attribute with stored procdeure

Question

I am working on a stored procedure that monitors the Last_Extract_Ts value in the table that provides information about Extract Transform Load(ETL). Now I want to check whether the Last_Extract_ts value changed from the last time the procedure ran, but I can't quite figure out how to store the result of the last procedure run so that I can use it in the current one.

Below is my procedure

create or replace PROCEDURE MONITOR AS


  v_count               number:=0;
  v_Last_Extract_Ts VARCHAR2(80) := '';
  v_Last_ETL_Run VARCHAR2(80) := '';
  BEGIN

select count(*) into v_count from oms_etl_config where ATTR_NM='last_extract_ts' and process_type='upd' and ATTR_VALUE<=to_char(sys_extract_utc(systimestamp)-5/1440,'YYYY-MM-DD HH24:MI:SS');
select Attr_value into v_Last_Extract_Ts from OMS_ETL_CONFIG where PROCESS_TYPE='upd' AND ATTR_NM='last_extract_ts';
Select MAX(START_TS) into v_Last_ETL_Run from  OMS_ETL_AUDIT;

dbms_output.put_line(v_count);
dbms_output.put_line(v_Last_Extract_Ts);
dbms_output.put_line(v_Last_ETL_Run);


END;

I came across something like storing the result of the stored procedure in a temp table in Insert results of a stored procedure into a temporary table , Exec stored procedure into dynamic temp table but I can't quite see how it meets my needs.

Is what I am trying to achieve possible or I need to have a different approach.

Thanks in advance.

P.S. I am absolute beginner with PL/SQL and stored procedures so I am not having any attempt in my post to show for the research I have done. Sorry for that.

@Aditya it's Oracle PL-SQL and not SQL server t SQL could u edit the tag — Shubham Khatri, Sep 30 '16 at 07:55
You can create a trigger on your table and check that if the value changes during this run. In that case you can raise a message — XING, Sep 30 '16 at 08:33
Also what kind of operation you need to do it with old and new values — XING, Sep 30 '16 at 08:42
@XING If the values don't change in consequtive three run of a stored procedure which run after every 5 mins then I need to send a warning mail — Shubham Khatri, Sep 30 '16 at 08:44
Perfect, then see my trigger in the answer. You need a trigger for that not any procedure — XING, Sep 30 '16 at 08:44

schurik · Accepted Answer · 2016-09-30T08:55:17.293

1

The simplest way is to save the last results in a table.

create a table:

Create table monitor_results 
( 
  last_run_date          date
, last_Last_Extract_Ts  varchar2(80)
, last_ETL_Run          varchar2(80)
, last_count            NUMBER
);

initialize values:

insert into monitor_results values (NULL, NULL, NULL, NULL);
commit;

in the stored procedure update the values in the table:

...

update monitor_results
set 
      last_run_date         = SYSDATE
    , last_Last_Extract_Ts  = v_Last_Extract_Ts
    , last_ETL_Run          = v_Last_ETL_Run
    , last_count            = v_count
;
commit;

edited Sep 30 '16 at 08:55

answered Sep 30 '16 at 08:45

schurik

7,798
2
23
29

thanks for your answer but I am not granted permission to create a new table in the database. I can see if I can use you approach, This is what I thought at first but abandoned it. – Shubham Khatri Sep 30 '16 at 08:49

score 1 · Answer 2 · answered Sep 30 '16 at 08:46

1

You can do this check using a trigger: See below:

CREATE OR REPLACE TRIGGER reporting_trigger
   AFTER UPDATE ON <Table>
   FOR EACH ROW
BEGIN
 /**Your column which holds the record**/
IF :new.a = :old.a THEN 
   raise_application_error( -20001, 'This is a custom error' );

 END IF;


END;

answered Sep 30 '16 at 08:46

XING

9,608
4
22
38

Do you mean I should drop using a procedure and use a trigger instead – Shubham Khatri Sep 30 '16 at 08:47
Yes.,So here in the example lets say if i insert a record. And next time when there is a update with the same value you will get a error message – XING Sep 30 '16 at 08:48
Okay, just one question. If the ETL stops working then it doesn't put any entry intro the Table and thats the case when Last_Extract_ts doesnt change, so how does this trigger work then. I can see it fires on every update on the table – Shubham Khatri Sep 30 '16 at 08:52
Thats the place where you can make change. Create an autonomous transaction which inserts a record to the table even when ETL fails. Ofcourse the trigger will only fired when its updated with the same value. – XING Sep 30 '16 at 08:55
Thanks for all the help.It looks a nice approach I will try that and mark you answer accepted when it works. – Shubham Khatri Sep 30 '16 at 08:59
Okay so this is a feasible approach, Is it possible for a job to schedule a trigger timely just like a procedure. If so can you point me to a link – Shubham Khatri Sep 30 '16 at 09:22
Trigger is invoked when some activity defined occurs on table. Why do you want to schedule it. If you really want to schedule, schedule the activity which is modifying the table. Use DBMS_SCHEDULER .https://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin006.htm – XING Sep 30 '16 at 09:29
Thanks for the help :) – Shubham Khatri Sep 30 '16 at 09:33

Monitoring a table attribute with stored procdeure

2 Answers2