CODEWITHSUNDEEP
oracle-databaseoracle10goracle11g
Peter Penzov
Peter Penzov

Reputation: 1712

Oracle - How to write simple PL/SQL procedure

I need to create a table with sessions. New users who are successfully authenticated will be added to the table. Every time when the users are opening the pages the user will checked in this table. So I want to use PL/SQL procedure which will run every minute and delete sessions older than 30 minutes to flush the "death" sessions.

Or maybe executing DELETE FROM "Dates" WHERE "Date" < SYSDATE - 1/48; every time when pages are reloaded will be a more optimized solution?

Upvotes: 0

Views: 1164

Answers (3)

jim mcnamara
jim mcnamara

Reputation: 16399

SQL

delete from mytable
  where mytable.datefield < sysdate - .02083333333333333333;
commit;

.02083333333333333333 is 1800 / 86400 which is 30 minutes in seconds / seconds in a day

If you are writing a trigger or a stored procedure you would use PL/SQL. Please elaborate a little more.

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

delete 
from table
where date_column < sysdate - 30/1440;

1 is a day

1/1440 is a minute

30/1440 equals 30 minutes

Upvotes: 3

Krzysztof
Krzysztof

Reputation: 16150

You don't need procedure to do that. Simple delete query is enough.

DELETE FROM "Dates" WHERE "Date" < SYSDATE - 1/48;

If you necessarily need procedure, you should wrap this query into proc.

Upvotes: 1

Related Questions