Does PL/SQL have an equivalent StringTokenizer to Java's?

Question

I use java.util.StringTokenizer for simple parsing of delimited strings in java. I have a need for the same type of mechanism in pl/sql. I could write it, but if it already exists, I would prefer to use that. Anyone know of a pl/sql implementation? Some useful alternative?

Answer 1

Hi dacracot,

if you have APEX installed, the function APEX_UTIL.string_to_table does just that.

Answer 2

PL/SQL does not come with a built-in tokenizer. However, it is relatively simple to build out of SQL or PL/SQL. Adrian Billington's web site has several solutions. In addition, if you are on 10g, you could use this code from Tanel Poder, which does it in SQL using regex.

Admittedly it would be easier if Oracle just included the dang facility as one of their built-ins.

Answer 3

An alternative is to write a Java stored proc (there is a JVM inside the database), that means you can use java.util.StringTokenizer. You have to wrap a Java stored proc inside a PL/SQL procedure/function.

Se here for an example: http://forums.oracle.com/forums/thread.jspa?messageID=2575374&#2575374

Sadly I doný understand Java's checked exceptions so the exception handling isn't really great (I'm not a Java dev).

Answer 4

PL/SQL does include a basic one for comma separated lists (DBMS_UTILITY.COMMA_TO_TABLE).

Example:

DECLARE
   lv_tab_length   BINARY_INTEGER;
   lt_array   DBMS_UTILITY.lname_array;
BEGIN
   DBMS_UTILITY.COMMA_TO_TABLE( list => 'one,two,three,four'
                              , tablen => lv_tab_length
                              , tab => lt_array
                              );

   DBMS_OUTPUT.PUT_LINE( 'lv_tab_length = ['||lv_tab_length||']' );

   FOR i IN 1..lv_tab_length
   LOOP
      DBMS_OUTPUT.PUT_LINE( '['||lt_array( i )||']' );
   END LOOP;

END;
/

Or see this Ask Tom link for other ideas...

Ak Tom - "varying elements in IN list"

Answer 5

An example of a mapreducy parallelized pl/sql tokenizer : http://blogs.oracle.com/datawarehousing/2009/10/in-database%5Fmap-reduce.html