CODEWITHSUNDEEP
oracle-databaseloopsplsqloracle11g
Integrated Gate
Integrated Gate

Reputation: 11

automated alert emails using sql developer

A supermarket has many products. When one of these products reaches a quantity of zero an automated email needs to be sent to the manager, showing that this product is out of stock.

I have done the email part( sending email through SQL Developer ). Now I need to set up a loop to keep tracking the products' quantity. How is this loop called? .

APC? i tried this out. but its not working for me 

create or replace
procedure check_stock_qty
begin      
for r in ( select product_name,product_id from super_market 
where pro_qty = 0 )  
loop       
UTL_MAIL.send(sender => '[email protected]',
        recipients => '[email protected]',
         subject => 'Test Mail',
         message => ( r.product_name ),
      mime_type => 'text; charset=us-ascii');  

end loop; 
end;
------------------------
BEGIN    

dbms_scheduler.create_job (job_name => 'stock check',
job_type        => 'PLSQL_BLOCK',        
job_action      => 'BEGIN check_stock_qty; END;',    
start_date      => SYSTIMESTAMP,       
repeat_interval => 'freq=minutely; interval=5; bysecond=0;',    
end_date        => NULL,         
enabled    => TRUE,
END;

the procedure compiled, but win run it. it gives an error " the selected program is in an invalid state for running. recompile the program and try again "

Upvotes: 1

Views: 10010

Answers (1)

APC
APC

Reputation: 146239

The best way to do this would be to use a database job to periodically check the PRODUCTS table.

First of all you need a stored procedure. Something like this:

create or replace procedure check_stock_qty
begin
     for r in ( select product_name from products
                where qty = 0 )
     loop
         your_email_proc_here ( r.product_name );
     end loop;
end;

You would then set this to run at regular intervals. As you're using Oracle 11g you should use the DBMS_SCHEDULER API to do this. This calll will run the above stock checker every five minutes:

BEGIN
    dbms_scheduler.create_job (
        job_name        => 'stock check',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN check_stock_qty; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
        end_date        => NULL,
        enabled         => TRUE,
END;
/

DBMS_SCHEDULER is pretty sophisticated i.e. complicated but it is well documented. Find out more.

Upvotes: 2

Related Questions