Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    12

    Unanswered: Procedure to send an output into table based on priority

    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!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2011
    Posts
    12
    It is possible to make stored procedure with priorities.

    Thanks anyway!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It is possible to make stored procedure with priorities.
    no
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2011
    Posts
    12
    Can you then please help me with the scenario you think is possible?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I need to teach my goat to fly.
    Can you then please help me with the scenario you think is possible?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •