How to decode MTSMS Origination number in Oracle PL/SQL?

CDR is generally known as data file for storing Traffic in Telecom Company.
With ASN.1 format decoder, It could not convert all value of each field in CDR.

Incoming SMS is an example.

How to decode it to be readable with the following hexa number?

Origination field value:
–D035D6162747C4F616E6
–915875209740F7
–8188F8

****************************CDR 9958****************************
Start_addr:0x30e391
cdr_type: MTSMSRecord
recordType:07
serviceCentre:(91)85510200800
servedIMSI:456061092699145
servedIMEI:354574053945320
servedMSISDN:(91)85593279735
msClassmark:301882
recordingEntity:(91)85593010000
location:(LAC: 03 F3 CELLID: 76 C3)
deliveryTime:2013-04-01 09:45:35 + 0700
systemType:02(Decimal)
basicService:( 21)
additionalChgInfo:( 02)
chargedParty:01(Decimal)
chargeAreaCode:000000
orgRNCorBSCId:007008
orgMSCId:006708
globalAreaID:54F66003F376C3
subscriberCategory:0A
firstmccmnc:54F660
smsUserDataType:FF
origination:915895482179F4
callReference:38077F9879
typeOfSubscribers:00(Decimal)
recordNumber:1950135172(Decimal)

*****************************************************************************

********************************* Oralce Function *********************************

create or replace function fx_GetSMSOriginationNumber(origin VARCHAR2)

return varchar2 is
v_str VARCHAR2(1000);
v_str2 varchar2(1000);
v_rev_str varchar2(1000);
v_firstChar VARCHAR2(10);
n_loop_n number := 0;
begin

FOR i IN 1..length(origin)/2 LOOP

v_rev_str := ”;
v_rev_str := substr(origin,(n_loop_n + 1),2);

SELECT REVERSE(v_rev_str) INTO v_rev_str from dual;

v_str2 := v_str2 || v_rev_str;

n_loop_n := n_loop_n + 2;

END LOOP;

v_firstChar := substr(v_str2,1,1);

–if LENGTH(TRIM(TRANSLATE(v_firstChar, ‘ +-.0123456789′, ‘ ‘))) = ” then
if to_number(v_firstChar) < 1 then
v_str2 := utl_raw.cast_to_varchar2(hextoraw(v_str2)) ;
else
v_str2 := substr(v_str2,1,LENGTH(v_str2) – 1);
end if;

return v_str2;
end;

 

**********

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s