For this site, I created a package (and some supporting objects) to dynamically show the source code and/or DDL of Apex and database objects.
It has turned into a bit of a monster size-wise, and it is not complete (i.e. a work in progress) but it serves its purpose very well.
I do plan to update the html it generates; that could be (and look) a bit less old school.

Using a package for this has a couple of obvious advantages: it makes sure the source code shown is always complete, up to date, and formatted the same consistent way.
A drawback is that for some object types, e.g. views and packages, generating the html output is quite slow. This slows down the entire page and puts a lot of strain on the (small) server I'm using. So I added a caching mechanism that makes sure the use of the Oracle views and packages is kept to a minimum.
Every time the source code of a particular (group of) objects is requested, the cache table is queried. If a non-expired entry is found, it is returned. If an entry is not found or expired, it is generated and merged into the cache table.
A database job checks to see if the cached version is expired (stale), and is scheduled to run every hour (max 1 hour seemed an acceptable lag). If the cache entry is expired, it marks it as such, and the next time the html is needed, a new version is generated and stored in the cache table.

Since htp.p only supports varchar2, it is limited to 32k. For package bodies that is not always sufficient, so I created an extra object_source_p procedure. It would probably have been better to change all the functions to procedures, but changing all the calls in Apex is a lot of work so I'm not going to bother with that.

Page

Identification
	Page ID	1120
	Name	Apex - General - Show Source Code

Region

Identification
	Sequence	1000
	Title	Code
	Type	PL/SQL
Source
	Region Source	begin htp.p ( ted_page_summary.page ) ; htp.p ( ted_page_summary.region ( p_item_string => 'Code' , p_show_source => true , p_show_header_and_footer => true ) ) ; htp.p ( ted_page_summary.object_source ( p_name => 'TED_PAGE_SUMMARY' , p_type => 'PACKAGE' ) ) ; ted_page_summary.object_source_p ( p_name => 'TED_PAGE_SUMMARY' , p_type => 'PACKAGE BODY' ) ; htp.p ( ted_page_summary.object_ddl ( p_name => 'TED_PAGE_SUMMARY_CACHES' , p_type => 'TABLE' ) ) ; htp.p ( ted_page_summary.scheduled_job ( p_name => 'TED_PAGE_SUMMARY_CACHE_REFRESH' ) ) ; end;

Package

Package
	Name	TED_PAGE_SUMMARY
	Source	package ted_page_summary as c_app_id constant number := nvl(nv('APP_ID'), 25384); c_page_id constant number := nv('APP_PAGE_ID'); type t_tr_rec is record( title varchar2(100) ,value clob ,codemirror varchar2(100) -- e.g. 'sql' ); type t_tr_tab is table of t_tr_rec index by binary_integer; type t_th_rec is record( title varchar2(100) ,indent pls_integer ,tr_tab t_tr_tab); type t_th_tab is table of t_th_rec index by binary_integer; type t_table_rec is record( title varchar2(100) ,comment varchar2(1000) ,th_tab t_th_tab); procedure htp_prn(p_clob in out nocopy clob); function htp_html_hr return varchar2; procedure cache_refresh; function page ( p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_display_attributes in boolean := false ,p_show_navigation in boolean := false ,p_show_javascript in boolean := false ,p_show_css in boolean := false ,p_show_html_header in boolean := false ,p_show_header_and_footer in boolean := false ,p_show_read_only in boolean := false ) return clob; function region ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_source in boolean := false ,p_show_user_interface in boolean := false ,p_show_master_detail in boolean := false ,p_show_attributes in boolean := false ,p_show_header_and_footer in boolean := false ,p_show_conditions in boolean := false ,p_show_read_only in boolean := false ,p_show_chart in boolean := false ,p_show_chart_series in boolean := false ,p_show_ir_link in boolean := false ) return clob; function cr_col ( p_item_string in varchar2 ,p_application_id in apex_application_page_rpt_cols.application_id%type := c_app_id ,p_page_id in apex_application_page_rpt_cols.page_id%type := c_page_id ,p_region_name in apex_application_page_rpt_cols.region_name%type := null ,p_show_appearance in boolean := false ,p_show_authorization in boolean := false ) return clob; function ir_col ( p_item_string in varchar2 ,p_application_id in apex_application_page_ir_col.application_id%type := c_app_id ,p_page_id in apex_application_page_ir_col.page_id%type := c_page_id ,p_region_name in apex_application_page_ir_col.region_name%type := null ,p_show_column_link in boolean := false ,p_show_appearance in boolean := false ,p_show_enable_users_to in boolean := false ,p_show_enable_users_to_item_string in varchar2 := null ,p_show_conditions in boolean := false ,p_show_authorization in boolean := false ) return clob; function ig_col ( p_item_string in varchar2 ,p_application_id in apex_appl_page_ig_columns.application_id%type := c_app_id ,p_page_id in apex_appl_page_ig_columns.page_id%type := c_page_id ,p_region_name in apex_appl_page_ig_columns.region_name%type := null ,p_show_column_link in boolean := false ,p_show_column_filter in boolean := false ) return clob; function button ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_appearance in boolean := false ,p_show_action in boolean := false ,p_show_advanced in boolean := false ,p_show_conditions in boolean := false ) return clob; function page_item ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_grid_layout in boolean := false ,p_show_label in boolean := false ,p_show_settings in boolean := false ,p_show_list_of_values in boolean := false ,p_show_element in boolean := false ,p_show_source in boolean := false ,p_show_default in boolean := false ,p_show_session_stare in boolean := false ,p_show_conditions in boolean := false ,p_show_read_only in boolean := false ) return clob; function dynamic_action ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ) return clob; function computation ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ) return clob; function process ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_source in boolean := false ,p_show_messages in boolean := false ,p_show_conditions in boolean := false ) return clob; function branch ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_conditions in boolean := false ) return clob; function column_group ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ) return clob; function object_source ( p_name in user_source.name%type ,p_type in user_source.type%type ) return clob; -- procedure object_source_p ( p_name in user_source.name%type ,p_type in user_source.type%type ); function object_ddl ( p_name in varchar2 ,p_type in varchar2 ) return clob; function scheduled_job(p_name in varchar2) return clob; function app_lov ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_show_source in boolean := true ) return clob; function breadcrumb ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ) return clob; end ted_page_summary;

Package Body

