CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqlprocedure
user230
user230

Reputation: 1

Oracle stored procedure that can auto remove records after 24 hours

Can a procedure auto triggered after 24 hours and remove records from a specific table?

I tried, but as a beginner I'm not able to do it.

Here is my code:

CREATE OR REPLACE PROCEDURE deleteresetlinks ("2" INT)
AS BEGIN  
    DELETE FROM passwordresetlink 
    WHERE DATEDIFF(DAY, 'date_created', GETDATE()) > 2;
END;

This code is throwing this error:

SQL> show errors
Errors for PROCEDURE DELETERESETLINKS:

LINE/COL ERROR

3/3 PL/SQL: SQL Statement ignored
3/39 PL/SQL: ORA-00904: "DATEDIFF":
invalid identifier

Upvotes: 0

Views: 1443

Answers (2)

Littlefoot
Littlefoot

Reputation: 142993

This is how I understood the question.

Sample data:

SQL> select * from passwordresetlink
  2  order by date_created;

        ID DATE_CREATED
---------- ---------------
         1 10.06.2021
         2 14.06.2021

Procedure is supposed to remove rows older than 2 days; now, that can be modified so that it includes time component or - as in my example - have it set to midnight, but that doesn't really matter.

SQL> create or replace procedure p_del_resetlink
  2  is
  3  begin
  4     delete from passwordresetlink
  5       where date_created < trunc (sysdate) - 2;
  6  end;
  7  /

Procedure created.

This is what answers your question, i.e. how to "auto-trigger" that procedure. Create a database job. I'm on 11g and this is the DBMS_JOB example because task is really simple - run the procedure once a day. I set it to 3 o'clock in the morning (so yes, it is a nightly job). Consider using DBMS_SCHEDULER instead.

SQL> set serveroutput on
SQL> declare
  2     newjobid  number;
  3  begin
  4     sys.dbms_job.submit (
  5        job        => newjobid,
  6        what       => 'p_del_resetlink;',
  7        next_date  => to_date ('16.06.2021 03:00:00', 'DD.MM.YYYY HH24:MI:SS'),
  8        interval   => 'TRUNC(SYSDATE + 1) + 3/24',
  9        no_parse   => false);
 10     sys.dbms_output.put_line ('Job Number is: ' || to_char (newjobid));
 11     commit;
 12  end;
 13  /
Job Number is: 2983310

PL/SQL procedure successfully completed.

OK, let's run it manually:

SQL> exec dbms_job.run(2983310);

PL/SQL procedure successfully completed.

SQL> select * from passwordresetlink;

        ID DATE_CREATED
---------- ---------------
         2 14.06.2021

SQL>

Looks OK; row that was created more than 2 days ago is now deleted.

How does the job look like itself?

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';

Session altered.

SQL> select job, what, last_date, next_date from user_jobs where job = 2983310;

       JOB WHAT                 LAST_DATE        NEXT_DATE
---------- -------------------- ---------------- ----------------
   2983310 p_del_resetlink;     15.06.2021 09:19 16.06.2021 03:00

SQL>

It was last executed a few minutes ago (today at 09:19) and will be next executed tomorrow at 03:00 (and every day after that, again at 03:00).

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18685

A good place to start is the documentation. Oracle has its own datatypes and operators, you can't just borrow them from other programming languages or databases and hope it works. DATEDIFF is a mysql function, it does not exist in oracle. I think you are looking for something like this:

CREATE OR REPLACE PROCEDURE deleteresetlinks (age_i IN NUMBER DEFAULT 1)
AS BEGIN  
  DELETE FROM passwordresetlink WHERE date_created < SYSDATE - age_i;
END;

In your original case, the parameter declaration ("2" INT) creates a parameter with a name "2" - that is very confusing, probably not what you intended. I replaced that with a more descriptive name "age_i" - using the suffix "_i" to indicate that it is an IN parameter. I'm assuming that the column "date_create" is of type date, in which case you can use simple date arithmic to calculate a difference of days. SYSDATE - 1 represents 24 hours ago.

Upvotes: 1

Related Questions