Locate Substitution String in clob Template column using regexp

Last time I wanted to locate the substitution strings in clob template column. I suppose there is no handy code for this. After searching asktom, I got some idea from this anwser and I composed three functions to get what I wanted. Today I updated them and it's available as a package in my github folder: UTL_APEX_SUBSTITUTIONSTR.sql.

There are three functions in this package.
--------------------------------------------------------------------------------------
-- locate all substitution strings from template HTML source
--   input: p_clob, HTML source of template
--   output: return all substitution string in HTML source
  function locate_all_substitutionstr(p_clob in clob)
    return t_locate_tab
    pipelined;

--------------------------------------------------------------------------------------
-- locate specified substitution string or keywords in attribute peer from template HTML source
-- (e.g. id="#REGION_STATIC_ID#_heading", onclick="htmldb_ToggleWithImage(this,'#REGION_ID#Body')" class="t13RegionTitle")
--   input: p_keywords, partial or full specified string, e.g. '_ID#', '#REGION_STATIC_ID#' 't13RegionTitle'
--   input: p_clob, HTML source of template
--   output: return line index, column index and attribute peer (e.g. id="#REGION_STATIC_ID#_heading")
  function locate_string_in_attr(p_keywords in varchar2,
                                        p_clob in clob)
    return t_locate_tab
    pipelined;

--------------------------------------------------------------------------------------
-- locate specified substitution string not in any tag attribute-peer from template HTML source
-- that means substitution string is orphan.
--   input: p_keywords, specified substitution string, support full string only. e.g. '#REGION_ATTRIBUTES#', '#BODY#'
--   input: p_clob, HTML source of template
--   output: return the whole line containing p_keywords to attribute_string
  function locate_orphan_substitutionstr(p_keywords in varchar2,
                                         p_clob in clob)
    return t_locate_tab
    pipelined;

1. Locate all substitution strings in clob template column

Search all substitution string in page template, and limit output to 50 rows, using pipelined table function to retrieve all substitution strings from input parameter.  Here application 100 is the testing app and apex_application_temp_page.page_body is the body definition in page template.
SQL> select *
  2    from (select p.application_id,
  3                 t.*,
  4                 to_char(p.template_id) as template_id,
  5                 p.template_name
  6            from apex_application_temp_page p,
  7                 table(utl_apex_substitutionstr.locate_all_substitutionstr(p.page_body)) t
  8           where p.application_id = 100
  9           order by 1, 5, 2, 3)
 10   where rownum < 51
 11  /
