Hi,

I have come accross the following challenge. I want to run a script everytime to pick up new information in a table according to the urgency and this should be in the form of a queue. Data with the status urgent should be executed first. I have two tables with the following structures:


CREATE TABLE "CDRUSER"."PTS_TRANSACTION_TB"
( "TICKET_NO" NUMBER NOT NULL ENABLE,
"STATUS" VARCHAR2(10 BYTE),
"USERNAME" VARCHAR2(50 BYTE),
"TOWN" VARCHAR2(20 BYTE),
"CASE_NO" VARCHAR2(20 BYTE),
"CASE_TYPE" VARCHAR2(20 BYTE),
"UNIT" VARCHAR2(20 BYTE),
"OFFICER_NO" VARCHAR2(20 BYTE),
"CERT_NO" VARCHAR2(20 BYTE),
CONSTRAINT "PTS_TRANSACTION_TB_PK" PRIMARY KEY ("TICKET_NO"))

CREATE TABLE "CDRUSER"."PTS_REQUESTS_TB"
( "TICKET_NO" NUMBER NOT NULL ENABLE,
"IMEI" VARCHAR2(15 BYTE),
"MSISDN" VARCHAR2(14 BYTE),
"START_DATE" DATE,
"END_DATE" DATE,
"SUBMITTED_ON" DATE,
"PROCESSED_ON" DATE,
"STATUS" VARCHAR2(10 BYTE),
CONSTRAINT "PTS_REQUESTS_TB_PK" PRIMARY KEY ("TICKET_NO"))

the structure of my output table is:


CREATE TABLE "CDRUSER"."PTS_MSISDN_REQUESTS_TMP"
( "TRANSACTION_DATE" VARCHAR2(20 BYTE),
"CALL_TYPE" VARCHAR2(20 BYTE),
"MSISDN" VARCHAR2(20 BYTE),
"IMEI" VARCHAR2(15 BYTE),
"CALL_DURATION" NUMBER(4,0),
"CALLING_NR" VARCHAR2(35 BYTE),
"CALLED_NR" VARCHAR2(35 BYTE),
"ORIGINATION_CALLED_NR" VARCHAR2(35 BYTE),
"INCOMING_SMS" VARCHAR2(22 BYTE),
"OUTGOING_SMS" VARCHAR2(25 BYTE),
"GLOBAL_CELL_ID" VARCHAR2(4000 BYTE)
)

My procedure is as follow:

create or replace procedure pts_msisdn_request_sp (rstart_date varchar2,rend_date varchar2, rmsisdn varchar2, rimei varchar2)

is
BEGIN

insert into pts_msisdn_requests_tmp(transaction_date, call_type, msisdn, imei, call_duration, calling_nr, called_nr, origination_called_nr, incoming_sms, outgoing_sms, global_cell_id)
select /*+ index(CDRGSM_RISK_UNMASKED_VW cdrgsm_rmsisdn_idx )*/
transaction_date, call_type, msisdn, imei, callduration, calling_nr,
called_nr, origination_called_nr, incoming_sms, outgoing_sms, global_cell_id
from cdruser.CDRGSM_RISK_UNMASKED_VW@MTCCDRT
where TRANS_DATE >= to_date(rstart_date, 'dd-mon-rrrr hh24miss')
and TRANS_DATE <= to_date(rend_date, 'dd-mon-rrrr hh24miss'
)
and MSISDN = to_char(rmsisdn)
or imei = to_char(rimei);

commit;

end;

What should happens is, whenever there is a new entry in the PTS_REQUESTS_TB, the procedure should execute according to the values entered. But first it should execute the entry with a status urgent and once done executes the next urgent request and later on the request with the normal status (some thing like a priority queue). Can you please help to correct me on my procedure and how i can implement a solution so that it can keep checking if theres anything to be executed?

Kindly assist please!

QUEUES are always First In, First Out & therefore do NOT support any type or prioritizing.

The closest mechanism within Oracle would be a TRIGGER

Good Luck, because I suspect you'll need it.

It is possible to make stored procedure with priorities.

Thanks anyway!

>It is possible to make stored procedure with priorities.
no

Can you then please help me with the scenario you think is possible?

I need to teach my goat to fly.
Can you then please help me with the scenario you think is possible?