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.
Output:
Hope this helps...
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 MarqueeExample 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
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.
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
Post a Comment