Skip to Main Content

Search

Report Settings

Page IdAscendingPage NameItem Data TypeRegion NameItem SequenceItem NameItem Source
1021Apex - Lists - HierarchyPage Region - Interactive ReportHierarchy10-### REGION_SOURCE=<br>select sys_connect_by_path(to_char(a.display_sequence,'fm00000'), '->') as path_sort , sys_connect_by_path(a.entry_text, '->') as path_text , a.entry_text , level , ( case when substr(a.entry_target, 14, instr(a.entry_target, 'SESSION')-16) between '0' and '999999' then to_number(substr(a.entry_target, 14, instr(a.entry_target, 'SESSION')-16)) else to_number(null) end ) as page_id , a.entry_target , a.display_sequence from apex_application_list_entries a where a.application_id = :APP_ID and a.list_name = 'Navigation Menu' start with a.list_entry_parent_id is null connect by prior a.list_entry_id = a.list_entry_parent_id order by path_sort
1021Apex - Lists - HierarchyPage Interactive Report---### SQL_QUERY=<br>select sys_connect_by_path(to_char(a.display_sequence,'fm00000'), '->') as path_sort , sys_connect_by_path(a.entry_text, '->') as path_text , a.entry_text , level , ( case when substr(a.entry_target, 14, instr(a.entry_target, 'SESSION')-16) between '0' and '999999' then to_number(substr(a.entry_target, 14, instr(a.entry_target, 'SESSION')-16)) else to_number(null) end ) as page_id , a.entry_target , a.display_sequence from apex_application_list_entries a where a.application_id = :APP_ID and a.list_name = 'Navigation Menu' start with a.list_entry_parent_id is null connect by prior a.list_entry_id = a.list_entry_parent_id order by path_sort <br>### DETAIL_LINK_TYPE=<br>No Link Column
1044Hobbies - GarfieldPage Region - Interactive ReportGarfield10-### REGION_SOURCE=<br>with g2 as ( select g.category , g.filename , to_date(substr(g.filename,3,6), 'RRMMDD') as d , to_char( to_date(substr(g.filename,3,6), 'RRMMDD'), 'YYYYMMDD') as sort_order from ted_garfields g union all select 'Random' as category , 'ga' || to_char(a.d, 'RRMMDD') || '.gif' as filename , a.d , to_char(a.r, 'fm00000000') as sort_order from ( select round(dbms_random.value*1000000) as r , d from ( select distinct trunc( to_date('01-01-1978','DD-MM-YYYY') + dbms_random.value * (trunc(sysdate) - to_date('01-01-1978','DD-MM-YYYY')) ) as d from ( select rownum n from dual connect by level <= 1500) ) order by r ) a where rownum <= 1000 ) select g2.* , to_char(g2.d, 'fmDay') as day , '<img style="min-width: 600px;" src="http://images.ucomics.com/comics/ga/' || to_char(g2.d, 'YYYY') || '/' || g2.filename || '"></img>' as image from g2 where ( (:P1044_CATEGORY is null and g2.category <> 'Random') or :P1044_CATEGORY = g2.category )
1044Hobbies - GarfieldPage Interactive Report---### SQL_QUERY=<br>with g2 as ( select g.category , g.filename , to_date(substr(g.filename,3,6), 'RRMMDD') as d , to_char( to_date(substr(g.filename,3,6), 'RRMMDD'), 'YYYYMMDD') as sort_order from ted_garfields g union all select 'Random' as category , 'ga' || to_char(a.d, 'RRMMDD') || '.gif' as filename , a.d , to_char(a.r, 'fm00000000') as sort_order from ( select round(dbms_random.value*1000000) as r , d from ( select distinct trunc( to_date('01-01-1978','DD-MM-YYYY') + dbms_random.value * (trunc(sysdate) - to_date('01-01-1978','DD-MM-YYYY')) ) as d from ( select rownum n from dual connect by level <= 1500) ) order by r ) a where rownum <= 1000 ) select g2.* , to_char(g2.d, 'fmDay') as day , '<img style="min-width: 600px;" src="http://images.ucomics.com/comics/ga/' || to_char(g2.d, 'YYYY') || '/' || g2.filename || '"></img>' as image from g2 where ( (:P1044_CATEGORY is null and g2.category <> 'Random') or :P1044_CATEGORY = g2.category ) <br>### DETAIL_LINK_TYPE=<br>No Link Column
1077DBA - ConstraintsPage Region - Interactive ReportHierarchy of primary & foreign keys20-### REGION_SOURCE=<br>select distinct owner , table_name , constraint_type , constraint_name , position , column_name , r_owner , r_table_name from ( select uc.owner , uc.table_name , uc.constraint_name , cols.column_name , uc.r_owner , ( select table_name from dba_constraints where owner = uc.r_owner and constraint_name = uc.r_constraint_name ) as r_table_name , ( select column_name from dba_cons_columns where owner = uc.r_owner and constraint_name = uc.r_constraint_name and position = cols.position ) as r_column_name , cols.position , uc.constraint_type from dba_constraints uc , dba_cons_columns cols where uc.owner = cols.owner and uc.constraint_name = cols.constraint_name and uc.constraint_type not in ( 'C', 'U' ) -- C = check constraint, P = primary key, R = Foreign key, U = unique key and uc.table_name not like 'BIN$%' -- exclude system "tables" -- # set your filters here and uc.owner = 'TEDSTRUIK' -- # ) -- # and set the rest of your filters here -- start with table_name = 'EMP' -- and column_name = 'EMPNO' -- # connect by nocycle prior owner = r_owner and prior table_name = r_table_name and prior column_name = r_column_name order by owner , table_name , constraint_type , constraint_name , position
1077DBA - ConstraintsPage Interactive Report---### SQL_QUERY=<br>select distinct owner , table_name , constraint_type , constraint_name , position , column_name , r_owner , r_table_name from ( select uc.owner , uc.table_name , uc.constraint_name , cols.column_name , uc.r_owner , ( select table_name from dba_constraints where owner = uc.r_owner and constraint_name = uc.r_constraint_name ) as r_table_name , ( select column_name from dba_cons_columns where owner = uc.r_owner and constraint_name = uc.r_constraint_name and position = cols.position ) as r_column_name , cols.position , uc.constraint_type from dba_constraints uc , dba_cons_columns cols where uc.owner = cols.owner and uc.constraint_name = cols.constraint_name and uc.constraint_type not in ( 'C', 'U' ) -- C = check constraint, P = primary key, R = Foreign key, U = unique key and uc.table_name not like 'BIN$%' -- exclude system "tables" -- # set your filters here and uc.owner = 'TEDSTRUIK' -- # ) -- # and set the rest of your filters here -- start with table_name = 'EMP' -- and column_name = 'EMPNO' -- # connect by nocycle prior owner = r_owner and prior table_name = r_table_name and prior column_name = r_column_name order by owner , table_name , constraint_type , constraint_name , position <br>### DETAIL_LINK_TYPE=<br>No Link Column
1082(PL)SQL - InstrPage Region - Interactive ReportIR20-### REGION_SOURCE=<br>with search as ( select lower( v('P1082_SEARCH_PATTERN_1') ) as pattern from dual union all select lower( v('P1082_SEARCH_PATTERN_2') ) as pattern from dual ) , lev as ( select level as lev from dual connect by level <= 100 ) , texts as ( select distinct lam.album_title , lsg.song_title , dbms_lob.substr(lsg.song_lyrics,4000,1) as text , lower(dbms_lob.substr(lsg.song_lyrics,4000,1)) as text_lower from search , ted_lyrics_albums lam , ted_lyrics_songs lsg where lam.pk_id = lsg.album_pk_id and lam.artist_code = 'MK' and dbms_lob.getlength(lsg.song_lyrics) > 0 and lower(lsg.song_lyrics) like '%' || search.pattern || '%' ) , a as ( select * from ( select search.* , texts.* , instr(texts.text_lower, search.pattern, 1, lev.lev) as i from search , texts , lev ) where i <> 0 ) select a.album_title , a.song_title , a.i , replace( '...' || substr(a.text, a.i-least(a.i-1,50), least(a.i-1,50)) || '<b>' || substr(a.text, a.i, length(a.pattern)) || '</b>' || substr(a.text, a.i+length(a.pattern), 50) || '...' , chr(13) || chr(10), '<br/>' ) as text_substr , apex_item.textarea ( p_idx => 11 , p_value => a.text , p_rows => 2 , p_cols => 60 ) as text_area from a order by a.album_title , a.song_title , a.i
1082(PL)SQL - InstrPage Region - HTML/TextInfo100-### REGION_SOURCE=<br>Method to find multiple occurrences of a one or more strings within a varchar2. Sort of an instr on steroids...<br/> <br/> In the example above, I used my Mark Knopfler lyrics table. The exact setup of that table is not really important here, all you need is a table with a varchar2 column (or a clob, as long as it's not bigger than 4000 bytes).<br/> Support for (bigger) clobs would be nice to have, but since this solution uses distinct and the &quot;lower&quot; function to make the search case-insensitive, that would be a bit of a challenge.<br/> <br/> You may want to change the &quot;connect by level &lt;= 100&quot; to some other value, if you expect to find the search pattern more than 100 times per varchar2. Note that this value does not have a big impact on performance, so I used 100 here to be on the safe side.<br/> <br/> <br/> Searching the internet for other solutions did not produce many results. And the ones I did find used regular expression functions like regexp_count, and were very slow.<br/> A (slow) example of that approach could look something like this: <textarea class="textarea codemirror-sql">with b as ( select t.* , lower(t.text) as lower_text , regexp_count(lower(t.text), 'search pattern') as c from some_table t where lower(t.text) like '%search pattern%' ) select b.* , level as lev from b connect by level <= b.c</textarea> <br/> An interesting link: <a href="http://stackoverflow.com/questions/17906105/oracle-query-to-find-all-occurences-of-a-charcter-in-a-string">http://stackoverflow.com/questions/17906105/oracle-query-to-find-all-occurences-of-a-charcter-in-a-string</a>.
1082(PL)SQL - InstrPage Interactive Report---### SQL_QUERY=<br>with search as ( select lower( v('P1082_SEARCH_PATTERN_1') ) as pattern from dual union all select lower( v('P1082_SEARCH_PATTERN_2') ) as pattern from dual ) , lev as ( select level as lev from dual connect by level <= 100 ) , texts as ( select distinct lam.album_title , lsg.song_title , dbms_lob.substr(lsg.song_lyrics,4000,1) as text , lower(dbms_lob.substr(lsg.song_lyrics,4000,1)) as text_lower from search , ted_lyrics_albums lam , ted_lyrics_songs lsg where lam.pk_id = lsg.album_pk_id and lam.artist_code = 'MK' and dbms_lob.getlength(lsg.song_lyrics) > 0 and lower(lsg.song_lyrics) like '%' || search.pattern || '%' ) , a as ( select * from ( select search.* , texts.* , instr(texts.text_lower, search.pattern, 1, lev.lev) as i from search , texts , lev ) where i <> 0 ) select a.album_title , a.song_title , a.i , replace( '...' || substr(a.text, a.i-least(a.i-1,50), least(a.i-1,50)) || '<b>' || substr(a.text, a.i, length(a.pattern)) || '</b>' || substr(a.text, a.i+length(a.pattern), 50) || '...' , chr(13) || chr(10), '<br/>' ) as text_substr , apex_item.textarea ( p_idx => 11 , p_value => a.text , p_rows => 2 , p_cols => 60 ) as text_area from a order by a.album_title , a.song_title , a.i <br>### DETAIL_LINK_TYPE=<br>No Link Column
1093Hobbies - SkydivePage Item (Varchar)Skydive20P1093_WIND_ANGLE<br>### ITEM_DEFAULT_TYPE=<br>STATIC_TEXT_WITH_SUBSTITUTIONS<br>### ITEM_DEFAULT=<br>0<br>### LOV_DEFINITION=<br>select trim(a) || ( case a when 0 then ' - North' when 90 then ' - East' when 180 then ' - South' when 270 then ' - West' else '' end ) as d , a as r from ( select (level-1)*10 as a from dual connect by level <= 36 ) order by a
1093Hobbies - SkydivePage Item (Varchar)Skydive30P1093_WIND_SPEED<br>### ITEM_DEFAULT_TYPE=<br>STATIC_TEXT_WITH_SUBSTITUTIONS<br>### ITEM_DEFAULT=<br>5.144<br>### LOV_DEFINITION=<br>select trim(a) as d , a * 0.5144 as r from ( select level-1 as a from dual connect by level <= 21 ) order by a
1104(PL)SQL - (Pipelined) Bulk CollectPage Item (Varchar)Settings10P1104_N_MIN<br>### LOV_DEFINITION=<br>select level as d , level as r from dual connect by level <= 100 order by 1
1104(PL)SQL - (Pipelined) Bulk CollectPage Item (Varchar)Settings20P1104_N_MAX<br>### LOV_DEFINITION=<br>select level as d , level as r from dual connect by level <= 100 order by 1
1104(PL)SQL - (Pipelined) Bulk CollectPage Item (Varchar)Settings50P1104_LIMIT<br>### LOV_DEFINITION=<br>select level as d , level as r from dual connect by level <= 100 order by 1
1115Hobbies - Woodworking - 16 inch Bandsaw - IntroductionPage Region - Interactive ReportInch fractions140-### REGION_SOURCE=<br>with a as ( select level as a64 from dual connect by level <= 64 ) , b as ( select a.a64 , ( case when mod(a.a64, 64) = 0 then trim(a.a64/64) || '/' || trim(64/64) else null end ) as x64 , ( case when mod(a.a64, 32) = 0 then trim(a.a64/32) || '/' || trim(64/32) else null end ) as x32 , ( case when mod(a.a64, 16) = 0 then trim(a.a64/16) || '/' || trim(64/16) else null end ) as x16 , ( case when mod(a.a64, 8) = 0 then trim(a.a64/ 8) || '/' || trim(64/ 8) else null end ) as x8 , ( case when mod(a.a64, 4) = 0 then trim(a.a64/ 4) || '/' || trim(64/ 4) else null end ) as x4 , ( case when mod(a.a64, 2) = 0 then trim(a.a64/ 2) || '/' || trim(64/ 2) else null end ) as x2 , ( case when mod(a.a64, 1) = 0 then trim(a.a64/ 1) || '/' || trim(64/ 1) else null end ) as x1 , a.a64/64 as inch_decimal , a.a64/64*25.4 as mm_decimal from a ) select nvl(x64, nvl(x32, nvl(x16, nvl(x8, nvl(x4, nvl(x2, x1) ))))) as fraction , b.* , b.mm_decimal + 25.4 * 1 as mm_decimal_1 , b.mm_decimal + 25.4 * 2 as mm_decimal_2 , b.mm_decimal + 25.4 * 3 as mm_decimal_3 , b.mm_decimal + 25.4 * 4 as mm_decimal_4 , b.mm_decimal + 25.4 * 5 as mm_decimal_5 from b order by b.a64
  • 1 - 15