Output:
APPLICATION_ID  LINE_NUMBER  COLUMN_INDEX  ATTRIBUTE_STRING                  TEMPLATE_ID          TEMPLATE_NAME
100             2            3             #SIDE_GLOBAL_NAVIGATION_LIST#     1238447314412412678  Standard
100             5            9             #REGION_POSITION_01#              1238447314412412678  Standard
100             8            9             #SUCCESS_MESSAGE#                 1238447314412412678  Standard
100             8            26            #NOTIFICATION_MESSAGE#            1238447314412412678  Standard
100             8            48            #GLOBAL_NOTIFICATION#             1238447314412412678  Standard
100             10           11            #BODY#                            1238447314412412678  Standard
100             14           43            #REGION_POSITION_05#              1238447314412412678  Standard
100             16           45            #APP_VERSION#                     1238447314412412678  Standard
100             17           47            #CUSTOMIZE#                       1238447314412412678  Standard
100             18           44            #SCREEN_READER_TOGGLE#            1238447314412412678  Standard
100             29           3             #REGION_POSITION_04#              1238447314412412678  Standard
100             2            1             #SIDE_GLOBAL_NAVIGATION_LIST#     1238450358914412684  Left Side Column
100             5            7             #REGION_POSITION_01#              1238450358914412684  Left Side Column
100             8            7             #REGION_POSITION_02#              1238450358914412684  Left Side Column
100             11           7             #SUCCESS_MESSAGE#                 1238450358914412684  Left Side Column
100             11           24            #NOTIFICATION_MESSAGE#            1238450358914412684  Left Side Column
100             11           46            #GLOBAL_NOTIFICATION#             1238450358914412684  Left Side Column
100             13           9             #BODY#                            1238450358914412684  Left Side Column
100             17           41            #REGION_POSITION_05#              1238450358914412684  Left Side Column
100             19           43            #APP_VERSION#                     1238450358914412684  Left Side Column
100             20           45            #CUSTOMIZE#                       1238450358914412684  Left Side Column
100             21           42            #SCREEN_READER_TOGGLE#            1238450358914412684  Left Side Column
100             32           3             #REGION_POSITION_04#              1238450358914412684  Left Side Column
100             2            1             #SIDE_GLOBAL_NAVIGATION_LIST#     1238453832114412688  Left and Right Side Columns
100             5            7             #REGION_POSITION_01#              1238453832114412688  Left and Right Side Columns
100             8            7             #REGION_POSITION_02#              1238453832114412688  Left and Right Side Columns
100             11           7             #SUCCESS_MESSAGE#                 1238453832114412688  Left and Right Side Columns
100             11           24            #NOTIFICATION_MESSAGE#            1238453832114412688  Left and Right Side Columns
100             11           46            #GLOBAL_NOTIFICATION#             1238453832114412688  Left and Right Side Columns
100             13           9             #BODY#                            1238453832114412688  Left and Right Side Columns
100             17           41            #REGION_POSITION_05#              1238453832114412688  Left and Right Side Columns
100             19           43            #APP_VERSION#                     1238453832114412688  Left and Right Side Columns
100             20           45            #CUSTOMIZE#                       1238453832114412688  Left and Right Side Columns
100             21           42            #SCREEN_READER_TOGGLE#            1238453832114412688  Left and Right Side Columns
100             31           91            #EXPAND_COLLAPSE_SIDE_COL_LABEL#  1238453832114412688  Left and Right Side Columns
100             33           5             #REGION_POSITION_03#              1238453832114412688  Left and Right Side Columns
100             38           3             #REGION_POSITION_04#              1238453832114412688  Left and Right Side Columns
100             2            3             #REGION_POSITION_01#              1238457585422412691  Login
100             3            3             #SUCCESS_MESSAGE#                 1238457585422412691  Login
100             3            20            #NOTIFICATION_MESSAGE#            1238457585422412691  Login
100             3            42            #GLOBAL_NOTIFICATION#             1238457585422412691  Login
100             7            7             #BODY#                            1238457585422412691  Login
100             2            1             #SIDE_GLOBAL_NAVIGATION_LIST#     1238461053833412693  Marquee
100             5            7             #REGION_POSITION_01#              1238461053833412693  Marquee
100             8            7             #SUCCESS_MESSAGE#                 1238461053833412693  Marquee
100             8            24            #NOTIFICATION_MESSAGE#            1238461053833412693  Marquee
100             8            46            #GLOBAL_NOTIFICATION#             1238461053833412693  Marquee
100             10           9             #REGION_POSITION_02#              1238461053833412693  Marquee
100             13           9             #BODY#                            1238461053833412693  Marquee
100             17           41            #REGION_POSITION_05#              1238461053833412693  Marquee
Example for region, report, and button template:
-- region template 
select *
  from (select r.flow_id,
               t.*,
               to_char(r.id) as template_id,
               r.page_plug_template_name
          from apex_050100.wwv_flow_page_plug_templates r,
               table(utl_apex_substitutionstr.locate_all_substitutionstr(r.template)) t
         where r.flow_id = 100
         order by 1, 5, 2, 3)
 where rownum < 51
 
-- report template 
select *
  from (select r.application_id,
               t.*,
               to_char(r.template_id) as template_id,
               r.template_name
          from apex_application_temp_report r,
               table(utl_apex_substitutionstr.locate_all_substitutionstr(r.col_template1)) t
         where r.application_id = 100
         order by 1, 5, 2, 3)
 where rownum < 51

-- button template
select *
  from (select b.application_id,
               t.*,
               to_char(b.button_template_id) as template_id,
               b.template_name
          from apex_application_temp_button b,
               table(utl_apex_substitutionstr.locate_all_substitutionstr(b.template)) t
         where b.application_id = 100
         order by 1, 5, 2, 3)
 where rownum < 51

2. Locate attribute peer in html tag by substitution strings in clob template column

Function utl_apex_substitutionstr.locate_string_in_attr supports simple fuzzy searching, e.g. _ID#, retrieving results below.
SQL> select *
  2    from (select r.application_id,
  3                 t.*,
  4                 to_char(r.region_template_id) as region_template_id,
  5                 r.template_name
  6            from apex_application_temp_region r,
  7                 table(utl_apex_substitutionstr.locate_string_in_attr(p_keywords => '_ID#',
  8                                                                      p_clob     => r.template)) t
  9           where r.application_id like '8%' -- APEX theme applications
 10           order by 1 desc, 5, 2, 3)
 11   where rownum < 51
 12  /
