Oracle Tips and Tricks — David Fitzjarrell

September 26, 2008

How Dynamic

Filed under: pl/sql — dfitzjarrell @ 18:58

Passing a list of values to a function or procedure should be, well, simple, and it is, really, unless you have a dynamic list of unknown length. Simply trying to use the supplied string, as-is, can be disappointing:

SQL> --
SQL> -- Let's try this the simple way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
  2  return number
  3  is
  4        lv_ct number;
  5  
  6        cursor get_empinfo is
  7        select count(*)
  8        from emp
  9        where deptno in subtype_list;
 10  
 11  begin
 12  
 13    open get_empinfo;
 14    fetch get_empinfo into lv_ct;
 15    close get_empinfo;
 16  
 17    return lv_ct;
 18  end;
 19  /

Function created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- The function created without error
SQL> --
SQL> -- Let's see if it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;
select instring_list_test('10,20,30') from dual
       *
ERROR at line 1:
ORA-01722: invalid number 
ORA-06512: at "BING.INSTRING_LIST_TEST", line 14 


SQL> 
SQL> --
SQL> -- That's silly, it should work ...
SQL> --

Since ‘10,20,30’ isn’t a number, and Oracle can’t magically separate the individual values the function call fails. It would succeed were there one value in this dynamic list, but real-life situations usually aren’t that simple and straightforward. Whatever shall we do? We need to ‘get dirty’ and actually code a way for Oracle to separate the values, make them numbers and populate a table, dynamically, so we can select from that table and generate a usable list. So, let’s try this again and see if we can get this to do what we want:

SQL> --
SQL> -- Let's try this again
SQL> --
SQL> -- We'll create a table type first
SQL> --
SQL> create or replace type InNumTab is table of number;
  2  /

Type created.

SQL> 
SQL> --
SQL> -- Now we'll use that table type to massage
SQL> -- the supplied string into a usable list
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
  2  return number
  3  is
  4        --
  5        -- The parsed value
  6        --
  7        lv_subtyp number;
  8        --
  9        -- The table we'll populate
 10        --
 11        lv_sublist InNumTab := InNumTab();
 12        --
 13        -- A place for the result
 14        --
 15        lv_ct number;
 16        --
 17        -- A variable so we can 'walk' the string
 18        --
 19        startpos number:=1;
 20        --
 21        -- Record counter to extend the table
 22        --
 23        rec     number:=1;
 24  
 25        --
 26        -- Query using the dynamic IN list
 27        --
 28        cursor get_empinfo (enums InNumTab) is
 29        select count(*)
 30        from emp
 31        where deptno in (select column_value from table(cast(enums as InNumTab)));
 32  
 33  begin
 34    --
 35    -- Extend the table so we can start populating it
 36    --
 37    lv_sublist.extend(rec);
 38  
 39    --
 40    -- 'Walk' the provided string
 41    -- The loop exits when no value separator is found
 42    -- We expect the value separator to be a comma
 43    --
 44    loop
 45        exit when instr(subtype_list, ',', startpos) = 0;
 46        lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,',', 1)-1);
 47        lv_sublist(rec) := lv_subtyp;
 48        startpos := instr(subtype_list, ',', startpos)+1;
 49        rec := rec+1;
 50        --
 51        -- After each addition we extend the table
 52        --
 53        lv_sublist.extend(rec);
 54    end loop;
 55  
 56    --
 57    -- We extend the table one more time to hold our last value
 58    --
 59    rec := rec+1;
 60    lv_sublist.extend(rec);
 61    lv_subtyp := substr(subtype_list, startpos);
 62    lv_sublist(rec) := lv_subtyp;
 63  
 64    --
 65    -- Get the count
 66    --
 67    open get_empinfo(lv_sublist);
 68    fetch get_empinfo into lv_ct;
 69    close get_empinfo;
 70  
 71    --
 72    -- Return the value to the caller
 73    --
 74    return lv_ct;
 75  end;
 76  /

Function created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;

