General
======================
Code Example: PL/SQL code that parse a string with delimiter into a table, and epoch time date conversion.
The results are stored in a table.
======================
Files
======================
PARSE_CAMPAIGN_PKG.sql
CAMPAIGN_SUBSCRIBERS.sql
======================
Code
======================
CAMPAIGN_SUBSCRIBERS.sql
CREATE TABLE CAMPAIGN_SUBSCRIBERS(
campaign_id NUMBER(22),
imsi VARCHAR2(30),
msisdn VARCHAR2(30),
campaign_date DATE
) TABLESPACE IGT_TABLE;
PARSE_CAMPAIGN_PKG.sql
---------------------------------
-- A code that parse campaign_history with id 102 from format
--1534249100:102:20624094159360-20624094159361-20624094159362:89:1;
--1533638521:102:20624089860956-20624089860958-20624089860959:89:1;
--1533586549:102:20624089281354-20624089281363-20624089281364:218:1;
--etc...
--epoc time:102:other_info:xxx:1;
-- To a table.
---------------------------------
CREATE OR REPLACE PACKAGE PARSE_CAMPAIGN_PKG IS
PROCEDURE parse_by_campaign_id (p_capmaign_id IN CAMPAIGN_SUBSCRIBERS.campaign_id%TYPE,
p_start_date_epoch IN VARCHAR2,
p_end_date_epoch IN VARCHAR2);
END PARSE_CAMPAIGN_PKG;
/
CREATE OR REPLACE PACKAGE BODY PARSE_CAMPAIGN_PKG IS
----------------------------------------------
PROCEDURE write_log(p_module_name SGA_W_LOG.procedure_name%TYPE,
p_text SGA_W_LOG.data%TYPE) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SGA_W_LOG (procedure_name, data, ts_last_modified)
VALUES (p_module_name, p_text, sysdate);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END write_log;
PROCEDURE cre_entry_campaign_sub (p_subscriber_rec IN SGA_W_PSMS_SUBSCRIBER%ROWTYPE,
p_campaign_id IN CAMPAIGN_SUBSCRIBERS.campaign_id%TYPE ,
p_campaign_date IN CAMPAIGN_SUBSCRIBERS.campaign_date%TYPE) IS
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
BEGIN
v_module_name := 'cre_entry_campaign_sub';
INSERT INTO CAMPAIGN_SUBSCRIBERS(campaign_id, imsi, msisdn, campaign_date )
VALUES (p_campaign_id, p_subscriber_rec.imsi, p_subscriber_rec.msisdn, p_campaign_date);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
write_log(v_module_name, v_msg_text);
RAISE;
END cre_entry_campaign_sub;
----------------------------------------------
-- 1531864267:99:17535087551370-17535087551371-17535087551372-17535087551373:1162:1;
----------------------------------------------
PROCEDURE handle_campaign (p_subscriber_rec IN SGA_W_PSMS_SUBSCRIBER%ROWTYPE,
p_campaign_text IN VARCHAR2,
p_campaign_id IN CAMPAIGN_SUBSCRIBERS.campaign_id%TYPE ,
p_start_date_epoch IN VARCHAR2,
p_end_date_epoch IN VARCHAR2) IS
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
v_epoch_date VARCHAR2(100);
v_text_delimiter VARCHAR2(1);
v_campaign_date DATE;
BEGIN
v_module_name := 'handle_campaign';
v_text_delimiter := ':';
v_epoch_date := SUBSTR(p_campaign_text,1, INSTR(p_campaign_text,v_text_delimiter)-1);
IF (p_start_date_epoch <= v_epoch_date) AND (v_epoch_date <= p_end_date_epoch) THEN
--24*60*60*1000 = 86400
v_campaign_date := TO_DATE('19700101','YYYYMMDD') + (1/86400 * v_epoch_date);
cre_entry_campaign_sub(p_subscriber_rec, p_campaign_id, v_campaign_date);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
write_log(v_module_name, v_msg_text);
RAISE;
END handle_campaign;
----------------------------------------------
PROCEDURE handle_subscriber (p_subscriber_rec IN SGA_W_PSMS_SUBSCRIBER%ROWTYPE,
p_campaign_id IN CAMPAIGN_SUBSCRIBERS.campaign_id%TYPE,
p_start_date_epoch IN VARCHAR2,
p_end_date_epoch IN VARCHAR2) IS
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
v_campaigns_list SGA_W_PSMS_SUBSCRIBER.campaign_history%TYPE;
v_curr_campaign SGA_W_PSMS_SUBSCRIBER.campaign_history%TYPE;
v_search_campaign VARCHAR2(100);
v_campaign_delimiter VARCHAR2(1);
v_next_campaign_index NUMBER;
BEGIN
v_module_name := 'handle_subscriber';
v_campaign_delimiter := ';';
v_next_campaign_index := 1;
v_campaigns_list := p_subscriber_rec.campaign_history;
v_search_campaign := ':'||TO_CHAR(p_campaign_id)||':';
-- v_campaigns_list_curr := v_campaigns_list;
WHILE v_next_campaign_index > 0 LOOP
v_next_campaign_index := INSTR(v_campaigns_list,v_campaign_delimiter);
v_curr_campaign := SUBSTR(v_campaigns_list,1,v_next_campaign_index);
v_campaigns_list := SUBSTR(v_campaigns_list,v_next_campaign_index+1);
IF INSTR(v_curr_campaign,v_search_campaign) > 0 THEN
handle_campaign(p_subscriber_rec, v_curr_campaign, p_campaign_id, p_start_date_epoch, p_end_date_epoch);
END IF;
IF v_campaigns_list = ';' THEN
v_next_campaign_index := 0;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
write_log(v_module_name, v_msg_text);
RAISE;
END handle_subscriber;
----------------------------------------------
PROCEDURE parse_by_campaign_id (p_capmaign_id IN CAMPAIGN_SUBSCRIBERS.campaign_id%TYPE,
p_start_date_epoch IN VARCHAR2,
p_end_date_epoch IN VARCHAR2) IS
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
v_seach_campaign_str VARCHAR2(100);
CURSOR get_campaign_subs_cur (cp_capmaign_id IN VARCHAR) IS
SELECT *
FROM SGA_W_PSMS_SUBSCRIBER
WHERE INSTR(campaign_history,cp_capmaign_id) > 0;
BEGIN
v_module_name := 'parse_by_campaign_id';
v_msg_text := 'Starting';
write_log(v_module_name, v_msg_text);
v_seach_campaign_str := ':'||p_capmaign_id||':';
FOR get_campaign_subs_rec IN get_campaign_subs_cur(TO_CHAR(v_seach_campaign_str)) LOOP
handle_subscriber(get_campaign_subs_rec, p_capmaign_id, p_start_date_epoch, p_end_date_epoch);
END LOOP;
v_msg_text := 'Finished';
write_log(v_module_name, v_msg_text);
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
write_log(v_module_name, v_msg_text);
RAISE;
END parse_by_campaign_id;
----------------------------------------------
END PARSE_CAMPAIGN_PKG ;
/
No comments:
Post a Comment