Output:
APPLICATION_ID  LINE_NUMBER  COLUMN_INDEX  ATTRIBUTE_STRING                              REGION_TEMPLATE_ID   TEMPLATE_NAME
8851            1            6             id="#REGION_STATIC_ID#"                       3773955815682111625  Footer Toolbar (Fullscreen)
8851            1            30            data-id="#REGION_STATIC_ID#"                  3773955815682111625  Footer Toolbar (Fullscreen)
8851            1            6             id="#REGION_STATIC_ID#"                       3773956337432111625  Footer Toolbar (Fixed)
8851            1            30            data-id="#REGION_STATIC_ID#"                  3773956337432111625  Footer Toolbar (Fixed)
8851            1            6             id="#REGION_STATIC_ID#"                       3773956714688111626  Header Toolbar (Fullscreen)
8851            1            30            data-id="#REGION_STATIC_ID#"                  3773956714688111626  Header Toolbar (Fullscreen)
8851            1            6             id="#REGION_STATIC_ID#"                       3773957032569111626  Header Toolbar (Fixed)
8851            1            30            data-id="#REGION_STATIC_ID#"                  3773957032569111626  Header Toolbar (Fixed)
8851            1            6             id="#REGION_STATIC_ID#"                       3773960018627216969  Footer Toolbar (Displays at Bottom of Page)
8851            1            6             id="#REGION_STATIC_ID#"                       3773970912843347834  Plain (No Title)
8851            1            6             id="#REGION_STATIC_ID#"                       3773986516015153022  Button Group (Vertical)
8851            1            6             id="#REGION_STATIC_ID#"                       3773986716277171983  Button Group (Horizontal)
8851            1            6             id="#REGION_STATIC_ID#"                       3773989519618239167  Collapsible (Open)
8851            1            6             id="#REGION_STATIC_ID#"                       3773995014648303985  Collapsible Set
8851            1            6             id="#REGION_STATIC_ID#"                       3774008426020553267  Region (With Title Bar)
8851            1            6             id="#REGION_STATIC_ID#"                       3774032731297886006  Region (With Title)
8851            1            6             id="#REGION_STATIC_ID#"                       3802613893727675064  Collapsible (Closed)
8851            1            24            id="#REGION_STATIC_ID#"                       4376395493293226388  Panel
8842            1            43            id="#REGION_STATIC_ID#"                       2039236646100190748  Alert
8842            8            35            id="#REGION_STATIC_ID#_heading"               2039236646100190748  Alert
8842            1            6             id="#REGION_STATIC_ID#"                       2099079838218790610  Interactive Report
8842            1            105           aria-labelledby="#REGION_STATIC_ID#_heading"  2099079838218790610  Interactive Report
8842            2            32            id="#REGION_STATIC_ID#_heading"               2099079838218790610  Interactive Report
8842            1            44            id="#REGION_STATIC_ID#"                       2117602213152591491  Wizard Container
8842            1            68            id="#REGION_STATIC_ID#"                       2124982336649579661  Buttons Container
8842            5            40            id="#REGION_STATIC_ID#_heading"               2124982336649579661  Buttons Container
8842            1            50            id="#REGION_STATIC_ID#"                       2320668864738842174  Content Block
8842            1            6             id="#REGION_STATIC_ID#"                       2530016523834132090  Title Bar
8842            1            63            id="#REGION_STATIC_ID#"                       2662888092628347716  Collapsible
8842            1            6             id="#REGION_STATIC_ID#_parent"                2671226943886536762  Inline Dialog
8842            2            6             id="#REGION_STATIC_ID#"                       2671226943886536762  Inline Dialog
8842            1            48            id="#REGION_STATIC_ID#"                       2672571031438297268  Hero
8842            1            92            id="#REGION_ID#"                              2672711194551076376  Login
8842            4            31            id="#REGION_STATIC_ID#_heading"               2672711194551076376  Login
8842            1            63            id="#REGION_STATIC_ID#"                       2865840475322558786  Carousel Container
8842            4            32            id="#REGION_STATIC_ID#_heading"               2865840475322558786  Carousel Container
8842            1            68            id="#REGION_STATIC_ID#"                       3221725015618492759  Tabs Container
8842            1            6             id="#REGION_STATIC_ID#"                       3369790999010910123  Blank with Attributes (No Grid)
8842            1            44            id="#REGION_STATIC_ID#"                       4070912133526059312  Standard
8842            4            32            id="#REGION_STATIC_ID#_heading"               4070912133526059312  Standard
8842            1            6             id="#REGION_STATIC_ID#"                       4499993862448380551  Blank with Attributes
8826            1            53            id="#REGION_STATIC_ID#"                       1881212613469537571  Borderless Region (no heading)
8826            1            74            id="#REGION_STATIC_ID#"                       6345253084111033760  Borderless Region
8826            1            73            id="#REGION_STATIC_ID#"                       6345253184438033764  Bracketed Region
8826            1            62            id="#REGION_STATIC_ID#"                       6345253383030033764  Button Region with Title
8826            1            62            id="#REGION_STATIC_ID#"                       6345253488291033764  Button Region without Title
8826            1            56            id="#REGION_STATIC_ID#"                       6345253558701033764  Chart List
8826            1            56            id="#REGION_STATIC_ID#"                       6345253665722033764  Chart Region
8826            1            56            id="#REGION_STATIC_ID#"                       6345253766471033765  Form Region
8826            1            72            id="#REGION_STATIC_ID#"                       6345253880725033765  Hide and Show Region

