How to put a part of a code as a string in table to use it in a procedure?

Question

I'm trying to resolve below issue: I need to prepare table that consists 3 columns: user_id, month value. Each from over 200 users has got different values of parameters that determine expected value which are: LOB, CHANNEL, SUBSIDIARY. So I decided to store it in table ASYSTENT_GOALS_SET. But I wanted to avoid multiplying rows and thought it would be nice to put all conditions as a part of the code that I would use in "where" clause further in procedure. So, as an example - instead of multiple rows:

I created such entry:

So far I created testing table ASYSTENT_TEST (where I collect month and value for certain user). I wrote a piece of procedure where I used BULK COLLECT.

declare
  type test_row is record
  (
  month NUMBER,
  value NUMBER
  );
  type test_tab is table of test_row;
  BULK_COLLECTOR test_tab;
  p_lob varchar2(10) :='GOSP';
  p_sub varchar2(14);
  p_ch varchar2(10) :='BR';
  begin
  select subsidiary into p_sub from ASYSTENT_GOALS_SET where user_id='40001001';
  execute immediate 'select mc, sum(ppln_wartosc) plan from prod_nonlife.mis_report_plans 
  where report_id = (select to_number(value) from prod_nonlife.view_parameters where view_name=''MIS'' and parameter_name=''MAX_REPORT_ID'')
    and year=2017 
    and month between 7 and 9 
    and ppln_jsta_symbol in (:subsidiary)
    and dcs_group in (:lob)
    and kanal in (:channel)
  group by month order by month' bulk collect into BULK_COLLECTOR
  using p_sub,p_lob,p_ch;
  forall x in BULK_COLLECTOR.first..BULK_COLLECTOR.last insert into ASYSTENT_TEST values BULK_COLLECTOR(x);
end;

So now when in table ASYSTENT_GOALS_SET column SUBSIDIARY (varchar) consists string 12_00_00 (which is code of one of subsidiary) everything works fine. But the problem is when user works in two subsidiaries, let say 12_00_00 and 13_00_00. I have no clue how to write it down. Should SUBSIDIARY column consist: '12_00_00','13_00_00' or "12_00_00","13_00_00" or maybe 12_00_00','13_00_00 I have tried a lot of options after digging on topics like "Deling with single/escaping/double qoutes". Maybe I should change something in execute immediate as well?

Or maybe my approach to that issue is completely wrong from the very beginning (hopefully not :) ). I would be grateful for support.

Answer 1

I didn't create the table function described here but that article inspired me to go back to try regexp_substr function again.
I changed:
ppln_jsta_symbol in (:subsidiary)
to
ppln_jsta_symbol in (select regexp_substr((select subsidiary from ASYSTENT_GOALS_SET where user_id=''fake_num''),''[^,]+'', 1, level) from dual connect by regexp_substr((select subsidiary from ASYSTENT_GOALS_SET where user_id=''fake_num''), ''[^,]+'', 1, level) is not null)
Now it works like a charm! Thank you @Dessma very much for your time and suggestion!

Answer 2

"I wanted to avoid multiplying rows and thought it would be nice to put all conditions as a part of the code that I would use in 'where' clause further in procedure"

This seems a misguided requirement. You shouldn't worry about number of rows: databases are optimized for storing and retrieving rows.

What they are not good at is dealing with "multi-value" columns. As your own solution proves, it is not nice, it is very far from nice, in fact it is a total pain in the neck. From now on, every time anybody needs to work with subsidiary they will have to invoke a function. Adding, changing or removing a user's subsidiary is much harder than it ought to be. Also there is no chance of enforcing data integrity i.e. validating that a subsidiary is valid against a reference table.

Maybe none of this matters to you. But there are very good reasons why Codd mandated "no repeating groups" as a criterion of First Normal Form, the foundation step of building a sound data model.

The correct solution, industry best practice for almost forty years, would be to recognise that SUBSIDIARY exists at a different granularity to CHANNEL and so should be stored in a separate table.