PL/SQL: Split URL Parameters
PL/SQL: Split Key-Value Pairs
REGEXP_REPLACE - Credit Card (CC) Numbers
PL/SQL: Pipelined Function
It all culminates in this (hopefully final) post.
The goal was to be able to take in a URL string and parse it out accordingly. I ultimately decided that persisting that data was not of importance, so I built a fairly flexible function which returns a user-defined-type (UDT), also known as a SQL object.
We'll start with the UDTs:
CREATE OR REPLACE TYPE r_key_value_record IS OBJECTNothing fancy there. I'm not exactly sure why I added the ORDEROF column, other than I think it will come in good use down the road.
(
orderof NUMBER(5),
key_string VARCHAR2(1000),
value_string VARCHAR2(1000)
)
/
CREATE OR REPLACE TYPE t_key_value_table AS TABLE OF R_KEY_VALUE_RECORD
/
FUNCTION parse_urlI tried to make this as flexible as possible. Different payment gateways return different response strings so this seemed necessary. I suppose I could build one for each...but that wouldn't be as fun.
( p_url IN VARCHAR2,
p_token_delimiter IN VARCHAR2,
p_keyvalue_delimiter IN VARCHAR2,
p_enclosed_by IN VARCHAR2 DEFAULT NULL,
p_line_start IN VARCHAR2 DEFAULT NULL,
p_line_end IN VARCHAR2 DEFAULT NULL ) RETURN T_KEY_VALUE_TABLE PIPELINED;
P_URL - self-explanatory
P_TOKEN_DELIMITER - For most URL strings, this will be the ampersand (&) that separates the key/value pairs.
P_KEYVALUE_DELIMITER - Usually the equals (=) sign, but can vary.
P_ENCLOSED_BY - occasionally a string will be enclosed by quotes (")
P_LINE_START - much more rare, the string has one or more characters at the beginning of the line
P_LINE_END - much more rare, the string has one or more characters at the end of the line
The declaration:
ISNothing special here.
l_table T_KEY_VALUE_TABLE := T_KEY_VALUE_TABLE();
l_string VARCHAR2(4000) := p_url;
l_token_delimiter_length INTEGER := LENGTH( p_token_delimiter );
l_key_value_delimiter_length INTEGER := LENGTH( p_keyvalue_delimiter );
l_key VARCHAR2(100);
l_value VARCHAR2(100);
l_keyvalue VARCHAR2(200);
l_counter INTEGER := 0;
l_new VARCHAR2(4000);
l_start INTEGER := 1;
l_end INTEGER := 0;
BEGIN
IF p_line_start IS NOT NULL THENGetting rid of any start or end characters. Next up, my kludge:
l_string := SUBSTR( l_string, LENGTH( p_line_start ) + 1 );
END IF;
IF p_line_end IS NOT NULL THEN
l_string := SUBSTR( l_string, 1, INSTR( l_string, p_line_end, -1 ) - 1 );
END IF;
IF SUBSTR( l_string, -1, l_token_delimiter_length ) != p_token_delimiter THENJust adding the delimiter to the end of the string. Apparently I'm too lazy to figure out a better way.
l_string := l_string || p_token_delimiter;
END IF;
Finally, the meat of the process:
LOOPWhat does all that do? Let's see.
l_counter := l_counter + 1;
IF p_keyvalue_delimiter IS NOT NULL THEN
l_end := INSTR( l_new, p_token_delimiter, 1 );
l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
EXIT WHEN l_keyvalue IS NULL;
l_key := SUBSTR( l_keyvalue, 1, INSTR( l_keyvalue, p_keyvalue_delimiter ) - 1 );
l_value := SUBSTR( l_keyvalue, INSTR( l_keyvalue, p_keyvalue_delimiter ) + 1 );
l_start := l_start + ( l_end + ( l_token_delimiter_length - 1 ) );
l_new := SUBSTR( l_string, l_start );
ELSE
l_end := INSTR( l_new, p_token_delimiter, 1 );
l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
EXIT WHEN l_new = p_token_delimiter;
l_key := l_counter;
l_value := l_keyvalue;
l_start := l_start + l_end ;
l_new := SUBSTR( l_string, l_start );
END IF;
l_table.EXTEND(1);
l_table( l_counter ) := R_KEY_VALUE_RECORD( l_counter, l_key, l_value );
PIPE ROW ( R_KEY_VALUE_RECORD( l_counter, l_key, l_value ) );
END LOOP;
RETURN;
END parse_url;
SET DEFINE OFFHow about a different flavor of URL string?
BEGIN
:C := '&&AUTH_CODE XXXXXX;&AVS_CODE N;&CMRCL_FLAG 5;&CMRCL_TYPE 3;&INTRN_SEQ_NUM
9999999;&PAYMENT_MEDIA AMEX;&REFERENCE 9211258897;&RESPONSE_TEXT PERFECT MATCH;&RESULT
SUCCESS;&RESULT_CODE 3;&TERMINATION_STATUS SUCCESS;&TRANS_DATE 2009.08.10;&TRANS_SEQ_NUM
999;&TRANS_TIME 15:24:51;&TROUTD 99999999;';
END;
/
SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, ';&', ' ', NULL, '&&', ';' ) );
ORDEROF KEY_STRING VALUE_STRING
------- -------------------- ------------------------
1 AUTH_CODE XXXXXX
2 AVS_CODE N
3 CMRCL_FLAG 5
4 CMRCL_TYPE 3
5 INTRN_SEQ_NUM 9999999
6 PAYMENT_MEDIA AMEX
7 REFERENCE 9211258897
8 RESPONSE_TEXT PERFECT MATCH
9 RESULT SUCCESS
10 RESULT_CODE 3
11 TERMINATION_STATUS SUCCESS
12 TRANS_DATE 2009.08.10
13 TRANS_SEQ_NUM 999
14 TRANS_TIME 15:24:51
15 TROUTD 99999999
BEGINOK, one more. For this one, there will be no key/value pair, it's simply ordered (yes, you need the API to decipher it).
:C := 'transaction_id=0983450982340985sada0384098098234&error_code=999&auth_response_text=Nice Job!';
END;
/
SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, '&', '=' ) );
ORDEROF KEY_STRING VALUE_STRING
------- -------------------- ------------------------------------
1 transaction_id 0983450982340985sada0384098098234
2 error_code 999
3 auth_response_text Nice Job!
BEGIN
:c := '1,1,1,This is a test transaction.,999999,Y,999999999,,,1.00,CC,authorization,,Jake,Kuramoto,,123 Main
Street,Somewhere,CA,93063,US,,,,,,,,,,,,,,,,,XXXXXXXXXXXXXXXX
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,';
END;
/
SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, ',', NULL ) );
ORDEROF KEY_STRING VALUE_STRING
------- -------------------- -------------------------------------------
1 1 1
2 2 1
3 3 1
4 4 This is a test transaction.
5 5 999999
6 6 Y
7 7 999999999
8 8
9 9
10 10 1.00
11 11 CC
12 12 authorization
13 13
14 14 Jake
15 15 Kuramoto
16 16
17 17 123 Main Street
18 18 Somewhere
19 19 CA
20 20 93063
21 21 US
22 22
...SNIP
37 37
38 38 XXXXXXXXXXXXXXXX
...SNIP
63 63
64 64
65 65
66 66
No comments:
Post a Comment