Dynamic IN-Lists…

10 thoughts on “Dynamic IN-Lists…”

1. Hi Tim,
   One other approach is to use the pre-defined collection type SYS.DBMS_DEBUG_VC2COLL or SYS.KU$_VCNT. I have a couple of related posts on my blog, here and here.

   Cheers!

2. Hi Tim,

   What a co-incidence. I’ve just been looking at a similar problem (using table functions to process a comma-separated list), although I used DBMS_UTILITY.COMMA_TO_TABLE to do the parsing.

   cheers

   Mark

3. Mark:

   You have to be a little careful with DBMS_UTILITY.COMMA_TO_TABLE as it is meant or items that correspond to valid Oracle object names, like tables and columns. If you use it on anything else it is prone to errors.

   Cheers

   Tim…

4. Tim

   Thanks for the advice, I think I’ve read about this restriction elsewhere. Do you know if there’s any issue with spaces between values, i.e. would “Mark Rittman” be parsed OK?

5. Mark:

   Looks like this would fail. See:

   SET SERVEROUTPUT ON
   DECLARE
   –l_list1 VARCHAR2(50) := ‘Tim_Hall,Mark_Rittman,Jeff_Hunter’;
   l_list1 VARCHAR2(50) := ‘Tim Hall,Mark Rittman,Jeff Hunter’;
   l_tablen BINARY_INTEGER;
   l_tab DBMS_UTILITY.uncl_array;
   BEGIN
   DBMS_OUTPUT.put_line(‘l_list1 : ‘ || l_list1);

   DBMS_UTILITY.comma_to_table (
   list => l_list1,
   tablen => l_tablen,
   tab => l_tab);

   FOR i IN 1 .. l_tablen LOOP
   DBMS_OUTPUT.put_line(i || ‘ : ‘ || l_tab(i));
   END LOOP;
   END;
   /
   l_list1 : Tim Hall,Mark Rittman,Jeff Hunter
   DECLARE
   *
   ERROR at line 1:
   ORA-20001: comma-separated list invalid near m Hal
   ORA-06512: at “SYS.DBMS_UTILITY”, line 174
   ORA-06512: at “SYS.DBMS_UTILITY”, line 202
   ORA-06512: at line 9

   If you try the version without the spaces it works fine.

   Cheers

   Tim…

6. Bugger. Well that’s a useful feature then, isn’t it.

7. Looks like this metalink note has the full details.

   http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1014181.102

8. Annoying isn’t it!

   It’s about time Oracle introduced a split function.

   Cheers

   Tim…

9. This blog popped up at the top of a search for dbms_utility.comma_to_table, so I thought I would add this:

   To overcome the problem of elements starting with numeric digits, you can use Replace and Translate to add, and then remove double quotes. Embedded spaces can be changed to some little used character like ~ or #.

   The code below demonstrates the double quote use: (hope the html “pre” works here)

   Declare
   csv varchar2(2000) := ‘abc,123.4,205,wxyz’;
   csv_new varchar2(2000);
   tab dbms_utility.uncl_array;
   cnt number;
   Begin
   csv_new := ‘”‘ || replace( csv, ‘,’, ‘”,”‘ ) || ‘”‘;
   dbms_output.put_line(‘Old: ‘ || csv);
   dbms_output.put_line(‘New: ‘ || csv_new);
   dbms_utility.comma_to_table(csv_new, cnt, tab);
   for i in 1..cnt loop
   dbms_output.put_line( translate( tab(i), ‘A”‘, ‘A’ ) );
   end loop;
   End;
   /

10. Well, the WordPress software screwed up the code above. It replaced apostrophes (single-quotes) with left and right-slanted ones. And it similarly changed the double-quotes, too. …not to mention the spacing and indentation was removed.

    So to get the above to work, you will need to fix the altered quotes.

Comments are closed.