Hi Team,
I have a requirement for an alert mechanism, the details of which are provided below:
The 'data_records' table has a 'stores' column, and each store has a certain number of items. When the total items in a particular store, or in all stores, such that the 'eff_data' is greater than or equal to 20% of 'sysdate', an alert should be sent.
Consider the following stores: 123, 234, 345, and 456 (more stores may be added in the future as well).
Please help this Requirement ….
example
store no.of items Requiment Alert
123 100 eff_date>sysdate 20 items just sent alert(return -1 code this scripts should be failed) when one store or all store fall in the category
234 200 eff_date>sysdate 40 items
345 300 no issue
456 400 no issue
attached screenshot
PROMPT ***
PROMPT *** Data_alert.sql ***
PROMPT ***
WHENEVER OSERROR EXIT FAILURE ROLLBACK
SET FEEDBACK ON
SET SERVEROUTPUT ON
SET TIMING ON
SPOOL OFF
SET TERMOUT ON
SET VERIFY OFF
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET optimizer_dynamic_sampling=0;
PROMPT ***
SELECT '*** Starting Data_alert.sql @:'|| TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM DUAL;
PROMPT ***
WHENEVER SQLERROR CONTINUE
---select COUNT(*) from Data_records where trunc(eff_date)>='10-SEP-23';
Declare
v_alert_threshold NUMBER := 0.20; -- 20%
BEGIN
-- Check if 'eff_data' for any store is greater than or equal to 20% of 'sysdate'
FOR rec IN (SELECT store_id, eff_date FROM Data_records) LOOP
IF rec.eff_data >= (SYSDATE * v_alert_threshold) THEN
-- Alert action: replace with your desired alert action
DBMS_OUTPUT.PUT_LINE('ALERT: Store ' || rec.store_id || ' has eff_data >= 20% of sysdate.');
END IF;
END LOOP;
END;
/
COMMIT;
UNDEF 1 2 3
QUIT;
Thanks