INSTRING_LIST_TEST('10,20,30')
------------------------------
                            27

SQL> select instring_list_test('10,20') from dual;

INSTRING_LIST_TEST('10,20')
---------------------------
                         12

SQL> select instring_list_test('10') from dual;

INSTRING_LIST_TEST('10')
------------------------
                       3

SQL> select instring_list_test('10,20,30,40') from dual;

INSTRING_LIST_TEST('10,20,30,40')
---------------------------------
                               42

SQL> select instring_list_test('10,20,30,40,50,60') from dual;

INSTRING_LIST_TEST('10,20,30,40,50,60')
---------------------------------------
                                     60

SQL> select instring_list_test('10,20,30,40,50') from dual;

INSTRING_LIST_TEST('10,20,30,40,50')
------------------------------------
                                  54

SQL> 

Notice that the length of the list is immaterial as the loop keeps running until the desired record separator is no longer present; we need to code one additional parse of the supplied string to extract the last value and place it in our dynamic table. We then use the CAST and TABLE functions when we query this ‘table’; the result is a list of values, rather than the original string, which makes IN very happy indeed.

So what if you don’t want to always use a comma to separate your data values? Don’t panic, we can re-write the function to accept a second parameter, the record separator:

SQL> --
SQL> -- We'll try this another way, passing in the desired record separator value
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
  2  return number
  3  is
  4        --
  5        -- The parsed value
  6        --
  7        lv_subtyp number;
  8        --
  9        -- The table we'll populate
 10        --
 11        lv_sublist InNumTab := InNumTab();
 12        --
 13        -- A place for the result
 14        --
 15        lv_ct number;
 16        --
 17        -- A variable so we can 'walk' the string
 18        --
 19        startpos number:=1;
 20        --
 21        -- Record counter to extend the table
 22        --
 23        rec     number:=1;
 24
 25        --
 26        -- Query using the dynamic IN list
 27        --
 28        cursor get_empinfo (enums InNumTab) is
 29        select count(*)
 30        from emp
 31        where deptno in (select column_value from table(cast(enums as InNumTab)));
 32
 33  begin
 34    --
 35    -- Extend the table so we can start populating it
 36    --
 37    lv_sublist.extend(rec);
 38
 39    --
 40    -- 'Walk' the provided string
 41    --
 42    -- We provide the desired record separator
 43    --
 44    loop
 45        exit when instr(subtype_list, recsep, startpos) = 0;
 46        lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
 47        lv_sublist(rec) := lv_subtyp;
 48        startpos := instr(subtype_list, recsep, startpos)+1;
 49        rec := rec+1;
 50        --
 51        -- After each addition we extend the table
 52        --
 53        lv_sublist.extend(rec);
 54    end loop;
 55
 56    --
 57    -- We extend the table one more time to hold our last value
 58    --
 59    rec := rec+1;
 60    lv_sublist.extend(rec);
 61    lv_subtyp := substr(subtype_list, startpos);
 62    lv_sublist(rec) := lv_subtyp;
 63
 64    --
 65    -- Get the count
 66    --
 67    open get_empinfo(lv_sublist);
 68    fetch get_empinfo into lv_ct;
 69    close get_empinfo;
 70
 71    --
 72    -- Return the value to the caller
 73    --
 74    return lv_ct;
 75  end;
 76  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;

INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
                                27

SQL> select instring_list_test('10;20',';') from dual;

INSTRING_LIST_TEST('10;20',';')
-------------------------------
                             12

SQL> select instring_list_test('10',',') from dual;

INSTRING_LIST_TEST('10',',')
----------------------------
                           3

SQL> select instring_list_test('10:20:30:40',':') from dual;

INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
                                   42

SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;

INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
                                         60

SQL> select instring_list_test('10,20,30,40,50',',') from dual;

INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
                                      54

SQL>

And it works like a charm, returning correct values for the counts requested. Is that cool, or what?

In Oracle 10g and later releases the MEMBER OF operator can be used in place of the table function used in the prior two examples:

