PL/SQL Key/Value Pair Utility

Get the code: keyValueUtils.pkg

KeyValueUtils is a simple (but very useful) Key/Value pair utility that allows you to define sets (collections) of KV pairs for use in PL/SQL applications.  The utility also provides the ability to access the KV set in a query using a pipeline function (and a table function).  This gives a developer the ability to create something similar to a NoSQL type of KV pair table (on the fly).

The utility consists of:

  • A  user defined type called T_KEY_VALUE, that contains two VARCHAR2 attributes key and value.
  • A collection of T_KEY_VALUE type called KEY_VALUE_SET.
  • A PL/SQL package called keyValueUtils.

The package contains the following procedure and functions:

  • PROCEDURE addPairToCollection – Adds  a KV pair to a KV pair set (collection).
  • PROCEDURE setValue – Sets the value of KV pair in the given KV set (collection). If KV pair currently exists in the set, it will be updated.  Otherwise, a new KV pair will added to the KV set.
  • FUNCTION getValue – Retrieves the value of KV pair in a given KV Set.
  • FUNCTION pipeLineQueryKVSet – Pipeline Function for retrieving the values in the KV set via a table function in a SQL query.
  • FUNCTION parseStringToKVSet – This function will parse a parametrized string into a KV Set and return that set. Example: test1=hello,test2=world … would be parsed into two pairs ((“test1″,”Hello”),(“test2″,”World”)).

The following code sample displays all of the functionality of the utility:

SET SCAN OFF;
DECLARE
v_param_string VARCHAR2(200) := ‘p_id_number=20101001-0900-00&p_wst_code=W&p_start_date=09/10/2010 0900&p_end_date=09/30/2010 0700&p_emp_code=003509&p_emp_name=JBENNETT';
v_param_set KEY_VALUE_SET := KEY_VALUE_SET();
v_pair T_KEY_VALUE := T_KEY_VALUE();CURSOR crsrKVCursor(kv_set KEY_VALUE_SET) IS
SELECT
key,
value
FROM
TABLE(kv_set)
ORDER BY key;

BEGIN

v_param_set := keyvalueutils.parsestringtokvset(v_param_string,’&’);

DBMS_OUTPUT.PUT_LINE(‘Initial Value of p_id_number=’||keyvalueutils.getValue(v_param_set,’p_id_number’));

keyvalueutils.setValue(v_param_set,’p_id_number’,’20101001-0930-00′);

DBMS_OUTPUT.PUT_LINE(‘Access and Display values “manually”.’);
DBMS_OUTPUT.PUT_LINE(‘p_id_number=’||keyvalueutils.getValue(v_param_set,’p_id_number’));
DBMS_OUTPUT.PUT_LINE(‘p_wst_code=’||keyvalueutils.getValue(v_param_set,’p_wst_code’));
DBMS_OUTPUT.PUT_LINE(‘p_start_date=’||keyvalueutils.getValue(v_param_set,’p_start_date’));
DBMS_OUTPUT.PUT_LINE(‘p_end_date=’||keyvalueutils.getValue(v_param_set,’p_end_date’));
DBMS_OUTPUT.PUT_LINE(‘p_emp_code=’||keyvalueutils.getValue(v_param_set,’p_emp_code’));
DBMS_OUTPUT.PUT_LINE(‘p_emp_name=’||keyvalueutils.getValue(v_param_set,’p_emp_name’));
DBMS_OUTPUT.PUT_LINE(”);

v_pair.key := ‘p_new_param';
v_pair.value := ‘Hello World';

keyvalueutils.addPairtoCollection(v_param_set,v_pair);

DBMS_OUTPUT.PUT_LINE(‘Access and display values via SQL query.’);

FOR data_rec IN crsrKVCursor(v_param_set) LOOP

DBMS_OUTPUT.PUT_LINE(data_rec.key||’=’||data_rec.value);

END LOOP;

END;

The results produced by the code above are:

Initial Value of p_id_number=20101001-0900-00
Access and Display values “manually”.
p_id_number=20101001-0930-00
p_wst_code=W
p_start_date=09/10/2010 0900
p_end_date=09/30/2010 0700
p_emp_code=003509
p_emp_name=JBENNETTAccess and display values via SQL query.
p_emp_code=003509
p_emp_name=JBENNETT
p_end_date=09/30/2010 0700
p_id_number=20101001-0930-00
p_new_param=Hello World
p_start_date=09/10/2010 0900
p_wst_code=W

Comments are closed.