Oracle Question

oraclepaul5

I need some help with an Oracle Database I am working on. I am currently using Application Express and have a question.

At the moment I have a database that records a time and sets a urgency rating from 4 - 1 on a particular item. 4 being the lowest and 1 being the highest urgency level. When the time on the item at level 4 goes over 24 hours it must be moved to level 3. When an item at level 3 has been going for over 6 hours then it must be moved to level 2 and finally when an item at level 2 has been at level 2 for more then an hour it must be moved to level 1...

I have talked to a couple of people so far on this subject and they said that without alot of codeing and alteration to my current database it could not happen automatically but I was given the idea of a button being pressed and comparing the current system time to the time when these items were recorded. Then comparing the two times and the level of urgency and then moving or not moving the urgency number accordingly.

This is just one idea, if anyone has any idea on how to do this I would very much apretiate any help, if you need any more information on what I am doing I would be more then happy to share it with you.

Thanks for reading this Post

oraclepaul5.

sleep2much

ever thought of using google?

oraclepaul5

thanks but google didnt have the answer I was looking for, that's why I tried here for something slightly more in-depth.. This is for my university course and google didnt really have the type of answer I was looking for. I was hopeing that somebody who was experienced in oracle would be able to lend me a hand.

isofa

Depends how you can control the events, I'm not that familar with Oracle, but I am with many other RDBMSs

Whichever way you do this, you'll obviously have to compare the item time with the system current time. You could do it via a button, or you could have some sort of timed loop running which after every hour (for example) compared the system time to the time of all records, and moved items to a new urgency rating when required.

If you can't find support for a timed loop, what about an external script from another app or the OS which calls the procedure within the application itself?

sleep2much

http://forums.oracle.com/forums/forum.jspa?forumID=14&start=0 could be a good place to look?

Lakeuk

I'd have it worked out on the fly when the data is required and not stored in the database for level details, otherwise it'll be overkill updates to the table which while ok for a demo app will be no good for a proper application.

Syntax not tested but gives the general idea, all from a little bit of google:-

SELECT
(CASE
WHEN round(to_number(end_date-start_date)*24) BETWEEN 1 AND 2 THEN 4
WHEN round(to_number(end_date-start_date)*24) BETWEEN 2 AND 3 THEN 3
WHEN round(to_number(end_date-start_date)*24) BETWEEN 3 AND 4 THEN 2
WHEN round(to_number(end_date-start_date)*24) BETWEEN 4 AND 5 THEN 1
ELSE 'N/A'
END) LEVEL
FROM table;

Blacksheep1979

if it's on a linux host then set a simple cron job to fire a stored proc checking and updating the fields as necessary. Either that or a simple perl script left running in the background doing similar.

motorguy

Yes i agree with Blacksheep, either linux or unix, i'd use a cron job to kick off a procedure at the desired interval. Use tasks if you're unfortunate enough to be running oracle on windows.

You could either use a cursor loop for the whole table or update where status = 4 and time > sysdate+24 or something like that.

ManAtHome

As Blacksheep said, all of the commercial workflow systems I've seen use some sort of recurrent task to change status. Typical applications are "customer contact" (aka "complaint") and helpdesk systems where you want to escalate if the problem hasn't been solved.

SandLake

Oracle have a dedicated Apex forum - http://forums.oracle.com/forums/forum.jspa?forumID=137

and if you are using a stored procedure then you don't need to use cron to schedule it, you can use the Oracle scheduler to run it every hour (for example)

oraclepaul5

Thanks very much for all the help, this has all helped me a huge amount.