Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Analytic Query Help

580950Feb 10 2008 — edited Feb 11 2008
My table has student grades by date. For each student, I need to return the date of the most recent F, and the number of consecutive F's the student had made at that time (NOT the total number of F's). So, for the table data below, I want the sql to return...

JANE 16-NOV-07 2
BILL 23-APR-07 4


NAME DATE GRADE
JANE 08-Feb-08 B
JANE 28-Dec-07 B
JANE 16-Nov-07 F
JANE 05-Oct-07 F
JANE 24-Aug-07 C
JANE 13-Jul-07 C
JANE 01-Jun-07 C
JANE 20-Apr-07 C
JANE 09-Mar-07 A
JANE 26-Jan-07 F
JANE 15-Dec-06 B
JANE 03-Nov-06 B
JANE 22-Sep-06 F
JANE 11-Aug-06 F
JANE 30-Jun-06 F
JANE 19-May-06 F
JANE 07-Apr-06 C
JANE 24-Feb-06 C
JANE 13-Jan-06 C
BILL 28-Jan-08 C
BILL 03-Dec-07 C
BILL 08-Oct-07 B
BILL 13-Aug-07 B
BILL 18-Jun-07 B
BILL 23-Apr-07 F
BILL 26-Feb-07 F
BILL 01-Jan-07 F
BILL 06-Nov-06 F
BILL 11-Sep-06 C
BILL 17-Jul-06 C
BILL 22-May-06 F
BILL 27-Mar-06 F
BILL 30-Jan-06 F

Comments

User_H3J7U

The queue triggered event job only when the enqueue occurs. Moreover, if a new enqueue occurs during the job execution, the scheduler will skip that event.
Simplest way is to start a single non-event job with a continuous dequeuing. Or enable multiple event job instances and dequeue all messages on startup. The third option is to register the pl/sql callback, but you still need to read messages on startup. Pl/sql callback is starting up to 20 processes on simultaneous enqueue.

murali rishna

i enable this one as well, still ,it is not working.
DBMS_SCHEDULER.SET_ATTRIBUTE('<<jobname>>','parallel_instances',TRUE);
As per your comment " enable multiple event job instances and dequeue all messages on startup. "
do you have sample,please share it?

User_H3J7U

Parallel_instances is needed to solve this problem_:_
Note:
The Scheduler runs the event-based job for each occurrence of an event that matches event_condition. However, by default, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job. Beginning in Oracle Database 11g Release 1 (11.1), you can change this default behavior by setting the job attribute PARALLEL_INSTANCES to TRUE. In this case, an instance of the job is started for every instance of the event, and all job instances are lightweight jobs. See the SET_ATTRIBUTE procedure in Oracle Database PL/SQL Packages and Types Reference for details.
How to dequeue messages from queue, you can find in the documentation Advanced Queuing User's Guide (pl/sql). See also Administrators Guide about event based jobs.

murali rishna

i tried with parallel instance,it is not working.still event are the in que table.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 10 2008
Added on Feb 10 2008
9 comments
1,353 views