3. Locate orphan substitution strings in clob template column

Function utl_apex_substitutionstr.locate_orphan_substitutionstr can get all orphan substitution strings, such as #BODY#, #REGION_ATTRIBUTES#, not in html attribute peer.
SQL> select r.application_id as app,
  2         t.line_number as l,
  3         t.column_index as c,
  4         t.attribute_string,
  5         to_char(r.region_template_id) as region_template_id,
  6         r.template_name
  7    from apex_application_temp_region r,
  8         table(utl_apex_substitutionstr.locate_orphan_substitutionstr(p_keywords => '#REGION_ATTRIBUTES#',
  9                                                                      p_clob     => r.template)) t
 10   where r.application_id = 105
 11   order by 1, 5, 2, 3
 12  /
note: function will return the whole line containing substitution string.
Output:
APP  L  C    ATTRIBUTE_STRING                                                                                                                                       REGION_TEMPLATE_ID   TEMPLATE_NAME
105  1  73   <div class="t-ContentBlock #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                                          1133955241583622872  Content Block
105  1  66   <div class="t-Alert #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                                                 1449931739077748731  Alert
105  1  29   <div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# class="#REGION_CSS_CLASSES#">                                                                         1449933605863748739  Blank with Attributes
105  1  91   <div class="t-ButtonRegion t-Form--floatLeft #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                        1449933724548748741  Buttons Container
105  1  86   <div class="t-Region t-Region--carousel #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                             1449939655295748748  Carousel Container
105  2  90   <div id="#REGION_STATIC_ID#"  class="t-DialogRegion #REGION_CSS_CLASSES# js-regionDialog" #REGION_ATTRIBUTES# style="display:none" title="#TITLE#">    1449942543202748751  Inline Dialog
105  1  71   <div class="t-HeroRegion #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                                            1449943574547748754  Hero
105  1  86   <div class="t-Region t-Region--hideShow #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                             1449944099227748755  Collapsible
105  1  29   <div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# class="t-IRR-region #REGION_CSS_CLASSES#" role="group" aria-labelledby="#REGION_STATIC_ID#_heading">  1449946754844748757  Interactive Report
105  1  108  <div class="t-Login-region t-Form--stretchInputs t-Form--labelsAbove #REGION_CSS_CLASSES#" id="#REGION_ID#" #REGION_ATTRIBUTES#>                       1449947053350748758  Login
105  1  67   <div class="t-Region #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                                                1449947288138748761  Standard
105  1  29   <div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# class="t-BreadcrumbRegion #REGION_CSS_CLASSES#">                                                      1449949806527748769  Title Bar
105  1  67   <div class="t-Wizard #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>                                                                1449950481029748771  Wizard Container
105  1  47   <div class="t-TabsRegion #REGION_CSS_CLASSES#" #REGION_ATTRIBUTES# id="#REGION_STATIC_ID#">                                                            1831042340212782454  Tabs Container
105  1  29   <div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# class="#REGION_CSS_CLASSES#">                                                                         1948339716123759720  Blank with Attributes (No Grid)
During testing, I found a typo in theme 8816 because the regexp in function locate_string_in_attr didn't locate keywords '_ID#' in region template 'Navigation Region', however the debug code commented out found the matching for _ID#. In template line 4: onclick="htmldb_ToggleWithImage('t#REGION_ID#','body#REGION_ID#')"", there are two quotation marks.
<table class="t16NavigationRegion" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# border="0" cellpadding="0" cellspacing="0" summary="" width="210" style="table-layout:fixed;">
<col width="25" /><col width="185" />
<tr>
<th align="center" style="border-top:2px solid #839fcd;border-bottom:1px solid #275096;background-color:#4167a7;" colspan="2"><img src="#IMAGE_PREFIX#themes/theme_16/navbar_top.gif" onclick="htmldb_ToggleWithImage('t#REGION_ID#','body#REGION_ID#')"" class="pb" /></th>
</tr>
...


Hope this helps...

Comments

Popular posts from this blog

RDS Customizable for APEX 5.1

Note for APEX 5.1 UI, Theme, Templates and Substitution Strings

Use Plugin Code Editor - Part 1/4: Demo