CODEWITHSUNDEEP
automationoracle11g
Pdksock
Pdksock

Reputation: 1030

Automate tasks in Oracle11g

I am creating a database for an auction in which I have a table that stores the entries of Items that are put up for auction.

create table items(
item_ID number(5) PRIMARY KEY,
SDate DATE NOT NULL,
EDate DATE NOT NULL, //end date for auction
minBid number(5,2) NOT NULL,
bidInc number(2,2) NOT NULL,
title varchar2(20) NOT NULL,
descr varchar2(255),
currentBid number(5,2)
)

Now after the end date has passed I want the database to automatically delete that entry from the table.
How do I do it?

Upvotes: 2

Views: 124

Answers (2)

Pdksock
Pdksock

Reputation: 1030

All those people who are stuck in a similar solution here's how you do it.

step 1:create procedure

create procedure item_delete as
begin
delete from items where edate<sysdate
end;

step 2:automate using DBMS_SCHEDULER

DBMS_SCHEDULER.create_job (
job_name        => 'delete_old_items',
job_type        => 'STORED_PROCEDURE',
job_action      => 'exec item_delete',
start_date      => sysdate,
repeat_interval => 'freq=daily; byhour=0;byminute=0;bysecond=0;',
end_date        => NULL,
enabled         => TRUE
);
END;


references
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1000363

Upvotes: 2

Artless
Artless

Reputation: 4568

Well, you could write a job in the Database what will delete rows older than X time, but since you tagged this as C# I'm guessing you want it done in your auction application.

In that case, you could create a Timer that will run a DELETE FROM items WHERE EDate > XXX query every time it runs.

EDIT: In that case, you can define a Stored Procedure that will delete old rows, and then you'll need to schedule it with a DBMS_JOB. I don't have an Oracle DB at hand to test this, but you could try running this:

Create a stored procedure:

create or replace procedure delete_old_auctions
as
begin
    delete from items where edate < sysdate
end;

Then you need to create a job that will execute this stored procedure every X time.

dbms_job.submit(
    what=>'your_db.delete_old_auctions',
    next_date=>sysdate+1/24,    -- start next hour
    interval=>'sysdate+1/24');  -- Run every hour

Upvotes: 3

Related Questions