Monday, 21 January 2013

Parsing Strings in PL/SQL

Consider a scenario where you need to parse a VARCHAR2 value which is delimited by a special character such as a comma. Rather than parse the entire input and search for the start and end of each sub-string, use the apex_util.string_to_table() that is available in Oracle 10G and 11G

A typical usage example would be as follows:


variable_array_to_hold_values apex_application_global.vc_arr2;
variable_test VARCHAR2(100) := 'show:me:a:parser';


    variable_array_to_hold_values := apex_util.string_to_table(variable_test, ':');
     FOR i IN 1..variable_array_to_hold_values.COUNT
         DBMS_OUTPUT.PUT_LINE(' Value is ' || variable_array_to_hold_values(i));
     END LOOP;

And the result would be
 Value is show
 Value is me
 Value is a
 Value is parser

No comments: