Dynamic IN-Lists…

I’ve just knocked up a quick article on Dynamic IN-Lists in response to a question on my forum. This is quite a popular question so I’m surprised I haven’t done it before. That’s one less question to answer in future 🙂

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer.

10 thoughts on “Dynamic IN-Lists…”

  1. 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

  2. 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…

  3. 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?

  4. 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…

  5. 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;
    /

  6. 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.