Validating Column Data Stored as CSV Against Another Table

Question

I wanted to see what some suggested approaches would be to validate a field that is stored as a CSV against a table containing appropriate values. Althought it would be desired, it is NOT an option to split the CSV list into another related table. In the example data below I would be trying to capture the code 99 for widget A.

Below is an example data representation.

Table: Widgets

WidgetName   WidgetCodeList
A            1, 2, 3
B            1
C            2, 3
D            99

Table: WidgetCodes

WidgetCode  
1
2
3

An earlier approach was to query the CSV column as rows using various string manipulations and CONNECT_BY_LEVEL however the performance was not acceptible.

Answer 1

In PL/SQL, you could make use of the Apex utility for converting a delimited string to a PL/SQL collection like this:

procedure validate_csv (p_csv varchar2)
is
   v_array apex_application_global.vc_arr2;
   v_dummy varchar2(1);
begin
   v_array := apex_util.string_to_table(p_csv, ', ');
   for i in 1..v_array.count
   loop
      begin
         select null
         into   v_dummy
         from   widgetcodes
         where  widgetcode = v_array(i);
      exception
         when no_data_found then
            raise_application_error('Invalid widget code: '||v_array(i));
      end;         
   end loop;
end;

Answer 2

Hi Jakkwylde,

You could try a pipelined function (here with a lateral join):

SQL> WITH widgets AS (
  2     SELECT 'A' WidgetName, '1, 2, 3' WidgetCodeList FROM dual
  3     UNION ALL SELECT 'B', '1' FROM DUAL
  4     UNION ALL SELECT 'C', '2, 3' FROM DUAL
  5     UNION ALL SELECT 'D', '99' FROM DUAL
  6  ), widgetcodes AS (
  7     SELECT ROWNUM widgetcode from dual CONNECT BY LEVEL <= 3
  8  )
  9  SELECT w.widgetname,
 10         to_number(s.column_value) missing_widget
 11    FROM widgets w
 12         CROSS JOIN TABLE(demo_pkg.string_to_tab(w.WidgetCodeList)) s
 13   WHERE NOT EXISTS (SELECT NULL
 14                       FROM widgetcodes ws
 15                      WHERE ws.widgetcode = to_number(s.column_value));

WIDGETNAME MISSING_WIDGET
---------- --------------
D                      99

See this other SO for an example of a pipelined function that converts a character string to a table.