1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Procedure to send an output into table based on priority

Discussion in 'SQL PL/SQL' started by sellyh19, Nov 23, 2011.

  1. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    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. tj.abrahamsen

    tj.abrahamsen Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Utah
    Hey there -

    A little bit late, but are you looking for trigger functionality? If you create a trigger on new entry in your PTS_REQUESTS_TB, you can in the trigger an insert into your other table through a call to your procedure.

    ~ TJ
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please provide the DDL for CDRGSM_RISK_UNMASKED_VW.