Escalation module with Oracle 9i and Oracle Forms 6i

25 pts.
Oracle 9i
Oracle development
Oracle Forms 6i
We are developing customer care software working on Escalation Module. Here my system should generates an email to executive or technical head if complaint doesn't get resolved within 3-hrs. I'm using Oracle 9i and forms 6i as front end. I have two points to be discussed.

  1. If complaint occurs at 10.00 am, so it should resolved by 1.00 pm. How do I calculate time difference between time limit and complain log in time? (please write query) I'm working on following query, its giving me INVALID NO ERROR. * select to_char(sysdate,'hh24:mi:ss')-to_char(to_date(21-may-09),'hh24:mi:ss') from tablename;
  2. Shall I go for trigger for sending an email? If yes, which kind of trigger on which event? How?
Please guide me. I'm new to Oracle. Thanks in advance. Eagerly waiting for reply.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hello Firasha.

You asked the same question a few days ago. If the answer provided was not useful, it would have been better to continue the discussion in the original question instead of creating a new one.

You are getting the INVALID NUMBER error because you are trying to perform an arithmetic operation between two varchar variables.

To calculate time differences you need to have your time values in DATE columns, there is no need to convert them to varchars.


SELECT sysdate - complaint_log_time FROM your_table;

If you want the result in hours, just convert it:

SELECT (sysdate - complaint_log_time)*24 FROM your_table;

To check whether the complaint was resolved at the time limit, you would need an application to keep monitoring the time limits for each complaint, or you could schedule a job, using the DBMS_JOB database package.

A trigger won’t work because triggers fire when something happens in the database, not when something does not happen.

As for how to send the e-mail, a couple of links were provided in the answer to the original question.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Firasha
    Thanks A lot for u r Valuable Guidance. As i am new with this site so i didn't know the procedure of asking the question . so i am really sorry for that. i will keep in mind u r all suggestion. well, thnx once again . its great help for me. Thnx a lott, Anjum.
    25 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: