Skip to Main Content
Ted Struik - Oracle
Search For
Home
Home
Ted Struik - Oracle
Search
Site Map
Statistics
Page Errors
Apex
Apex
General
Autocomplete
Conditions
Errors
Items used in PL/SQL
Validations
Dynamic Actions
Logs
Version
App-wide Items
Error Handling
Apex Views
Apex Upgrade 4.2 to 5.1
Apex Upgrade 20.2 to 23.1
Database Dependencies
Show Source Code
URLs
Authorization
Modal Dialogs
Apex Office Print (AOP)
Apex-Sert
Warn on Unsaved Changes
Form Regions
Reports
Checkboxes
Download
Errors
Nowrap Columns
Substitution Strings
Use IR results in pl/sql
Clickable Row
Row Highlight
Translate Filters etc.
Bar Charts
Column Usage
Reset Pagination
Hide IR Actions menu items
Get Classic Report query
Interactive Grid
ORA-20987
ERR-1002
Copying Text
IG Info
Multiple Row Select
Read Only Options
Search
Sort and Search
Images & Fonts
Standard Icons & Fonts
WebDav
Font Awesome
Icons/image popup LOV
Lists
Hierarchy
LOVs
Definition Bug
Set 1-item LOV to Disabled
With Clause Errors
Modal LOV Plugin
HTML / CSS / JavaScript
Vertical Align
Legend CSS
Geolocation
Apex.confirm
Pie Charts
Codemirror
Theme Roller
Catching Key Strokes
Browser Cache
Locale
RequireJS
Collapsible Regions
Find JavaScript
Import / Export
Dynamic Translations
Translated Pages
APEXExport (java)
apex_export (pl/sql)
Component Settings
Charts
Translate Charts
[Reports] Bar Charts
Shuttles
Shuttle Sorting
Disable Shuttle Items
Menus
Accordion-Like
Scroll bug
Authorization
Session State
About Session State
Rollback & Commit
Performance of v()
Apex Clone Session
Request
Session State in Views
Collections
ORA-20101 & ORA-20104
ORA-00001
Max Row Count
(PL)SQL
(PL)SQL
External Tables
SQL Plus
Conditions
Long
ORDImage
Date & Timestamp
CGI environment
Sys_context
Rounding percentages
Apex_string.split in SQL
Dbms_metadata
Instr
Regular Expressions
Rollback to Savepoint
Use table aliases
(Pipelined) Bulk Collect
JSON
Source Code Search (PL/SQL)
Source Code Search (SQL)
Format Models
Pipelined Table Functions
Hint no_unnest
Listagg
DBA
DBA
Backup & Restore
Users
Export & Import
DBMS Jobs
Tablespaces
Table Size
Flush cache
Access Control List (ACL)
Locks
Java
Constraints
Object Dependencies
Package Dependencies 1
Package Dependencies 2
Grants
SQL History
Startup Errors
ORDS
Miscellaneous
Miscellaneous
Holidays
E-mail
NLExtract
Windows
VPN
VMware
Links
Google
Visualization Orgchart
Hide Search Results
Reverse Proxy
To-do List
Cytoscape
Graphics
Upscaling
Amazon Prices
Domoticz
DDoS
Hobbies
Hobbies
Motorcycles
'95 Kawasaki ZZR 600
'94 Kawasaki ZZR 1100
'91 Honda CBR 600 F2
'95 Kawasaki ZZR 1100
'99 Honda CBR 1100 XX
'00 Honda CBR 1100 XX
'02 Kawasaki ZX-12R (NL)
'05 Ducati 999 (NL)
'08 Honda Fireblade
'07 Suzuki Bandit 1250A
'18 Suzuki GSX-S 1000 F
MotoGP
Garfield
Lyrics
Skydive
Woodworking
Pantorouter
16 inch Bandsaw
Introduction
Frame
Wheels
Wheel mounts
Blade guides
Trunions and table
Enclosure
Alignment and more
Box Joint Jig
Foldable Sawhorses
Strip Sander XL
Photography
Sony RX10 IV
Yes Minister
WinAmp
SQL History
Rows
1
5
10
15
20
25
50
100
1000
All
Last Active Time
Parsing Schema Name
Sql Text
Module
Service
Elapsed Time
Rows Processed
Disk Reads
First Load Time
08-12-2024 13:17:48
APEX_240100
SELECT /*+ result_cache */ FILE_NAME FROM WWV_FLOW_STATIC_FILES WHERE SECURITY_GROUP_ID = :B2 AND FLOW_ID = :B1 AND FILE_NAME LIKE 'icons/app-icon-%'
TEDSTRUIK/APEX:APP 25384:1149
freepdb1
26776
0
2
08-12-2024 08:22:05
08-12-2024 13:17:48
APEX_240100
SELECT /*+ result_cache */ NAME, CSS_CLASSES FROM WWV_FLOW_THEME_STYLES WHERE FLOW_ID = :B3 AND THEME_ID = :B2 AND IS_CURRENT = 'Y' AND SECURITY_GROUP_ID = :B1
TEDSTRUIK/APEX:APP 25384:1149
freepdb1
35585
272
1
08-12-2024 08:22:05
08-12-2024 13:17:48
APEX_240100
SELECT /*+ result_cache */ S.NAME, S.TAB_SET, S.STEP_TITLE, S.ALIAS, S.MEDIA_TYPE, S.FIRST_ITEM, S.WELCOME_TEXT, S.BOX_WELCOME_TEXT, S.FOOTER_TEXT, TRUNC(S.STEP_TEMPLATE) AS STEP_TEMPLATE, S.PAGE_CSS_CLASSES, S.PAGE_TEMPLATE_OPTIONS, S.REQUIRED_ROLE, NVL(S.ALLOW_DUPLICATE_SUBMISSIONS,'Y') ALLOW_DUPLICATE_SUBMISSIONS, S.RELOAD_ON_SUBMIT, S.WARN_ON_UNSAVED_CHANGES, S.HTML_PAGE_HEADER, S.HTML_PAGE_ONLOAD, S.JAVASCRIPT_FILE_URLS AS PAGE_JAVASCRIPT_FILE_URLS, S.JAVASCRIPT_CODE, S.JAVASCRIPT_CODE_ONLOAD, S.CSS_FILE_URLS AS PAGE_CSS_FILE_URLS, S.INLINE_CSS, NVL(S.PROTECTION_LEVEL,'N') PROTECTION_LEVEL, NVL(S.AUTOCOMPLETE_ON_OFF,'ON') AUTOCOMPLETE_ON_OFF, S.CACHE_MODE, S.CACHE_TIMEOUT_SECONDS, S.CACHE_WHEN_CONDITION_TYPE, S.CACHE_WHEN_CONDITION_E1, S.CACHE_WHEN_CONDITION_E2, NVL(S.INCLUDE_APEX_CSS_JS_YN,'Y') INCLUDE_APEX_CSS_JS_YN, S.BROWSER_CACHE, S.READ_ONLY_WHEN_TYPE, S.READ_ONLY_WHEN, S.READ_ONLY_WHEN2, S.PAGE_MODE, S.DIALOG_ATTRIBUTES, S.DIALOG_HEIGHT, S.DIALOG_WIDTH, S.DIALOG_MAX_WIDTH,
TEDSTRUIK/APEX:APP 25384:1149
freepdb1
175746
297
14
08-12-2024 08:22:04
08-12-2024 13:17:48
APEX_240100
SELECT * FROM WWV_FLOW_WORKSHEET_COLUMNS WHERE WORKSHEET_ID = :B5 AND FLOW_ID = :B4 AND SECURITY_GROUP_ID = :B3 AND ((:B1 IS NULL AND (:B2 = 'Y' OR DISPLAY_TEXT_AS NOT IN ( 'HIDDEN', 'HIDDEN_ESCAPE_SC' ) )) OR (DB_COLUMN_NAME = :B1 )) ORDER BY DISPLAY_ORDER, ID
TEDSTRUIK/APEX:APP 25384:1
freepdb1
56014
422
9
08-12-2024 08:23:50
08-12-2024 13:17:48
APEX_240100
SELECT * FROM WWV_FLOW_DATA WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1 AND ROWNUM = 1
TEDSTRUIK/APEX:APP 25384:1149
freepdb1
177505
14
6
08-12-2024 08:22:04
1 - 5 of 357
Column Actions
Search
Info
My take on a query to list historical SQL statements. Play around with the where clause to change this to your needs.
Either run this query as sys/system, or give the user this privilege:
grant select on v_$sql to tedstruik;
Note that you can only select from v$sql; you cannot grant to v$sql or any other fixed views. So here we need to give the grant directly to the base table v_$sql instead.
The Dynamic Action in the Code section below is included as an example of how to get Codemirror to work in reports - more info on my
Codemirror
page. It is not needed for the rest of this example, but it does make it look nice.
Code
Region
Identification
Sequence
20
Title
SQL History
Type
Interactive Report
Source
Source Type
DYNAMIC_QUERY
Region Source
with s as ( select 'SelecT' as search_text from dual ) select v.last_active_time , v.parsing_schema_name , v.sql_text --, v.sql_fulltext -- clob containing full SQL , v.module , v.service , v.elapsed_time , v.rows_processed , v.disk_reads , to_date(v.first_load_time, 'YYYY-MM-DD/HH24:MI:SS') as first_load_time from s , v$sql v where ( dbms_lob.instr (v.sql_fulltext , lower(s.search_text) ) <> 0 or dbms_lob.instr (v.sql_fulltext , upper(s.search_text) ) <> 0 or instr (lower(v.sql_text), lower(s.search_text) ) <> 0 ) -- not elegant, and won't find 'SelecT' if the query > 1000 characters, but the best we can do here (and still get some performance) and v.last_active_time >= trunc(sysdate - 1) -- limit the date range and v.parsing_schema_name not in ('SYSTEM', 'SYS', 'SYSMAN', 'DBSNMP', 'CTXSYS') -- exclude some schemas and instr(v.module, 'APEX:APP 25384') <> 0 -- find activity for this Apex app
Dynamic Action
Identification
Name
codemirror
Execution Options
Sequence
10
When
Event
After Refresh
Selection Type
Region
Region
SQL History
Advanced
Event Scope
Static
True Action
Identification
Action
Execute JavaScript Code
Settings
Code
var arrayLength = this.affectedElements.length; var p_mode = ""; var p_readOnly = true; for (var i = 0; i < arrayLength; i++) { if (this.affectedElements[i].classList.contains("codemirror-plsql")) {p_mode = "text/x-plsql"} else if (this.affectedElements[i].classList.contains("codemirror-sql")) {p_mode = "text/x-sql"} else if (this.affectedElements[i].classList.contains("codemirror-javascript")) {p_mode = "text/javascript"} else if (this.affectedElements[i].classList.contains("codemirror-html")) {p_mode = "text/html"} else if (this.affectedElements[i].classList.contains("codemirror-css")) {p_mode = "text/css"} else if (this.affectedElements[i].classList.contains("codemirror-sh")) {p_mode = "text/x-sh"} else if (this.affectedElements[i].classList.contains("codemirror-shell")) {p_mode = "text/x-sh"} else if (this.affectedElements[i].classList.contains("codemirror-xml")) {p_mode = "application/xml"} else if (this.affectedElements[i].classList.contains("codemirror-java")) {p_mode = "text/x-java"} else {p_mode = ""} ; if (this.affectedElements[i].classList.contains("codemirror-edit")) {p_readOnly = false} else {p_readOnly = true} ; if (this.affectedElements[i].classList.contains("codemirror-nowrap")) {p_lineWrapping = false} else {p_lineWrapping = true} ; codemirrorFromTextArea ( this.affectedElements[i] , p_mode , p_readOnly , p_lineWrapping ) ; }
Execution Options
Sequence
10
Fire On Initialization
No
Affected Elements
Selection Type
jQuery Selector
jQuery Selector
.a-IRR-table [class*="codemirror-"]