updating a table automatically at a particular time

[amolso]

Hello Friends

I Have a table in which I am storing a two columns
'Start_date and 'End_date' where 'start_date' is entered
as greater than sysdate and end_date greater than
start date. Also I have Start_Time which is again a
date field.
There is another column in same table called 'Status'
This column's value should be modified to 'open' at
the specified 'Start_Date' and on that 'Start_Time'
By the system itself. This currently done manually on
SQL prompt by me. Is there any method or procedure or
package which will automatically update the record to open
when specified time or date is reached

Amol

 

[MikeJones]

Crikey that's a good one!! I think you will have to use DBMS_JOB to solve this. You could put a trigger on the table so that on insert (or update for that matter) it spawns a DBMS_JOB to update the column. You can give a time to DBMS_JOB as well so it will run at a specified time.

 

[amolso]

Well A trigger will do it when a particular user updates
the table. I want that update to take place directly.

Do you mean that I have to activate any function of the
DBMS_JOB package in that trigger
OR
I have to activate that Packaged Function externally
like exec dbms_job. ...
Can you tell me that procedure or function with example
as I am not aware of this package much.

Thanks in advance
Amol

 

[MikeLacey]

Amol -- you on unix?
Mike
michael.j.lacey@ntlworld.com

 

[RandyD]

Maybe your status column is not really appropriate. Is it a derived value? in other words, do you always know by rules associated with the dates what the status should be?

In this case, you can just select a status text based on the date rules at query time. This could be a callable function that implemented the appropriate rules.

hth
Randy

 

[amolso]

I am not on unix but on NT SERVER
Randy , I am not really clear about question which you
have asked. But I would Like to tell you that my status
column is a numeric field i.e. id which refers to
Status Master where I have id is primary key and status
as description like :
699 created
700 open
701 Closed

I am storing id in my auction table which is 699
and start_date, start_time are date values.
I want the table to be updated automatically when
sysdate equals the specified date, time.
I think there is a need of a backgroung process which will
attempt to update my auction table.(This is what I think
not necessarily correct)

Is there anything procedure which will keep alive a particular procedure in backgroung to check system time.
If it's there how can I integrate it with user procedure

AMOL

 

[MikeJones]

Amol,

we use DBMS_JOB alot to automatically schedule jobs. we run Oracle on Unix, but I see no reason why NT should implement it differently.

I've just double checked the syntax of DBMS_JOB at :

http://durga.uits.indiana.edu:8000/ora_docs_816/doc/index.htm

You could do this in your trigger

jobno number;
BEGIN
DBMS_JOB.SUBMIT(jobno,
'UPDATE my table set status = '||:new.status||' where pk = '||:new.pk'
:new.start_time, NULL);
commit;
END;
/

Chek out the web page above it's quite straight forward to use. You can specify the run time down to the nearest second.

HTH

Mike.

 

[RandyD]

a followup point...

what I meant was, if you use a rule during your manual update that says for example: if start_dt and start_tm (why these are two seperate columns is another question) are older than sysdate, and end_dt is null, then assign the status of 700 (Open) to the record, then you can do the same type of logic at query time, without having to update the record in the first place. (and no status column is required because the status is derived from the dates & times)

you can build a union query like this:

SELECT *, 700 FROM tab WHERE start_dt < sysdate AND end_D IS NULL
UNION
SELECT *, 701 FROM tab WHERE end_dt IS NOT NULL

this gives valid statsu at query time without the need to update the table with the status column info.

hope this explains my point a little better.

Randy