Treat lists as table

For many times I have a situation to use a variable list of varchar2 values as parameter in some procedure or multiple action. It was OK when action was able to be pulled in one script and execute at once, but many times this was not a case, so I have to copy/paste these entries to several places by hand with no or little chance to automate that. And even more, chance to make a mistake was much bigger!
Many of you probably know that Apex has brought PL/SQL solution, which introduced similar functionality through string_to_table function.

Here is how it looks like in Oracle’s case:

declare
  list_arr2 htmldb_application_global.vc_arr2;
begin
  list_arr2 := htmldb_util.string_to_table('a:2:III');
  for i in 1..list_arr2.count loop
    dbms_output.put_line(list_arr2(i));
  end loop;
end;
/

And the result is:

SQL> /
a
2
III

PL/SQL procedure successfully completed.

SQL>

You see that a variable items list is transformed in some kind of table like structure, what satisfied needs for serving in Apex application itself. However, main problem in this case is that you deal with arrays and in a case you want to use them in SQL you must transform them somehow to table.

From this point of view, now I realize that mine solution, which exists several years before Apex has published it’s method, did resolve mine needs completely and may resolve some of yours...in a very similar way like Apex did. I'm not saying that Apex code cannot be fixed to behave like mine, but for that you need source which is not available.

Solution is compound from one type and one small function, which I called list2table:

create or replace type g_tbl_varchar2 is table of varchar2(1000);
/

CREATE OR REPLACE FUNCTION list2table(
                                      p_list IN VARCHAR2,
                                      p_delimiter IN VARCHAR2 default ','
                                       ) RETURN g_tbl_varchar2

/*--------------------------------------------------------------------------------------
NAME    : list2table
PURPOSE : Function returns one column table records from variable items list (like classic Oracle table).

Date    : 05.03.2005.
Author  : Damir Vadas

Remarks : p_delimiter could be any CHAR with length 1

Changes (DD.MM.YYYY, Name, CR/TR#):
------------------------------------------------------------------------------------ */
IS
  v_str VARCHAR2(4000) := p_list || p_delimiter;
  v_cnt NUMBER := length(v_str) - length(REPLACE(v_str, p_delimiter));
  v_chartab g_tbl_varchar2 := g_tbl_varchar2();
BEGIN
  FOR i IN 1 .. v_cnt LOOP
    v_chartab.EXTEND;
    v_chartab(i) := substr(v_str, 1, instr(v_str, p_delimiter) - 1);
    v_str := substr(v_str, instr(v_str, p_delimiter) + 1);
  END LOOP;
  RETURN v_chartab;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    raise;
END;
/

Be aware that this code has a parameter p_delimiter but in my case only 1 char length is allowed. Usage is very simple:

SELECT * FROM TABLE(list2table('a,s,d,12,3,4,5'));

Here is a little more complex example which shows how to include where and column name in such a list result. This is condittio sinne qua non for full manipulation as a table:

with q as (
   SELECT my_name FROM TABLE(list2table('a,s,d,12,3,4,5,122,145'))
)
select q.my_name
from q
where q.my_name='d';

MY_NAME
-------
d

With such a functionality I was able to sore values from variable list to one column table and then use them as normal data parameter all around.

Practical usage of this will be shown soon in topic “Disable APPS users dynamically” when I get time to write it.