Stored Procedure to populate Materialized View

Question

How to create a stored procedure. We have a parent view with the data in the one of the columns as below,

• Link-ABCD*Link-Child01*Link-Child02
• Link-DEFG*Link-Child03*Link-Child04
• Link-XYZ*Link-Child05*Link-Child04

We want to create a Materialized View on top of this Parent View having the following structure,

ID_COLUMN | ELEMENT_COLUMN        | PARENT_ID_COLUMN
  1       |        Link-ABCD      |     0
  2       |        Link-Child01   |     1
  3       |        Link-Child02   |     2
  4       |        Link-DEFG      |     0
  5       |        Link-Child03   |     4
  6       |        Link-Child04   |     5
  7       |        Link-XYZ       |     0
  8       |        Link-Child05   |     7
  9       |        Link-Child04   |     8
   -

• ID_Column = Auto Generated
• ELEMENT_COLUMN = Need to split the data present in the parent view with the delimiter "*", and insert each element only if it does not exist before or if element is already exists, then we need to check parent id of the existing element and if the parent id is not the same as the ID_COLUMN of the current parent. For example we had to insert Link-Child04 again because the parent is different for last case.
• PARENT_ID_COLUMN = value will be the same as the ID_COLUMN of the parent element. FOR eg Link-Child01 is a child of Link-ABCD whose id was 1, so the parent_id for Link-Child01 has to be 1. and so on. The parent_id for the elements who don't have any parents will be 0. For example parent id for Link-ABCD, Link-DEFG and Link-XYZ is 0.

Answer 1

You can generate the result you want using a recursive CTE (as long as you're on Oracle 11gR2 or higher), and a ranking function. If the column in your parent_view is called, for example, str then you can do:

with rcte (str, root_rn, child_level, element) as (
  select str,
    row_number() over (order by str),
    1,
    regexp_substr(str, '(.*?)(\*|$)', 1, 1, NULL, 1)
  from parent_view
  union all
  select str,
    root_rn,
    child_level + 1,
    regexp_substr(str, '(.*?)(\*|$)', 1, child_level + 1, NULL, 1)
  from rcte
  where child_level <= regexp_count(str, '\*')
)
select rank() over (order by root_rn, child_level) as id,
  element,
  case when child_level = 1 then 0
       else rank() over (order by root_rn, child_level) - 1 end as parent_id
from rcte
order by root_rn, child_level;

which with your sample strings gets:

        ID ELEMENT               PARENT_ID
---------- -------------------- ----------
         1 Link-ABCD                     0
         2 Link-Child01                  1
         3 Link-Child02                  2
         4 Link-DEFG                     0
         5 Link-Child03                  4
         6 Link-Child04                  5
         7 Link-XYZ                      0
         8 Link-Child05                  7
         9 Link-Child04                  8

But it's unclear what you really want to do with those. You could create a normal view or a materialized view from that query, but the IDs will change as rows are added, removed modified in the parent view - or rather, in its base tables. (And views built on top of views can introduce performance issues).

You could also maintain a normal table from a procedure, using the same query; but that would seem to be duplicating the functionality that a materialized view gives you.

---

You might also want to look at the parent view's query to see where those strings are coming from - it's just a guess, but it might be generating them by aggregating values from base tables which you could just look at directly.

Answer 2

Example of how to extract the 3 fields:

select a.link_info,
       substr(a.link_info,
              1,
              instr(a.link_info,'*',1,1)-1       /* Until 1st asterisk*/
              )                      as element1,
       substr(a.link_info,
              instr(a.link_info,'*',1,1)+1,     /* between 1st and 2nd asterisk*/
              instr(a.link_info,'*',1,2)-instr(a.link_info,'*',1,1)-1
              )                      as element2,
       substr(a.link_info,
              instr(a.link_info,'*',1,2)+1     /* after the 2nd asterisk */
              )                      as element3
from (select 'Link-ABCD*Link-Child01*Link-Child02' as link_info
      from dual
      union all
      select 'Link-DEFG*Link-Child03*Link-Child04' as link_info
      from dual
      union all
      select 'Link-XYZ*Link-Child05*Link-Child04' as link_info
      from dual
      ) a;