Package Body
	Name	TED_PAGE_SUMMARY
	Source	package body ted_page_summary as c_lf constant varchar2(10) := chr(13) || chr(10); c_table_style constant varchar2(1000) := ' text-align: left;' || ' vertical-align: top;' || ' font-size: 13px;' || ' border-collapse: collapse;'; c_td_style constant varchar2(1000) := 'border-width: 1px;' || ' border-style: solid;' || ' border-color: #A0A0A0;' || ' text-align: left;' || ' vertical-align: top;' || ' padding-right: 10px;' -- Note: padding-left differs per td || ' padding-top: 0px;'; g_th_colors apex_application_global.vc_arr2; g_table t_table_rec; procedure htp_prn(p_clob in out nocopy clob) is v_text varchar2(32000); v_clob clob := p_clob; begin while length(v_clob) > 0 loop begin if length(v_clob) > 16000 then v_text := substr(v_clob, 1, 16000); htp.prn(v_text); v_clob := substr(v_clob, length(v_text) + 1); else v_text := v_clob; htp.prn(v_text); v_clob := ''; v_text := ''; end if; end; end loop; end htp_prn; function htp_html_hr return varchar2 is begin return '<hr width="80%">'; end htp_html_hr; function escape_sc#(p_clob in clob) return clob is -- htf.escape_sc only handles varchar2 (max 32k), so chop p_clob into bite-size pieces and handle & append those -- "dbms_lob.getlength(p_clob) > 0" is needed to prevent "ORA-22275 invalid LOB locator specified" v_result clob; v_chunk_size pls_integer := 10000; v_clob_chunk clob; v_offset pls_integer := 1; begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); if dbms_lob.getlength(p_clob) > 0 then begin loop dbms_lob.read(p_clob, v_chunk_size, v_offset, v_clob_chunk); v_offset := v_offset + v_chunk_size; dbms_lob.append(v_result, htf.escape_sc(v_clob_chunk)); end loop; exception when no_data_found then null; when others then raise; end; end if; return(v_result); end escape_sc#; procedure add_table# ( p_title in varchar2 ,p_page_id in varchar2 := c_page_id ) is begin g_table := null; g_table.title := initcap(replace(p_title, '_', ' ')); if p_page_id <> c_page_id then g_table.comment := 'Note: This object is located on page ' || p_page_id; end if; end add_table#; procedure add_th# ( p_title in varchar2 ,p_indent in pls_integer := 1 ) is v_th_n pls_integer := g_table.th_tab.count + 1; begin g_table.th_tab(v_th_n).title := initcap(replace(p_title, '_', ' ')); g_table.th_tab(v_th_n).indent := p_indent; end add_th#; procedure add_tr# ( p_title in varchar2 ,p_value in clob ,p_codemirror in varchar2 := null ) is v_th_n pls_integer := g_table.th_tab.count; v_tr_n pls_integer := g_table.th_tab(v_th_n).tr_tab.count + 1; begin if p_value is not null then g_table.th_tab(v_th_n).tr_tab(v_tr_n).title := p_title; g_table.th_tab(v_th_n).tr_tab(v_tr_n).value := p_value; g_table.th_tab(v_th_n).tr_tab(v_tr_n).codemirror := p_codemirror; end if; end add_tr#; procedure render_table#(po_clob in out clob) is v_th_n pls_integer; v_tr_n pls_integer; v_tr_indent pls_integer; begin -- start table dbms_lob.append(po_clob, '<h3>' || g_table.title || '</h3>' || c_lf); if g_table.comment is not null then dbms_lob.append(po_clob, '<h5 style="color:#0000AA;">' || g_table.comment || '</h5>' || c_lf); end if; dbms_lob.append(po_clob, '<table style="' || c_table_style || '">' || c_lf); -- loop th v_th_n := g_table.th_tab.count; for i_th in 1 .. v_th_n loop -- start th v_tr_n := g_table.th_tab(i_th).tr_tab.count; if v_tr_n > 0 then -- start tr dbms_lob.append(po_clob, '<tr>' || c_lf); -- add title v_tr_indent := 10 + (g_table.th_tab(i_th).indent - 1) * 20; dbms_lob.append(po_clob , '<td rowspan="' || trim(v_tr_n + 1) || '"' || ' style="' || c_td_style || ' background-color: ' || g_th_colors(g_table.th_tab(i_th).indent) || ';' || ' padding-left: ' || trim(v_tr_indent) || 'px;' -- OLD: || ' min-width: ' || trim(120 - v_tr_indent) || 'px;' || ' white-space: nowrap;' || '">' || g_table.th_tab(i_th).title || '</td>' || c_lf); -- end tr dbms_lob.append(po_clob, '</tr>' || c_lf); -- loop tr for i_tr in 1 .. v_tr_n loop -- start tr dbms_lob.append(po_clob, '<tr>' || c_lf); -- add title dbms_lob.append(po_clob , '<td' || ' style="' || c_td_style -- OLD: || ' min-width: 150px;' || ' white-space: nowrap;' || ' padding-left: 10px;' || '">' || g_table.th_tab(i_th).tr_tab(i_tr).title || '</td>' || c_lf); -- add value dbms_lob.append(po_clob ,'<td' || ' style="' || c_td_style || ' width: 99%;' || ' padding-left: 10px;' || '">' || '<textarea style="resize: both;" name="p_t01" rows="5" cols="80" id="" class="textarea codemirror-' || nvl(g_table.th_tab(i_th).tr_tab(i_tr).codemirror, 'codemirror-plain') || '" wrap="virtual">' || escape_sc#(g_table.th_tab(i_th).tr_tab(i_tr).value) || '</textarea>' || '</td>' || c_lf); -- end tr dbms_lob.append(po_clob, '</tr>' || c_lf); end loop; end if; end loop; -- end table dbms_lob.append(po_clob, '</table>' || c_lf); -- br after table dbms_lob.append(po_clob, '<br/>' || c_lf); end render_table#; function cache_key# ( p_function_name in varchar2 ,p_application_id in apex_application_pages.application_id%type := null ,p_page_id in apex_application_pages.page_id%type := null ,p_region_name in varchar2 := null ,p_item_string in varchar2 := null ,p_object_name in varchar2 := null ,p_object_type in varchar2 := null ) return ted_page_summary_caches.key%type is begin return p_function_name || '#' || p_application_id || '#' || p_page_id || '#' || p_region_name || '#' || p_item_string || '#' || p_object_name || '#' || p_object_type || '#'; end cache_key#; procedure cache_refresh -- remove all entries that have not been used for 30 days or more; they are most likely not needed anymore -- set expired_yn = 'Y' for: -- all cache entries that are older than the pages/objects they contain -- all cache entries that are older than this package (which means the result could be different after a refresh) is pragma autonomous_transaction; cursor c1(cp_pck_ddl_date in date) is -- pages select c1.* from ted_page_summary_caches c1 where c1.rowid in (select c.rowid as row_id from apex_applications a ,apex_application_pages p ,ted_page_summary_caches c where a.application_id = c_app_id and a.application_id = p.application_id and instr(lower(c.key), '#' || trim(p.page_id) || '#') <> 0 -- any cache of this page and (p.last_updated_on > c.date_updated -- the page is newer than the cache or c.date_updated < cp_pck_ddl_date -- the cache entry is older than this package )) for update; cursor c2(cp_pck_ddl_date in date) is -- objects select c1.* from ted_page_summary_caches c1 where c1.rowid in (select c.rowid as row_id from user_objects o ,ted_page_summary_caches c where instr(lower(c.key), lower('#' || o.object_name || '#' || o.object_type || '#')) <> 0 -- any cache of this object and (o.last_ddl_time > c.date_updated -- the object is newer than the cache or c.date_updated < cp_pck_ddl_date -- the cache entry is older than this package )) for update; l_pck_ddl_date date; begin delete from ted_page_summary_caches t where t.date_used is null and t.date_updated < trunc(sysdate) - 30; select max(o.last_ddl_time) into l_pck_ddl_date from user_objects o where o.object_name = $$plsql_unit; for r1 in c1(cp_pck_ddl_date => l_pck_ddl_date) loop update ted_page_summary_caches c set c.expired_yn = 'Y' where current of c1; end loop; for r2 in c2(cp_pck_ddl_date => l_pck_ddl_date) loop update ted_page_summary_caches c set c.expired_yn = 'Y' where current of c2; end loop; commit; end cache_refresh; procedure cache_select# ( p_function_name in varchar2 ,p_application_id in apex_application_pages.application_id%type := null ,p_page_id in apex_application_pages.page_id%type := null ,p_region_name in varchar2 := null ,p_item_string in varchar2 := null ,p_object_name in varchar2 := null ,p_object_type in varchar2 := null ,po_result in out clob ) is pragma autonomous_transaction; v_key ted_page_summary_caches.key%type := cache_key#(p_function_name => p_function_name ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_region_name => p_region_name ,p_item_string => p_item_string ,p_object_name => p_object_name ,p_object_type => p_object_type); v_row ted_page_summary_caches%rowtype; v_hit_or_miss varchar2(4) := 'miss'; l_used_by_25384_page_ids ted_page_summary_caches.used_by_25384_page_ids%type; begin if v('APP_BUILDER_SESSION') is not null then -- when we are developing, getting cached results is confusing return; end if; select * into v_row from ted_page_summary_caches where key = v_key; if v_row.expired_yn = 'N' then -- cache hit v_hit_or_miss := 'hit'; end if; if v_hit_or_miss = 'hit' then po_result := v_row.result; -- update hit update ted_page_summary_caches set hits = hits + 1 where key = v_key; else -- update miss update ted_page_summary_caches set misses = misses + 1 where key = v_key; end if; l_used_by_25384_page_ids := v_row.used_by_25384_page_ids; -- append the calling page_id to l_used_by_25384_page_ids if needed select listagg(val, ',') within group(order by val) into l_used_by_25384_page_ids from (select column_value as val from table(apex_string.split(v_row.used_by_25384_page_ids, ',')) union select v('APP_PAGE_ID') as val from dual where nv('APP_ID') = 25384 and nv('APP_PAGE_ID') not in (0, 2, 3)); -- update date used with sysdate and l_used_by_25384_page_ids update ted_page_summary_caches set date_used = sysdate ,used_by_25384_page_ids = l_used_by_25384_page_ids where key = v_key; commit; exception when no_data_found then null; end cache_select#; procedure cache_upsert# ( p_function_name in varchar2 ,p_application_id in apex_application_pages.application_id%type := null ,p_page_id in apex_application_pages.page_id%type := null ,p_region_name in varchar2 := null ,p_item_string in varchar2 := null ,p_object_name in varchar2 := null ,p_object_type in varchar2 := null ,p_result in clob ,p_warnings in varchar2 := null ) is pragma autonomous_transaction; v_key ted_page_summary_caches.key%type := cache_key#(p_function_name => p_function_name ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_region_name => p_region_name ,p_item_string => p_item_string ,p_object_name => p_object_name ,p_object_type => p_object_type); begin insert into ted_page_summary_caches (key ,result ,date_updated ,hits ,misses ,expired_yn ,warnings ,used_by_25384_page_ids) values (v_key ,p_result ,sysdate ,0 ,0 ,'N' ,rtrim(p_warnings, c_lf) ,trim(p_page_id)); commit; exception when dup_val_on_index then update ted_page_summary_caches set result = p_result ,date_updated = sysdate ,expired_yn = 'N' ,warnings = rtrim(p_warnings, c_lf) where key = v_key; commit; end cache_upsert#; function page ( p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_display_attributes in boolean := false ,p_show_navigation in boolean := false ,p_show_javascript in boolean := false ,p_show_css in boolean := false ,p_show_html_header in boolean := false ,p_show_header_and_footer in boolean := false ,p_show_read_only in boolean := false ) return clob is cursor c1 is select a.* from apex_application_pages a where a.application_id = p_application_id and a.page_id = p_page_id; v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'page', p_application_id => p_application_id, p_page_id => p_page_id, po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for c1_rec in c1 loop add_table#(p_title => 'Page', p_page_id => p_page_id); -- Identification (old: Name) add_th#('Identification'); add_tr#('Page ID', trim(c1_rec.page_id)); add_tr#('Name', c1_rec.page_name); add_tr#('Page Alias', c1_rec.page_alias); add_tr#('Group', c1_rec.page_group); -- Appearance (old: Display Attributes) if p_show_display_attributes then add_th#('Appearance'); add_tr#('Page Template', c1_rec.page_template); end if; -- Navigation if p_show_navigation then add_th#('Navigation'); add_tr#('Warn on Unsaved Changes', nvl(c1_rec.warn_on_unsaved_changes, 'Page Default')); end if; -- JavaScript if p_show_javascript then add_th#('JavaScript'); add_tr#('File URLs', c1_rec.javascript_file_urls); add_tr#('Function and Global<br/>Variable Declaration', c1_rec.javascript_code, p_codemirror => 'javascript'); add_tr#('Execute when Page Loads', c1_rec.javascript_code_onload, p_codemirror => 'javascript'); end if; -- CSS if p_show_css then add_th#('CSS'); add_tr#('File URLs', c1_rec.css_file_urls); add_tr#('Inline', c1_rec.inline_css, p_codemirror => 'css'); end if; -- HTML Header if p_show_html_header then add_th#('HTML Header'); add_tr#('HTML Header', c1_rec.page_html_header, p_codemirror => 'html'); add_tr#('Page HTML Body Attribute', c1_rec.page_html_onload, p_codemirror => 'html'); -- ??? end if; -- Header and Footer if p_show_header_and_footer then add_th#('Header and Footer'); add_tr#('Header Text', trim(c1_rec.header_text)); add_tr#('Footer Text', trim(c1_rec.footer_text)); end if; -- Read Only if p_show_read_only then add_th#('Read Only'); add_tr#('Read Only Condition Type', trim(c1_rec.read_only_condition_type)); add_tr#('Expression 1', trim(c1_rec.read_only_condition_exp1)); add_tr#('Expression 2', trim(c1_rec.read_only_condition_exp2)); end if; -- render_table render_table#(v_result); end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'page' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end page; function region ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_source in boolean := false ,p_show_user_interface in boolean := false ,p_show_master_detail in boolean := false ,p_show_attributes in boolean := false ,p_show_header_and_footer in boolean := false ,p_show_conditions in boolean := false ,p_show_read_only in boolean := false ,p_show_chart in boolean := false ,p_show_chart_series in boolean := false ,p_show_ir_link in boolean := false ) return clob is cursor c1(p_item in varchar2) is select a.* ,reg_md.region_name as master_region_name ,bc.breadcrumb_name from apex_application_page_regions a left join apex_application_page_regions reg_md on reg_md.region_id = a.master_region_id left join apex_application_breadcrumbs bc on ('M' || trim(bc.breadcrumb_id) = a.source_type_code) where a.application_id = p_application_id and a.page_id = p_page_id and (lower(a.region_name) = lower(p_item) or lower(a.static_id) = lower(p_item)); cursor c_chart_s2(p_region_id in apex_application_page_chart_s.region_id%type) is select c2.* from apex_application_page_chart_s c2 where c2.region_id = p_region_id order by c2.series_seq; cursor c_ir(p_region_id in apex_application_page_ir.region_id%type) is select ir.* from apex_application_page_ir ir where ir.region_id = p_region_id; v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'region' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Region', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Sequence', trim(c1_rec.display_sequence)); add_tr#('Title', c1_rec.region_name); add_tr#('Type', c1_rec.source_type); -- Source if p_show_source then add_th#('Source'); add_tr#('Location', c1_rec.location); add_tr#('Query Type', c1_rec.query_type); add_tr#('Table Owner', c1_rec.table_owner); add_tr#('Table Name', c1_rec.table_name); add_tr#('Where Clause', c1_rec.where_clause, p_codemirror => 'sql'); add_tr#('Order By Clause', c1_rec.order_by_clause, p_codemirror => 'sql'); add_tr#('Region Source' ,c1_rec.region_source ,(case when c1_rec.source_type_code in ('DYNAMIC_QUERY', 'SQL_QUERY', 'NATIVE_JET_CHART') then 'sql' when c1_rec.source_type_code in ('PLSQL_PROCEDURE', 'FUNCTION_RETURNING_SQL_QUERY') then 'plsql' when c1_rec.source_type_code in ('STATIC_TEXT') and c1_rec.source_type = 'HTML/Text' then 'html' else null end)); add_tr#('Page Items to Submit', c1_rec.ajax_items_to_submit); add_tr#('Breadcrumb', c1_rec.breadcrumb_name); end if; if p_show_chart then null; end if; if p_show_chart_series then for c_chart_s2_rec in c_chart_s2(p_region_id => c1_rec.region_id) loop add_th#('Chart Series'); add_tr#('Series Name', c_chart_s2_rec.series_name); add_tr#('Series Type', c_chart_s2_rec.series_type); add_tr#('Sequence', trim(c_chart_s2_rec.series_seq)); add_tr#('Data Source Type', c_chart_s2_rec.data_source_type); add_tr#('Data Source', c_chart_s2_rec.data_source, p_codemirror => 'sql'); add_tr#('Page Items to Submit', c_chart_s2_rec.items_to_submit); end loop; end if; -- User Interface if p_show_user_interface then add_th#('Layout'); add_tr#('Parent Region', c1_rec.parent_region_name); -- add_th#('Appearance'); add_tr#('Template', c1_rec.template); end if; -- Master Detail if p_show_master_detail then add_th#('Master Detail'); add_tr#('Master Region', c1_rec.master_region_name); end if; -- Attributes if p_show_attributes then add_th#('Attributes'); add_tr#('Static ID', trim(c1_rec.static_id)); add_tr#('Region CSS Classes', trim(c1_rec.region_css_classes)); add_tr#('Region Attributes', trim(c1_rec.region_attributes_substitution)); if c1_rec.display_region_selector <> 'No' then add_tr#('Region Display Selector', trim(c1_rec.display_region_selector)); end if; if c1_rec.enable_csv_output = 'Yes' then add_tr#('CSV Export Enabled', c1_rec.enable_csv_output); add_tr#('Link Text', c1_rec.csv_link_label); end if; end if; -- Header and Footer if p_show_header_and_footer then add_th#('Header and Footer'); add_tr#('Region Header', trim(c1_rec.region_header_text), p_codemirror => 'html'); add_tr#('Region Footer', trim(c1_rec.region_footer_text), p_codemirror => 'html'); end if; -- Conditions if p_show_conditions then add_th#('Conditions'); add_tr#('Condition Type', trim(c1_rec.condition_type)); add_tr#('Expression 1' ,trim(c1_rec.condition_expression1) ,(case when c1_rec.condition_type in ('PL/SQL Expression') then 'plsql' when c1_rec.condition_expression2 in ('PLSQL') then 'plsql' else null end)); add_tr#('Expression 2', trim(c1_rec.condition_expression2)); end if; -- Read Only if p_show_read_only then add_th#('Read Only'); add_tr#('Read Only Condition Type', trim(c1_rec.read_only_condition_type)); add_tr#('Expression 1', trim(c1_rec.read_only_condition_exp1)); add_tr#('Expression 2', trim(c1_rec.read_only_condition_exp2)); end if; -- IR Link if p_show_ir_link then for c_ir_rec in c_ir(p_region_id => c1_rec.region_id) loop add_th#('IR Link'); add_tr#('Link Column', trim(c_ir_rec.detail_link_type)); add_tr#('Target', trim(c_ir_rec.detail_link_target)); add_tr#('Link Icon', trim(c_ir_rec.detail_link_text)); add_tr#('Link Atttributes', trim(c_ir_rec.detail_link_attributes)); end loop; end if; -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'region' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end region; function cr_col ( p_item_string in varchar2 ,p_application_id in apex_application_page_rpt_cols.application_id%type := c_app_id ,p_page_id in apex_application_page_rpt_cols.page_id%type := c_page_id ,p_region_name in apex_application_page_rpt_cols.region_name%type := null ,p_show_appearance in boolean := false ,p_show_authorization in boolean := false ) return clob is cursor c1(p_item in varchar2) is select a.* from apex_application_page_rpt_cols a where a.application_id = p_application_id and a.page_id = p_page_id and lower(a.column_alias) = lower(p_item) and (a.region_name = p_region_name or p_region_name is null); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'cr_col' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_region_name => p_region_name ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Report Column', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Column Name', c1_rec.column_alias); add_tr#('Type', c1_rec.display_as); -- Heading add_th#('Heading'); add_tr#('Heading', c1_rec.heading); -- Appearance if p_show_appearance then add_th#('Appearance'); add_tr#('Format Mask', c1_rec.format_mask); end if; -- Column Formatting add_th#('Column Formatting'); add_tr#('HTML Expression', c1_rec.html_expression); -- [Security] Authorization Scheme if p_show_authorization then add_th#('Security'); add_tr#('Authorization Scheme', c1_rec.authorization_scheme); end if; -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'cr_col' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_region_name => p_region_name ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end cr_col; function ir_col ( p_item_string in varchar2 ,p_application_id in apex_application_page_ir_col.application_id%type := c_app_id ,p_page_id in apex_application_page_ir_col.page_id%type := c_page_id ,p_region_name in apex_application_page_ir_col.region_name%type := null ,p_show_column_link in boolean := false ,p_show_appearance in boolean := false ,p_show_enable_users_to in boolean := false ,p_show_enable_users_to_item_string in varchar2 := null ,p_show_conditions in boolean := false ,p_show_authorization in boolean := false ) return clob is cursor c1(p_item in varchar2) is select a.* from apex_application_page_ir_col a where a.application_id = p_application_id and a.page_id = p_page_id and lower(a.column_alias) = lower(p_item) and (a.region_name = p_region_name or p_region_name is null); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'ir_col' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_region_name => p_region_name ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Report Column', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Column Name', c1_rec.column_alias); add_tr#('Type', c1_rec.display_text_as || (case when c1_rec.display_text_as = 'ESCAPE_SC' then ' (Escape special characters)' else null end)); -- Heading add_th#('Heading'); add_tr#('Heading', c1_rec.report_label); add_tr#('Alternative Label', c1_rec.alternative_label); -- Column Link if p_show_column_link then add_th#('Column Link'); add_tr#('Link Text', c1_rec.column_linktext); add_tr#('Link Attributes', c1_rec.column_link_attr); add_tr#('URL', c1_rec.column_link); end if; -- Appearance if p_show_appearance then add_th#('Appearance'); add_tr#('Format Mask', c1_rec.format_mask); end if; -- Column Formatting add_th#('Column Formatting'); add_tr#('HTML Expression', c1_rec.html_expression); -- Enable Users To if p_show_enable_users_to then add_th#('Column Formatting'); -- Noformat Start if instr(coalesce(p_show_enable_users_to_item_string, 'Hide'), 'Hide') <> 0 then add_tr#('Hide', c1_rec.allow_hide); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Sort'), 'Sort') <> 0 then add_tr#('Sort', c1_rec.allow_sorting); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Filter'), 'Filter') <> 0 then add_tr#('Filter', c1_rec.allow_filtering); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Highlight'), 'Highlight') <> 0 then add_tr#('Highlight', c1_rec.allow_highlighting); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Control Break'), 'Control Break') <> 0 then add_tr#('Control Break', c1_rec.allow_ctrl_breaks); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Aggregate'), 'Aggregate') <> 0 then add_tr#('Aggregate', c1_rec.allow_aggregations); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Compute'), 'Compute') <> 0 then add_tr#('Compute', c1_rec.allow_computations); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Chart'), 'Chart') <> 0 then add_tr#('Chart', c1_rec.allow_charting); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Group By'), 'Group By') <> 0 then add_tr#('Group By', c1_rec.allow_group_by); end if; if instr(coalesce(p_show_enable_users_to_item_string, 'Pivot'), 'Pivot') <> 0 then add_tr#('Pivot', c1_rec.allow_pivot); end if; -- Noformat End end if; -- Server-side Condition if p_show_conditions then add_th#('Server-side Condition'); add_tr#('Type', c1_rec.display_condition_type); if c1_rec.display_condition2 = 'PLSQL' then add_tr#('Language', c1_rec.display_condition2); add_tr#('PL/SQL Expression', c1_rec.display_condition); elsif c1_rec.display_condition2 is not null then add_tr#('Condition' ,c1_rec.display_condition2 || (case when c1_rec.display_condition is not null then ' - ' || c1_rec.display_condition end)); end if; end if; -- [Security] Authorization Scheme if p_show_authorization then add_th#('Security'); add_tr#('Authorization Scheme', c1_rec.authorization_scheme); end if; -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'ir_col' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_region_name => p_region_name ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end ir_col; function ig_col ( p_item_string in varchar2 ,p_application_id in apex_appl_page_ig_columns.application_id%type := c_app_id ,p_page_id in apex_appl_page_ig_columns.page_id%type := c_page_id ,p_region_name in apex_appl_page_ig_columns.region_name%type := null ,p_show_column_link in boolean := false ,p_show_column_filter in boolean := false ) return clob is cursor c1(p_item in varchar2) is select a.* ,a.attributes.based_on as attributes_based_on -- before Apex 24.2: a.attribute_02 ,lov.list_of_values_name from apex_appl_page_ig_columns a left join apex_application_lovs lov on lov.lov_id = a.lov_id where a.application_id = p_application_id and a.page_id = p_page_id and lower(a.name) = lower(p_item) and (a.region_name = p_region_name or p_region_name is null); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'ig_col' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_region_name => p_region_name ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Report Column', p_page_id => p_page_id); -- Column Definition add_th#('Definition'); add_tr#('Column Name', c1_rec.name); add_tr#('Type', c1_rec.item_type); add_th#('Heading'); add_tr#('Column Heading', c1_rec.heading); add_th#('Settings'); add_tr#('Based On', c1_rec.attributes_based_on); add_th#('Appearance'); add_tr#('Format Mask', c1_rec.format_mask); if c1_rec.attributes_based_on = 'LOV' or c1_rec.lov_type is not null then add_th#('Column Definition'); add_tr#('Type', c1_rec.lov_type); add_tr#('List of Values', c1_rec.list_of_values_name); add_tr#('SQL Query', c1_rec.lov_source); add_tr#('Display Extra Values', c1_rec.lov_display_extra); add_tr#('Display Null', c1_rec.lov_display_null); end if; -- Column Link if p_show_column_link then add_th#('Link'); add_tr#('Link Text', c1_rec.link_text); add_tr#('Link Attributes', c1_rec.link_attributes); add_tr#('Link Target', c1_rec.link_target); end if; -- Column Filter if p_show_column_filter then add_th#('Column Filter'); add_tr#('Enabled', c1_rec.enable_filter); end if; -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'ig_col' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_region_name => p_region_name ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end ig_col; function button ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_appearance in boolean := false ,p_show_action in boolean := false ,p_show_advanced in boolean := false ,p_show_conditions in boolean := false ) return clob is cursor c1(p_item in varchar2) is select a.* from apex_application_page_buttons a where a.application_id = p_application_id and a.page_id = p_page_id and lower(a.button_name) = lower(p_item); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'button' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Button', p_page_id => p_page_id); -- Name add_th#('Name'); add_tr#('Button Name ', c1_rec.button_name); add_tr#('Text Label / Alt', c1_rec.label); -- Displayed add_th#('Displayed'); add_tr#('Sequence', trim(c1_rec.button_sequence)); add_tr#('Display in Region', c1_rec.region); -- Appearance if p_show_appearance then add_th#('Appearance'); add_tr#('Button Template', trim(c1_rec.button_template)); add_tr#('CSS Classes', trim(c1_rec.button_css_classes)); add_tr#('Icon', trim(c1_rec.icon_css_classes)); end if; -- Behavior (old: Action When Button Clicked) if p_show_action then add_th#('Behavior'); add_tr#('Action', trim(c1_rec.button_action)); add_tr#('Target (Redirect URL)', trim(c1_rec.redirect_url)); -- ################# add_tr#('Execute Validations', trim(c1_rec.execute_validations)); add_tr#('Database Action', trim(c1_rec.database_action)); end if; -- Advanced if p_show_advanced then add_th#('Advanced'); add_tr#('Static ID', trim(c1_rec.button_static_id)); add_tr#('Custom Attributes', trim(c1_rec.button_attributes)); end if; -- Conditions if p_show_conditions then add_th#('Conditions'); add_tr#('Condition Type', trim(c1_rec.condition_type)); add_tr#('Expression 1', trim(c1_rec.condition_expression1)); add_tr#('Expression 2', trim(c1_rec.condition_expression2)); end if; -- Security -- Configuration -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'button' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end button; function page_item ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_grid_layout in boolean := false ,p_show_label in boolean := false ,p_show_settings in boolean := false ,p_show_list_of_values in boolean := false ,p_show_element in boolean := false ,p_show_source in boolean := false ,p_show_default in boolean := false ,p_show_session_stare in boolean := false ,p_show_conditions in boolean := false ,p_show_read_only in boolean := false ) return clob is cursor c1(p_item in varchar2) is select a.* ,a.attributes.submit_when_enter_pressed as attribute_submit_when_enter_pressed -- before Apex 24.2: a.attribute_01 ,a.attributes.subtype -- before Apex 24.2: a.attribute_04 from apex_application_page_items a where a.application_id = p_application_id and a.page_id = p_page_id and lower(a.item_name) = lower(p_item); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'page_item' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Item', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Name', c1_rec.item_name); add_tr#('Display As', c1_rec.display_as); -- Label if p_show_label then add_tr#('Label', trim(c1_rec.label)); end if; -- Settings if p_show_settings then add_th#('Settings'); add_tr#('Value Required', trim(c1_rec.is_required)); if c1_rec.display_as_code = 'NATIVE_SELECT_LIST' then add_tr#('Page Action on Selection', trim(c1_rec.attribute_submit_when_enter_pressed)); end if; add_tr#('Subtype', trim(c1_rec.subtype)); end if; -- Layout (old: Grid Layout) if p_show_grid_layout then add_th#('Layout'); add_tr#('Sequence', trim(c1_rec.display_sequence)); add_tr#('Region', c1_rec.region); add_tr#('Start New Grid', trim(c1_rec.new_grid)); add_tr#('Start New Row', trim(c1_rec.new_grid_row)); add_tr#('Start New Column', trim(c1_rec.new_grid_column)); add_tr#('Column', trim(c1_rec.grid_column)); add_tr#('Column Span', trim(c1_rec.column_span)); add_tr#('Row Span', trim(c1_rec.row_span)); add_tr#('Column CSS Classes', trim(c1_rec.grid_column_css_classes)); add_tr#('Column Attributes', trim(c1_rec.grid_column_attributes)); end if; -- List of Values if p_show_list_of_values then add_th#('List of Values'); add_tr#('Named LOV', trim(c1_rec.lov_named_lov)); add_tr#('Display Extra Values', trim(c1_rec.lov_display_extra)); add_tr#('Display Null Value', trim(c1_rec.lov_display_null)); if c1_rec.lov_display_null = 'Yes' then add_tr#('Null Display Value', trim(c1_rec.lov_null_text)); add_tr#('Null Return Value', trim(c1_rec.lov_null_value)); end if; add_tr#('Cascading LOV Parent Item(s)', trim(c1_rec.lov_cascade_parent_items)); add_tr#('List of values definition' ,trim(c1_rec.lov_definition) ,(case when trim(c1_rec.lov_definition) not like 'STATIC%' then 'sql' else null end)); end if; -- Advanced (old: Element) if p_show_element then add_th#('Advanced'); add_tr#('CSS Classes', trim(c1_rec.html_form_element_css_classes)); add_tr#('Custom Attributes', trim(c1_rec.html_form_element_attributes)); add_tr#('Pre Text', trim(c1_rec.pre_element_text)); add_tr#('Post Text', trim(c1_rec.post_element_text)); add_tr#('Warn on Unsaved Changes', trim(c1_rec.warn_on_unsaved_changes)); end if; -- Source if p_show_source then add_th#('Source'); add_tr#('Source Used', trim(c1_rec.source_used)); add_tr#('Source Type', trim(c1_rec.item_source_type)); add_tr#('Source value or expression' ,trim(c1_rec.item_source) ,(case when trim(c1_rec.item_source_type) in ('Function Body') then 'plsql' else null end)); add_tr#('Post Calculation<br/>Computation', trim(c1_rec.source_post_computation)); end if; -- Default if p_show_default then add_th#('Default'); add_tr#('Default Value' ,trim(c1_rec.item_default) ,(case when trim(c1_rec.item_default_type) like 'PLSQL%' then 'sql' when c1_rec.item_default_type = 'FUNCTION_BODY' then 'plsql' else null end)); add_tr#('Default Value Type', trim(c1_rec.item_default_type)); end if; -- Session State if p_show_session_stare then add_th#('Session State'); add_tr#('Data Type', c1_rec.session_state_data_type); add_tr#('Storage' ,(case c1_rec.maintain_session_state when 'Per Session' then 'Per Session (Persistent)' when 'Do Not Save Session State' then 'Per Request (Memory Only)' else c1_rec.maintain_session_state end)); end if; -- Server-side Condition if p_show_conditions then add_th#('Server-side Condition'); add_tr#('Type', trim(c1_rec.condition_type)); add_tr#('Expression 1', trim(c1_rec.condition_expression1)); add_tr#('Expression 2', trim(c1_rec.condition_expression2)); end if; -- Read Only if p_show_read_only then add_th#('Read Only'); add_tr#('Read Only Condition Type', trim(c1_rec.read_only_condition_type)); add_tr#('Expression 1', trim(c1_rec.read_only_condition_exp1)); add_tr#('Expression 2', trim(c1_rec.read_only_condition_exp2)); end if; -- Security -- Configuration -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'page_item' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end page_item; function dynamic_action ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ) return clob is cursor c1(p_item in varchar2) is select a.* from apex_application_page_da a where a.application_id = p_application_id and a.page_id = p_page_id and lower(a.dynamic_action_name) = lower(p_item); cursor c2(p_dynamic_action_id in apex_application_page_da.dynamic_action_id%type) is select a.* ,json_value(a.attributes, '$.js_code' returning varchar2 truncate) as att_js_code ,json_value(a.attributes, '$.plsql_code' returning varchar2 truncate) as att_plsql_code ,json_value(a.attributes, '$.type' returning varchar2 truncate) as att_type ,json_value(a.attributes, '$.return_item' returning varchar2 truncate) as att_return_item ,json_value(a.attributes, '$.items_to_return' returning varchar2 truncate) as att_items_to_return ,json_value(a.attributes, '$.suppress_change_event' returning varchar2 truncate) as att_suppress_change_event ,json_value(a.attributes, '$.plsql_expression' returning varchar2 truncate) as att_plsql_expression ,json_value(a.attributes, '$.escape_special_characters' returning varchar2 truncate) as att_escape_special_characters ,json_value(a.attributes, '$.items_to_submit' returning varchar2 truncate) as att_items_to_submit ,json_value(a.attributes, '$.js_expression' returning varchar2 truncate) as att_js_expression ,json_value(a.attributes, '$.message' returning varchar2 truncate) as att_message ,json_value(a.attributes, '$.show_on_same_line' returning varchar2 truncate) as att_show_on_same_line ,json_value(a.attributes, '$.hide_page_items_on_same_line' returning varchar2 truncate) as att_hide_page_items_on_same_line ,json_value(a.attributes, '$.attribute_01' returning varchar2 truncate) as att_attribute_01 ,json_value(a.attributes, '$.attribute_02' returning varchar2 truncate) as att_attribute_02 from apex_application_page_da_acts a where a.application_id = p_application_id and a.page_id = p_page_id and a.dynamic_action_id = p_dynamic_action_id order by a.dynamic_action_event_result desc -- True/False ,a.action_sequence; v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'dynamic_action' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Dynamic Action', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Name', c1_rec.dynamic_action_name); -- Execution Options add_th#('Execution Options'); add_tr#('Sequence', to_clob(c1_rec.dynamic_action_sequence)); -- When add_th#('When'); add_tr#('Event', c1_rec.when_event_name); add_tr#('Custom Event', c1_rec.when_event_custom_name); add_tr#('Selection Type', c1_rec.when_selection_type); add_tr#(c1_rec.when_selection_type || '(s)', c1_rec.when_element); -- DOM Object, Item ... add_tr#('Region', c1_rec.when_region); add_tr#('Button', c1_rec.when_button); -- Client-side Condition add_th#('Client-side Condition'); add_tr#('Type', c1_rec.when_condition); add_tr#('Expression', c1_rec.when_expression); -- Server-side Condition add_th#('Server-side Condition'); add_tr#('Type', c1_rec.condition_type); add_tr#('Expression 1' ,c1_rec.condition_expression1 ,(case when c1_rec.condition_type in ('Exists (SQL query returns at least one row)') then 'sql' else null end)); add_tr#('Expression 2' ,c1_rec.condition_expression2 ,(case when c1_rec.condition_type in ('Exists (SQL query returns at least one row)') then 'sql' else null end)); -- Advanced add_th#('Advanced'); add_tr#('Event Scope' ,(case c1_rec.when_event_scope when 'bind' then 'Static' when 'live' then 'Dynamic' when 'one' then 'Once' else c1_rec.when_event_scope end)); -- True / False Actions for c2_rec in c2(p_dynamic_action_id => c1_rec.dynamic_action_id) loop add_th#(c2_rec.dynamic_action_event_result || ' Action'); add_tr#(' ', ' '); -- Identification add_th#('Identification', 2); add_tr#('Action ', c2_rec.action_name); -- Settings add_th#('Settings', 2); case c2_rec.action_name when 'Execute JavaScript Code' then add_tr#('Code', c2_rec.att_js_code, p_codemirror => 'javascript'); when 'Execute Server-side Code' then add_tr#('Code', c2_rec.att_plsql_code, p_codemirror => 'plsql'); when 'Show' then add_tr#('Show all page items<br/>on the same line', c2_rec.att_show_on_same_line); when 'Hide' then add_tr#('Hide all page items<br/>on the same line', c2_rec.att_hide_page_items_on_same_line); when 'Highlight' then add_tr#('Color', c2_rec.att_attribute_01); add_tr#('Speed', c2_rec.att_attribute_02); when 'Set Value' then add_tr#('Set Type', c2_rec.att_type); case c2_rec.att_type when 'JAVASCRIPT_EXPRESSION' then add_tr#('JavaScript Expression', c2_rec.att_js_expression, p_codemirror => 'javascript'); add_tr#('Suppress Change Event', c2_rec.att_suppress_change_event); when 'SQL_STATEMENT' then add_tr#('SQL Statement', c2_rec.attribute_03, p_codemirror => 'sql'); add_tr#('Items to Submit', c2_rec.att_items_to_submit); add_tr#('Escape Special Characters', c2_rec.att_escape_special_characters); add_tr#('Suppress Change Event', c2_rec.att_suppress_change_event); when 'PLSQL_EXPRESSION' then add_tr#('PL/SQL Expression', c2_rec.att_plsql_expression, p_codemirror => 'plsql'); add_tr#('Items to Submit', c2_rec.att_items_to_submit); add_tr#('Escape Special Characters', c2_rec.att_escape_special_characters); add_tr#('Suppress Change Event', c2_rec.att_suppress_change_event); when 'DIALOG_RETURN_ITEM' then add_tr#('Return Item', c2_rec.att_return_item); add_tr#('Suppress Change Event', c2_rec.att_suppress_change_event); else v_warnings := v_warnings || 'unknown Set Type att_type: ' || c2_rec.att_type || c_lf; add_tr#(c2_rec.att_type, 'UNKNOWN'); end case; when 'Disable' then add_tr#('Disable', null); when 'Enable' then add_tr#('Enable', null); when 'Alert' then add_tr#('Text', c2_rec.att_message); when 'Close Dialog' then add_tr#('Items to Return', c2_rec.att_items_to_return); when 'Refresh' then null; else v_warnings := v_warnings || 'unknown action_name: ' || c2_rec.action_name || c_lf; add_tr#(c2_rec.action_name, 'UNKNOWN'); end case; /* -- Settings - OLD VERSION for Apex < 26.1 add_th#('Settings', 2); case c2_rec.action_name when 'Execute JavaScript Code' then add_tr#('Code', trim(c2_rec.attribute_01), p_codemirror => 'javascript'); when 'Execute PL/SQL Code' then add_tr#('Code', trim(c2_rec.attribute_01), p_codemirror => 'plsql'); -- old (< apex 20.2) when 'Execute Server-side Code' then add_tr#('Code', trim(c2_rec.attribute_01), p_codemirror => 'plsql'); when 'Show' then add_tr#('Show all page items<br/>on the same line', c2_rec.attribute_01); when 'Hide' then add_tr#('Hide all page items<br/>on the same line', c2_rec.attribute_01); when 'Highlight' then add_tr#('Color', c2_rec.attribute_01); add_tr#('Speed', c2_rec.attribute_02); when 'Set Value' then add_tr#('Set Type', c2_rec.attribute_01); case c2_rec.attribute_01 when 'JAVASCRIPT_EXPRESSION' then add_tr#('JavaScript Expression', c2_rec.attribute_05, p_codemirror => 'javascript'); add_tr#('Suppress Change Event', c2_rec.attribute_09); when 'SQL_STATEMENT' then add_tr#('SQL Statement', c2_rec.attribute_03, p_codemirror => 'sql'); add_tr#('Items to Submit', c2_rec.attribute_07); add_tr#('Escape Special Characters', c2_rec.attribute_08); add_tr#('Suppress Change Event', c2_rec.attribute_09); when 'PLSQL_EXPRESSION' then add_tr#('PL/SQL Expression', c2_rec.attribute_04, p_codemirror => 'sql'); add_tr#('Items to Submit', c2_rec.attribute_07); add_tr#('Escape Special Characters', c2_rec.attribute_08); add_tr#('Suppress Change Event', c2_rec.attribute_09); when 'DIALOG_RETURN_ITEM' then add_tr#('Return Item', c2_rec.attribute_10); add_tr#('Suppress Change Event', c2_rec.attribute_09); else v_warnings := v_warnings || 'unknown Set Type attribute_01: ' || c2_rec.attribute_01 || c_lf; add_tr#(c2_rec.attribute_01, 'UNKNOWN'); end case; when 'Disable' then add_tr#('Disable', null); when 'Enable' then add_tr#('Enable', null); when 'Alert' then add_tr#('Text', c2_rec.attribute_01); when 'Close Dialog' then add_tr#('Items to Return', c2_rec.attribute_01); when 'Refresh' then null; else v_warnings := v_warnings || 'unknown action_name: ' || c2_rec.action_name || c_lf; add_tr#(c2_rec.action_name, 'UNKNOWN'); end case; */ -- Execution Options add_th#('Execution Options', 2); add_tr#('Sequence', trim(c2_rec.action_sequence)); add_tr#('Fire On Initialization', trim(c2_rec.execute_on_page_init)); -- Affected Elements if c2_rec.affected_elements_type is not null then add_th#('Affected Elements', 2); add_tr#('Selection Type', c2_rec.affected_elements_type); case c2_rec.affected_elements_type when 'Item' then add_tr#('Item(s)', c2_rec.affected_elements); when 'Region' then add_tr#('Region', c2_rec.affected_region); when 'jQuery Selector' then add_tr#('jQuery Selector', c2_rec.affected_elements); when 'BUTTON' then add_tr#('Button', c2_rec.affected_button); when 'Column' then add_tr#('Column(s)', c2_rec.affected_elements); else v_warnings := v_warnings || 'unknown affected_elements_type: ' || c2_rec.affected_elements_type || c_lf; add_tr#(c2_rec.affected_elements_type, 'UNKNOWN'); end case; end if; -- Comments end loop; -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'dynamic_action' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end dynamic_action; function computation ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ) return clob is cursor c1(p_item in varchar2) is -- pages select c.page_id ,c.page_name ,c.item_name ,c.execution_sequence ,c.computation_point ,c.computation_type ,c.computation from apex_application_page_comp c where c.application_id = p_application_id and c.page_id = p_page_id and lower(c.item_name) = lower(p_item) -- union all -- application -- # maybe later ; v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'computation' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Computation', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Item Name', c1_rec.item_name); -- Execution add_th#('Execution'); add_tr#('Sequence', trim(c1_rec.execution_sequence)); add_tr#('Point', c1_rec.computation_point); -- Computation add_th#('Computation'); add_tr#('Type', trim(c1_rec.computation_type)); add_tr#('Value', c1_rec.computation); -- multiple labels are possible here -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'computation' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end computation; function process ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_source in boolean := false ,p_show_messages in boolean := false ,p_show_conditions in boolean := false ) return clob is cursor c1(p_item in varchar2) is -- pages select a.process_name ,a.process_type ,a.execution_sequence ,a.process_point ,a.region_name ,a.run_process ,a.process_source ,a.success_message ,a.process_error_message ,a.when_button_pressed ,a.condition_type ,a.condition_expression1 ,a.condition_expression2 from apex_application_page_proc a where a.application_id = p_application_id and a.page_id = p_page_id and lower(a.process_name) = lower(p_item) union all -- application select p.process_name ,p.process_type ,p.process_sequence as execution_sequence ,p.process_point ,null as region_name ,null as run_process ,p.process as process_source ,null as success_message ,p.error_message as process_error_message ,null as when_button_pressed ,p.condition_type ,p.condition_expression1 ,p.condition_expression2 from apex_application_processes p where p.application_id = p_application_id and nvl(p_page_id, 0) = 0 and lower(p.process_name) = lower(p_item); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'process' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Process', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Name', c1_rec.process_name); add_tr#('Type', c1_rec.process_type); add_tr#('Form Region', c1_rec.region_name); -- Process Point add_th#('Process Point'); add_tr#('Sequence', trim(c1_rec.execution_sequence)); add_tr#('Process Point', c1_rec.process_point); add_tr#('Run Process', c1_rec.run_process); -- Source if p_show_source then add_th#('Source'); add_tr#('Process' ,trim(c1_rec.process_source) ,(case when c1_rec.process_type in ('PL/SQL Anonymous Block', 'PL/SQL anonymous block') then 'plsql' else null end)); end if; -- Messages if p_show_messages then add_th#('Messages'); add_tr#('Process Success Message', c1_rec.success_message); add_tr#('Process Error Message', c1_rec.process_error_message); end if; -- Conditions if p_show_conditions then add_th#('Conditions'); add_tr#('When Button Pressed', trim(c1_rec.when_button_pressed)); add_tr#('Condition Type', trim(c1_rec.condition_type)); add_tr#('Expression 1', trim(c1_rec.condition_expression1)); add_tr#('Expression 2', trim(c1_rec.condition_expression2)); end if; -- Security -- Configuration -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'process' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end process; function branch ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_page_id in apex_application_pages.page_id%type := c_page_id ,p_show_conditions in boolean := false ) return clob is cursor c1(p_item in varchar2) is select b.* from apex_application_page_branches b where b.application_id = p_application_id and b.page_id = p_page_id and lower(b.branch_name) = lower(p_item); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'branch' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#(p_title => 'Branch', p_page_id => p_page_id); -- Identification add_th#('Identification'); add_tr#('Name', c1_rec.branch_name); -- Execution add_th#('Execution'); add_tr#('Sequence', trim(c1_rec.process_sequence)); add_tr#('Point', c1_rec.branch_point); -- Behavior add_th#('Behavior'); add_tr#('Type', trim(c1_rec.branch_type)); add_tr#('Target', c1_rec.branch_action); -- Server Side Condition if p_show_conditions then add_th#('Server Side Condition'); add_tr#('When Button Pressed', trim(c1_rec.when_button_pressed)); add_tr#('Condition Type', trim(c1_rec.condition_type)); add_tr#('Expression 1', trim(c1_rec.condition_expression1)); add_tr#('Expression 2', trim(c1_rec.condition_expression2)); end if; /* -- Source if p_show_source then add_th#('Source'); add_tr# ( 'Process' , trim(c1_rec.process_source) , ( case when c1_rec.process_type in ('PL/SQL Anonymous Block','PL/SQL anonymous block') then 'plsql' else null end ) ) ; end if; -- Messages if p_show_messages then add_th#('Messages'); add_tr#('Process Success Message', c1_rec.success_message); add_tr#('Process Error Message' , c1_rec.process_error_message); end if; -- Security -- Configuration */ -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'process' ,p_application_id => p_application_id ,p_page_id => p_page_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end branch; function column_group ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ) return clob is cursor c1(p_item in varchar2) is select a.* ,a.columns as assigned_columns -- columns is a reserved name from apex_application_page_ir_cgrps a where a.application_id = p_application_id and lower(a.column_group_name) = lower(p_item); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin -- try the cache first cache_select#(p_function_name => 'column_group', p_application_id => p_application_id, p_item_string => p_item_string, po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; dbms_lob.createtemporary(v_result, false, dbms_lob.call); for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#('Column Group'); add_th#('Column Group'); add_tr#('Group Name ', c1_rec.column_group_name); add_tr#('Description', c1_rec.column_group_description); add_tr#('Assigned Columns', to_clob(c1_rec.assigned_columns)); -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'column_group' ,p_application_id => p_application_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end column_group; function object_source ( p_name in user_source.name%type ,p_type in user_source.type%type ) return clob is cursor c1 is select text from user_source where name = upper(p_name) and type = upper(p_type) order by line; v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_source clob; begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); dbms_lob.createtemporary(v_source, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'object_source', p_object_name => p_name, p_object_type => p_type, po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; if p_type in ('PACKAGE', 'PACKAGE BODY', 'TYPE BODY', 'FUNCTION', 'JAVA SOURCE', 'PROCEDURE', 'LIBRARY', 'TRIGGER', 'TYPE') then add_table#(p_type); -- Source add_th#(p_type); add_tr#('Name', p_name); for c1_rec in c1 loop dbms_lob.append(v_source, c1_rec.text); end loop; add_tr#('Source' ,v_source ,(case when p_type in ('PACKAGE', 'PACKAGE BODY', 'TYPE BODY', 'FUNCTION', 'JAVA SOURCE', 'PROCEDURE', 'LIBRARY', 'TRIGGER', 'TYPE') then 'plsql' else null end)); else raise_application_error(-20000, 'object type ' || p_type || ' not known'); end if; -- render_table render_table#(v_result); -- insert/update into the cache cache_upsert#(p_function_name => 'object_source' ,p_object_name => p_name ,p_object_type => p_type ,p_result => v_result ,p_warnings => v_warnings); return(v_result); dbms_lob.freetemporary(v_source); dbms_lob.freetemporary(v_result); end object_source; procedure object_source_p ( p_name in user_source.name%type ,p_type in user_source.type%type ) is v_result clob := object_source(p_name => p_name, p_type => p_type); begin htp_prn(p_clob => v_result); end object_source_p; function object_ddl ( p_name in varchar2 ,p_type in varchar2 ) return clob is v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_source clob; v_text_vc user_views.text_vc%type; begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); dbms_lob.createtemporary(v_source, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'object_ddl', p_object_name => p_name, p_object_type => p_type, po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; if p_type not in ('TABLE', 'VIEW', 'MATERIALIZED_VIEW', 'INDEX', 'SEQUENCE') then raise_application_error(-20000, 'object type ' || p_type || ' not known'); end if; add_table#(p_type); -- Source add_th#(p_type); add_tr#('Name', p_name); -- Use user_views.text_vc for views of 4000 chars or less; -- otherwise, dbms_metadata will truncate those views after "... AS ", when called from Apex. -- This is a weird bug, because dbms_metadata works fine if called from schema tedstruik - and also when the view is bigger than 4000 chars. if upper(p_type) = 'VIEW' then begin select v.text_vc into v_text_vc from user_views v where v.view_name = upper(p_name) and v.text_length <= 4000; exception when no_data_found then null; end; if v_text_vc is not null then v_source := v_text_vc; else v_source := dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml('VIEW', upper(p_name))).extract('//ROWSET/ROW/VIEW_T/TEXT/text()').getclobval() ,1); end if; else v_source := dbms_metadata.get_ddl(object_type => upper(p_type), name => upper(p_name)); end if; add_tr#('DDL', v_source, 'sql'); -- render_table render_table#(v_result); -- insert/update into the cache cache_upsert#(p_function_name => 'object_ddl', p_object_name => p_name, p_object_type => p_type, p_result => v_result, p_warnings => v_warnings); return(v_result); dbms_lob.freetemporary(v_source); dbms_lob.freetemporary(v_result); exception when others then v_result := 'Error during rendering of ' || p_name || '<br/><br/>'; return(v_result); end object_ddl; function scheduled_job(p_name in varchar2) return clob is cursor c1 is select * from user_scheduler_jobs t where t.job_name = upper(p_name); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_source clob; begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); dbms_lob.createtemporary(v_source, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'scheduled_job', p_object_name => p_name, po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; add_table#('Scheduled Job'); -- Source add_th#('Job'); for c1_rec in c1 loop add_tr#('Job Name', trim(c1_rec.job_name)); add_tr#('Job Type', trim(c1_rec.job_type)); add_tr#('Job Action', trim(c1_rec.job_action)); add_tr#('Schedule Type', trim(c1_rec.schedule_type)); add_tr#('Start Date', trim(c1_rec.start_date)); add_tr#('Repeat Interval', trim(c1_rec.repeat_interval)); end loop; -- render_table render_table#(v_result); -- insert/update into the cache cache_upsert#(p_function_name => 'scheduled_job', p_object_name => p_name, p_result => v_result, p_warnings => v_warnings); return(v_result); dbms_lob.freetemporary(v_source); dbms_lob.freetemporary(v_result); exception when others then v_result := 'Error during rendering of ' || p_name || '<br/><br/>'; return(v_result); end scheduled_job; function app_lov ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ,p_show_source in boolean := true ) return clob is cursor c1(p_item in varchar2) is select a.* ,(select listagg(lvc.query_column_name, ', ') within group(order by lvc.query_column_name) from apex_application_lov_cols lvc where lvc.lov_id = a.lov_id) as additional_display_columns from apex_application_lovs a where a.application_id = p_application_id and lower(a.list_of_values_name) = lower(p_item); v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'app_lov', p_application_id => p_application_id, p_item_string => p_item_string, po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#('List of Values'); -- Name add_th#('Name'); add_tr#('', c1_rec.list_of_values_name); -- Type add_th#('Type'); add_tr#('', c1_rec.lov_type); -- Source if p_show_source then add_th#('Source'); add_tr#('Query', trim(c1_rec.list_of_values_query), (case when trim(c1_rec.lov_type) in ('Dynamic') then 'sql' else null end)); end if; -- Column Mapping add_th#('Column Mapping'); add_tr#('Return', c1_rec.return_column_name); add_tr#('Display', c1_rec.display_column_name); add_tr#('Default Sort', c1_rec.default_sort_column_name); add_tr#('Sort Direction', c1_rec.default_sort_direction); -- Additional Display Columns add_th#('Additional Display Columns'); add_tr#('', c1_rec.additional_display_columns); -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'app_lov' ,p_application_id => p_application_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end app_lov; function breadcrumb ( p_item_string in varchar2 ,p_application_id in apex_application_pages.application_id%type := c_app_id ) return clob is cursor c1(p_item in varchar2) is select bcb.breadcrumb_name ,bce.* ,(select bce_p.entry_label from apex_application_bc_entries bce_p where bce_p.breadcrumb_entry_id = bce.parent_breadcrumb_id) as bce_p_entry_label ,ltrim(sys_connect_by_path(bce.entry_label, ' / '), ' / ') as breadcrumb_path ,level as lvl from apex_application_bc_entries bce join apex_application_breadcrumbs bcb on bcb.breadcrumb_id = bce.breadcrumb_id where bce.application_id = p_application_id and lower(bcb.breadcrumb_name) = lower(p_item) start with bce.parent_breadcrumb_id is null connect by prior bce.breadcrumb_entry_id = bce.parent_breadcrumb_id order by level ,bce.entry_label; v_result ted_page_summary_caches.result%type; v_warnings ted_page_summary_caches.warnings%type; v_vc_arr2 apex_application_global.vc_arr2 := apex_string.string_to_table(p_item_string); begin dbms_lob.createtemporary(v_result, false, dbms_lob.call); -- try the cache first cache_select#(p_function_name => 'breadcrumb', p_application_id => p_application_id, p_item_string => p_item_string, po_result => v_result); if dbms_lob.getlength(v_result) <> 0 then return v_result; end if; for i in 1 .. v_vc_arr2.count loop for c1_rec in c1(p_item => v_vc_arr2(i)) loop add_table#('Breadcrumb'); -- Breadcrumb add_th#('Breadcrumb'); add_tr#('Name', c1_rec.breadcrumb_name); -- Breadcrumb Entry add_th#('Breadcrumb Entry'); add_tr#('Page', trim(c1_rec.defined_for_page)); add_tr#('Parent Entry', trim(c1_rec.bce_p_entry_label)); add_tr#('Short Name', trim(c1_rec.entry_label)); add_tr#('Long Name', trim(c1_rec.entry_long_label)); -- Target add_th#('Target'); add_tr#('URL', trim(c1_rec.url)); -- render_table render_table#(v_result); end loop; end loop; -- insert/update into the cache cache_upsert#(p_function_name => 'breadcrumb' ,p_application_id => p_application_id ,p_item_string => p_item_string ,p_result => v_result ,p_warnings => v_warnings); return(v_result); end breadcrumb; begin g_th_colors(1) := 'lightskyblue'; g_th_colors(2) := '#B6E1FC'; end ted_page_summary;