SQL>
SQL> --
SQL> -- We'll try this yet another way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
  2  return number
  3  is
  4          --
  5          -- The parsed value
  6          --
  7          lv_subtyp number;
  8          --
  9          -- The table we'll populate
 10          --
 11          lv_sublist InNumTab := InNumTab();
 12          --
 13          -- A place for the result
 14          --
 15          lv_ct number;
 16          --
 17          -- A variable so we can 'walk' the string
 18          --
 19          startpos number:=1;
 20          --
 21          -- Record counter to extend the table
 22          --
 23          rec     number:=1;
 24
 25          --
 26          -- Query using the dynamic IN list
 27          --
 28          cursor get_empinfo (enums InNumTab) is
 29          select count(*)
 30          from emp
 31          where deptno member of enums;
 32
 33  begin
 34      --
 35      -- Extend the table so we can start populating it
 36      --
 37      lv_sublist.extend(rec);
 38
 39      --
 40      -- 'Walk' the provided string
 41      --
 42      -- We expect the value separator to be a comma
 43      --
 44      loop
 45          exit when instr(subtype_list, recsep, startpos) = 0;
 46          lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
 47          lv_sublist(rec) := lv_subtyp;
 48          startpos := instr(subtype_list, recsep, startpos)+1;
 49          rec := rec+1;
 50          --
 51          -- After each addition we extend the table
 52          --
 53          lv_sublist.extend(rec);
 54      end loop;
 55
 56      --
 57      -- We extend the table one more time to hold our last value
 58      --
 59      rec := rec+1;
 60      lv_sublist.extend(rec);
 61      lv_subtyp := substr(subtype_list, startpos);
 62      lv_sublist(rec) := lv_subtyp;
 63
 64      --
 65      -- Get the count
 66      --
 67      open get_empinfo(lv_sublist);
 68      fetch get_empinfo into lv_ct;
 69      close get_empinfo;
 70
 71      --
 72      -- Return the value to the caller
 73      --
 74      return lv_ct;
 75  end;
 76  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;

INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
                                27

SQL> select instring_list_test('10;20',';') from dual;

INSTRING_LIST_TEST('10;20',';')
-------------------------------
                             12

SQL> select instring_list_test('10',',') from dual;

INSTRING_LIST_TEST('10',',')
----------------------------
                           3

SQL> select instring_list_test('10:20:30:40',':') from dual;

INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
                                   42

SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;

INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
                                         60

SQL> select instring_list_test('10,20,30,40,50',',') from dual;

INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
                                      54

SQL>

The problem, though simple to state, isn’t quite as simple to solve, as proven above. That’s because Oracle is a database, it isn’t your brain, so it can’t draw on prior experience and know that ‘10,20,30’ is glorified shorthand for 10,20,30. And because it’s software (really, it is) running on a computer, it does what you tell it to do, whether or not those instructions provide the output you’d intended. Since it can’t think like you do you’ll have to start ‘thinking’ like it does, and that may throw a wrench into your logical picture of the situation. Knowing what to do with that wrench is half of the battle.

Passing dynamic lists to procedures and functions is possible, it just isn’t as easy as you’d like to think, especially the first time through. But, knowing how to navigate such a situation makes life a bit easier in the IT realm, which may make you pretty nifty.

Of course such knowledge is no substitute for the ultimate cool of driving a Lamborghini to the dollar store …

Advertisements

1 Comment »

  1. cool PL/SQL stuff!my solution is just SQL without any PL/SQL.plz re-format SQL as your taste. with dept as ( SELECT to_number (regexp_substr(:v_str||:v_deli, '[^'||:v_deli||']+', 1, level) ) AS deptno FROM dual connect by level<= ( length(:v_str) – length(replace(:v_str, :v_deli)) ) / length(:v_deli) + 1 )select count(*) from emp, dept where emp.deptno = dept.deptno ; The DataBase Scientistregards odong

    Comment by odong — June 11, 2009 @ 15:49 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: