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
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
Unique Visits per Month
Page Visits per Month
Info
A simple statistics view with page hits for this application.
The dynamic action on page 0 handles the page hit logging.
For the package below, you will need this: grant execute on dbms_crypto to tedstruik;
All the hashing of IP addresses is needed to comply with the AVG/GDPR laws. The MAC key (ted_secret_stuff.c_salt) is a secret random string, which I put in another package for obvious reasons.
The IPSTACK_JSON column is for future use, it might be useful to get some info on the IP (before we hash it).
Note 1: For a simpler method of logging page visits, you can also use the method described by Martin Giffy D'Souza, which is discussed on my
Apex -> General -> Logs
page. That would be a bit easier to implement than this one, but also a bit less flexible.
Note 2: I really should find a way to filter out all the search bots from this list, like Google, Bing, etc. Some (most?) of these pages seem quite popular but that's just because some search bots keep visiting them over and over...
Note 3: In March 2021 my VPS provider OVH had some downtime, and in April my Users tablespace got filled up by accident - so nothing was logged for 10 days in March and after April 9th. And since I already planned to update this package, I have stopped logging for the moment. To be continued.
Code
Region
Identification
Sequence
50
Title
Unique Visits per Month
Type
Chart
Source
Source Type
NATIVE_JET_CHART
Chart Series
Series Name
Visits
Sequence
10
Data Source Type
SQL Query
Data Source
select to_char(t.month_inserted, 'YYYY-MM') as month_inserted , t.session_n from ted_p1113_session_month_mv t where t.month_inserted < trunc(sysdate, 'MM') and t.month_inserted < to_date('01-03-2021', 'DD-MM-YYYY') -- ### Temporary Fix order by 1
Region
Identification
Sequence
60
Title
Page Visits per Month
Type
Chart
Source
Source Type
NATIVE_JET_CHART
Region Source
select to_char(t.month_inserted, 'YYYY-MM') as month_inserted , trim(t.page_id) || ' - ' || t.page_name as page_descr , t.session_n from ted_p1113_session_page_month_mv t where t.month_inserted < trunc(sysdate, 'MM') and t.month_inserted < to_date('01-03-2021', 'DD-MM-YYYY') -- ### Temporary Fix order by 1 , 2
Chart Series
Series Name
Visits
Sequence
10
Table
Table
Name
ted_access_logs
DDL
CREATE TABLE "TEDSTRUIK"."TED_ACCESS_LOGS" ( "DATE_INSERTED" DATE NOT NULL ENABLE, "APP_ID" NUMBER NOT NULL ENABLE, "PAGE_ID" NUMBER NOT NULL ENABLE, "APP_SESSION" NUMBER NOT NULL ENABLE, "N" NUMBER NOT NULL ENABLE, "IPSTACK_JSON" CLOB, "IP_ADDRESS_HASHED" RAW(256) NOT NULL ENABLE, "BOT_PK_ID" NUMBER, CONSTRAINT "TED_ACCESS_LOGS_UK" UNIQUE ("DATE_INSERTED", "APP_ID", "PAGE_ID", "APP_SESSION", "IP_ADDRESS_HASHED") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 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, CONSTRAINT "TED_ACCESS_LOGS_BOTS_FK" FOREIGN KEY ("BOT_PK_ID") REFERENCES "TEDSTRUIK"."TED_ACCESS_LOG_BOTS" ("PK_ID") 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 ("IPSTACK_JSON") 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))
View
View
Name
ted_apex_application_pages_v
DDL
with iv_list as (select distinct -- needed for page 1069, which is included twice in the menu li.application_id ,to_number(regexp_substr(lie.entry_target, '[^:]+', 1, 2)) as page_id from apex_application_lists li join apex_application_list_entries lie on lie.list_id = li.list_id where li.application_id = 25384 -- hardcoding this makes debugging in Apex Builder (which has app_id 4500) a lot easier and li.list_name = 'Navigation Menu' and nvl(lie.condition_type_code, 'x') <> 'NEVER') select p.application_id ,p.page_id ,p.page_title ,p.page_name ,nvl(pd.created_on, p.created_on) as created_on ,nvl(pd.last_updated_on, p.last_updated_on) as last_updated_on from apex_application_pages p join iv_list on (iv_list.application_id = p.application_id and iv_list.page_id = p.page_id) -- only show pages included in the Navigation Menu left join ted_page_dates pd on (pd.application_id = p.application_id and pd.page_id = p.page_id) where to_number(p.page_id) >= 1000 -- to_number needed to prevent "ORA-01722: invalid number"
Materialized View
Materialized View
Name
ted_p1113_session_month_mv
DDL
CREATE MATERIALIZED VIEW "TEDSTRUIK"."TED_P1113_SESSION_MONTH_MV" ("MONTH_INSERTED", "SESSION_N") SEGMENT CREATION IMMEDIATE ORGANIZATION HEAP 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" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT trunc(sysdate)+1 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE AS with h as ( -- my own ip addresses select ted_access_log.hash('82.197.212.38') as h from dual union all select ted_access_log.hash('51.91.96.181') as h from dual ) , p as ( select p1.application_id , p1.page_id , p1.page_name from ted_apex_application_pages_v p1 union all select p2.application_id , p2.page_id , p2.page_name from apex_application_pages p2 where p2.application_id = nvl(v('APP_ID') ,25384) and p2.page_id between 1 and 999 and p2.page_id not in (101) ) , a as ( select t.app_id , trunc(t.date_inserted, 'MM') as month_inserted , count(distinct t.app_session) as session_n from ted_access_logs t where t.ip_address_hashed not in ( select h from h ) and t.date_inserted >= to_date('2018-10-15', 'YYYY-MM-DD') -- after the day I installed the logging packages etc. group by t.app_id , trunc(t.date_inserted, 'MM') ) select a.month_inserted , a.session_n from a
Materialized View
Materialized View
Name
ted_p1113_session_page_month_mv
DDL
CREATE MATERIALIZED VIEW "TEDSTRUIK"."TED_P1113_SESSION_PAGE_MONTH_MV" ("PAGE_ID", "PAGE_NAME", "MONTH_INSERTED", "SESSION_N") SEGMENT CREATION IMMEDIATE ORGANIZATION HEAP 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" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT trunc(sysdate)+1 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE AS with h as ( -- my own ip addresses select ted_access_log.hash('82.197.212.38') as h from dual union all select ted_access_log.hash('51.91.96.181') as h from dual ) , p as ( select p1.application_id , p1.page_id , p1.page_name from ted_apex_application_pages_v p1 union all select p2.application_id , p2.page_id , p2.page_name from apex_application_pages p2 where p2.application_id = nvl(v('APP_ID') ,25384) and p2.page_id between 1 and 999 and p2.page_id not in (101) ) , a as ( select t.app_id , t.page_id , trunc(t.date_inserted, 'MM') as month_inserted , count(distinct t.app_session) as session_n from ted_access_logs t where t.ip_address_hashed not in ( select h from h ) and t.date_inserted >= to_date('2018-10-15', 'YYYY-MM-DD') -- after the day I installed the logging packages etc. group by t.app_id , t.page_id , trunc(t.date_inserted, 'MM') ) select p.page_id , p.page_name , a.month_inserted , a.session_n from p , a where p.application_id = a.app_id and p.page_id = a.page_id
Package
Package
Name
ted_access_log
Source
package ted_access_log is procedure update_bots; function ip_to_integer(p_ip_address in varchar2) return number deterministic; function bot_pk_id ( p_ip_address in varchar2 ,p_user_agent in varchar2 ) return ted_access_log_bots.pk_id%type; function hash(p_unhashed in varchar2) return raw; procedure log_insert; end ted_access_log;
Package Body
Package Body
Name
ted_access_log
Source
package body ted_access_log is procedure update_bots is cursor c_monperrus is select jt.* ,lua.created_by from ted_access_log_user_agents lua ,json_table(lua.list_clob, '$[*]' columns(pattern path '$.pattern')) jt where lua.created_by = 'monperrus' and lua.active_yn = 'Y'; begin -- monperrus -- https://github.com/monperrus/crawler-user-agents?tab=readme-ov-file for r_monperrus in c_monperrus loop begin insert into ted_access_log_bots bots (bot_type ,info_source ,ip_from ,ip_to ,user_agent_regex) values ('Web Crawler' ,r_monperrus.created_by ,null ,null ,r_monperrus.pattern); exception when dup_val_on_index then -- needed to prevent ORA-02292: integrity constraint (TEDSTRUIK.TED_ACCESS_LOGS_BOTS_FK) violated - child record found (to TED_ACCESS_LOGS) null; end; end loop; end update_bots; function ip_to_integer(p_ip_address in varchar2) return number deterministic is l_ip_address varchar2(100) := p_ip_address; l_result number; begin -- apex_workspace_activity_log sometimes shows 2 comma-separated IP address; -- in those cases the first one seems to be the one we need. if instr(l_ip_address, ',') > 0 then l_ip_address := substr(l_ip_address, 1, instr(l_ip_address, ',') - 1); end if; -- NoFormat Start l_result := 16777216 * to_number(substr(l_ip_address,1,instr(l_ip_address,'.',1,1)-1)) + 65536 * to_number(substr(l_ip_address,instr(l_ip_address,'.',1,1)+1,instr(l_ip_address,'.',1,2)-instr(l_ip_address,'.',1,1)-1)) + 256 * to_number(substr(l_ip_address,instr(l_ip_address,'.',1,2)+1,instr(l_ip_address,'.',1,3)-instr(l_ip_address,'.',1,2)-1)) + to_number(substr(l_ip_address,instr(l_ip_address,'.',1,3)+1)) ; -- NoFormat End return l_result; exception when others then -- this may happen for IP6-only entries return null; end ip_to_integer; function bot_pk_id ( p_ip_address in varchar2 ,p_user_agent in varchar2 ) return ted_access_log_bots.pk_id%type is l_result ted_access_log_bots.pk_id%type; cursor c_bots is select min(bots.pk_id) as bots_pk_id from ted_access_log_bots bots where ip_to_integer(p_ip_address) between ip_to_integer(bots.ip_from) and ip_to_integer(coalesce(bots.ip_to, bots.ip_from)) or regexp_substr(p_user_agent, bots.user_agent_regex) is not null or (p_user_agent is null and bots.user_agent_regex is null and bots.ip_from is null); begin open c_bots; fetch c_bots into l_result; close c_bots; return l_result; exception when others then raise_application_error(-20000, sqlerrm); end bot_pk_id; function hash(p_unhashed in varchar2) return raw is v_hash_raw raw(256); v_charset varchar2(100); begin if p_unhashed is null then -- Needed to prevent "ORA-29261: bad argument" in dbms_crypto.mac return null; end if; select value into v_charset from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; v_hash_raw := dbms_crypto.mac(src => utl_i18n.string_to_raw(data => p_unhashed, dst_charset => v_charset) ,typ => dbms_crypto.hash_sh256 ,key => utl_i18n.string_to_raw(ted_secret_stuff.c_salt)); -- better alternative, that needs no grants on dbms_crypto: /* select standard_hash('p_unhashed' || utl_i18n.string_to_raw(ted_secret_stuff.c_salt)) into v_hash_raw from dual ; */ return v_hash_raw; exception when others then raise_application_error(-20000, sqlerrm); end hash; function hash_check ( p_unhashed in varchar2 ,p_hashed in raw ) return boolean is begin if p_hashed = hash(p_unhashed => p_unhashed) then return true; end if; return false; exception when others then raise_application_error(-20000, sqlerrm); end hash_check; procedure log_insert is pragma autonomous_transaction; l_scope logger_logs.scope%type := $$plsql_unit || '.' || 'log_insert'; l_params logger.tab_param; l_access_logs_row ted_access_logs%rowtype; l_ip_address varchar2(100); l_ip_address_hashed raw(256); begin l_ip_address := nvl(owa_util.get_cgi_env('X-FORWARDED-FOR'), owa_util.get_cgi_env('REMOTE_ADDR')); l_ip_address_hashed := hash(p_unhashed => l_ip_address); l_access_logs_row.date_inserted := trunc(sysdate); l_access_logs_row.app_id := v('APP_ID'); l_access_logs_row.page_id := v('APP_PAGE_ID'); l_access_logs_row.ip_address_hashed := l_ip_address_hashed; l_access_logs_row.app_session := v('APP_SESSION'); l_access_logs_row.n := 1; l_access_logs_row.bot_pk_id := bot_pk_id(p_ip_address => l_ip_address, p_user_agent => owa_util.get_cgi_env('HTTP_USER_AGENT')); begin insert into ted_access_logs values l_access_logs_row; exception when dup_val_on_index then update ted_access_logs a set a.n = a.n + 1 ,a.bot_pk_id = l_access_logs_row.bot_pk_id -- bots filters may have changed, so we always update this where a.date_inserted = l_access_logs_row.date_inserted and a.app_id = l_access_logs_row.app_id and a.page_id = l_access_logs_row.page_id and a.ip_address_hashed = l_access_logs_row.ip_address_hashed and a.app_session = l_access_logs_row.app_session; end; commit; exception -- we don't want to break the entire website if this fails when others then logger.log_error(sqlerrm, l_scope, null, l_params); end log_insert; end ted_access_log;
Dynamic Action
Note: This object is located on page 0
Identification
Name
ted_access_log
Execution Options
Sequence
120
When
Event
Page Load
Advanced
Event Scope
Static
True Action
Identification
Action
Execute Server-side Code
Settings
Code
begin ted_access_log.log_insert; end;
Execution Options
Sequence
10
Fire On Initialization
No