Table

Table
	Name	TED_PAGE_SUMMARY_CACHES
	DDL	CREATE TABLE "TEDSTRUIK"."TED_PAGE_SUMMARY_CACHES" ( "KEY" VARCHAR2(4000) NOT NULL ENABLE, "RESULT" CLOB NOT NULL ENABLE, "DATE_UPDATED" DATE NOT NULL ENABLE, "HITS" NUMBER NOT NULL ENABLE, "MISSES" NUMBER NOT NULL ENABLE, "EXPIRED_YN" VARCHAR2(1) NOT NULL ENABLE, "WARNINGS" VARCHAR2(4000), "DATE_USED" DATE, "USED_BY_25384_PAGE_IDS" VARCHAR2(4000), CONSTRAINT "TED_PAGE_SUMMARY_CACHES_PK" PRIMARY KEY ("KEY") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 167 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" LOB ("RESULT") STORE AS SECUREFILE ( TABLESPACE "USERS" ENABLE STORAGE IN ROW 4000 CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

Scheduled Job

Job
	Job Name	TED_PAGE_SUMMARY_CACHE_REFRESH
	Job Type	PLSQL_BLOCK
	Job Action	ted_page_summary.cache_refresh;
	Schedule Type	CALENDAR
	Start Date	12-FEB-19 12.00.00.000000 AM +01:00
	Repeat Interval	Freq=Hourly