Turning customers into rockstars, with Oracle Database, SQL and Oracle APEX.
declare
rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
begin
owa.init_cgi_env(:n__, :nm__, :v__);
htp.HTBUF_LEN := 63;
null; null;
simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%';
simple_list__(3) := 'utl\_%';
simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%';
simple_list__(7) := 'htf.%';
simple_list__(8) := 'wpg_docload.%';
simple_list__(9) := 'ctxsys.%';
simple_list__(10) := 'mdsys.%';
if ((owa_match.match_pattern(p_string => 'f' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then
rc__ := 2;
else
null; null; f(p=>:p);
if (wpg_docload.is_file_download) then
rc__ := 1;
wpg_docload.get_download_file(:doc_info);
null; null; null;
commit;
else
rc__ := 0; null; null; null; commit;
owa.get_page(:data__, :ndata__);
end if;
end if;
:rc__ := rc__;
end;
select sum(elapsed_time), count(*),
to_char(avg(elapsed_time),'9999.99') avg_elap,
a.application_id||':'||a.page_id app_and_page,
dbms_lob.substr(b.region_source,2000,1)
from apex_workspace_activity_log a
join apex_application_page_regions b
on a.application_id = b.application_id and a.page_id = b.page_id
where a.view_date between to_date('201112142100','RRRRMMDDHH24MI')
and to_date('201112142105','RRRRMMDDHH24MI')
and a.page_id is not null
and b.source_type like '%Report%'
group by a.application_id||':'||a.page_id, dbms_lob.substr(b.region_source,2000,1)
having count(*) > 10
order by 3 desc
<style>
#apexir_EXPR, #apexir_col_values_drop { width: 300px; }
</style>
declare
rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
begin
owa.init_cgi_env(:n__, :nm__, :v__);
htp.HTBUF_LEN := 63;
null; null;
simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%';
simple_list__(3) := 'utl\_%';
simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%';
simple_list__(7) := 'htf.%';
simple_list__(8) := 'wpg_docload.%';
simple_list__(9) := 'ctxsys.%';
simple_list__(10) := 'mdsys.%';
if ((owa_match.match_pattern(p_string => 'f' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then
rc__ := 2;
else
null; null; f(p=>:p);
if (wpg_docload.is_file_download) then
rc__ := 1;
wpg_docload.get_download_file(:doc_info);
null; null; null;
commit;
else
rc__ := 0; null; null; null; commit;
owa.get_page(:data__, :ndata__);
end if;
end if;
:rc__ := rc__;
end;
select workspace, application_name, application_id, page_id, count(*) total_page_events, avg(elapsed_time) avg_elapsed_time, sum(elapsed_time) elapsed_time
from apex_workspace_activity_log
where view_date between to_date('201110260900','RRRRMMDDHH24MISS') and to_date('201110261100','RRRRMMDDHH24MISS')
group by workspace, application_name, application_id, page_id
order by 6, 7 asc
Firstly, this post has nothing to do with Oracle nor Oracle Application Express. So you can stop reading now, if you wish. Also, this is a shameless request for financial donations. So, again, you can stop reading now. As our friend Carl Backstrom said so many years ago, "Hey it's my blog I can post what I want ! :)"
| HTML DB 1.5 | 2004 |
| HTML DB 1.6 | 2005 |
| HTML DB 2.0 | 2005 |
| Application Express 2.1 | 2006 |
| Application Express 2.2 | 2006 |
| Application Express 3.0 | 2007 |
| Application Express 3.1 | 2008 |
| Application Express 3.2 | 2009 |
| Application Express 4.0 | 2010 |
| Application Express 4.1 | 2011* |
begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;
begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;
begin
execute immediate ('begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;');
exception when others then
g_content_length := 0;
end;
DELETE FROM wwv_flow_data
WHERE flow_instance = :b1
AND item_id IN (
SELECT ID
FROM wwv_flow_page_plugs
WHERE flow_id = :b3
AND page_id = :b2
AND plug_source_type IN
('SIMPLE_CHART', 'UPDATABLE_SQL_QUERY',
'DBMSSQL_CURSOR',
'FUNCTION_RETURNING_DBMSSQL_CURSOR',
'FUNCTION_RETURNING_SQL_QUERY_CACHED',
'FUNCTION_RETURNING_SQL_QUERY', 'STRUCTURED_QUERY',
'SQL_QUERY', 'DYNAMIC_QUERY'))
| Tablespace Name | Bytes | Amount Used | Amount Free | Percentage Used |
| APEX_1193421002954380607 | 43,057,152 | 40,697,856 | 2,359,296 | 94.52 |
SQL> select bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name = 'APEX_1193421002954380607';
BYTES AUT MAXBYTES
---------- --- ----------
45154304 YES 320339968
2006 83,482
2007 107,021
2008 119,092
2009 148,304
2010 209,555
Total Page Views: 4,882,817
Distinct Applications: 3,644
Distinct Users: 4,141
Number of Workspaces: 10,777
Number of Applications: 37,883
Workspaces Approved: 535
Workspaces Purged: 578
To be purged in a week: 594
create table click_log(
user_name varchar2(1000),
click_ts timestamp,
ip_address varchar2(100),
user_agent varchar2(1000),
apex_session_id number,
rownum_clicked number,
info varchar2(4000) )
insert into click_log(
user_name,
click_ts,
ip_address,
user_agent,
apex_session_id,
rownum_clicked,
info )
values
( v('APP_USER'),
systimestamp,
owa_util.get_cgi_env('REMOTE_ADDR'),
owa_util.get_cgi_env('HTTP_USER_AGENT'),
v('APP_SESSION'),
v('P5_ID'),
'Clicked: ' || v('P5_NAME') );
commit;
<a href="http://www.amazon.com/" target="_new">http://www.amazon.com</a>
<a href="http://www.amazon.com/" target="_new" id="link">http://www.amazon.com</a>
create table click_log(
user_name varchar2(1000),
click_ts timestamp,
ip_address varchar2(100),
user_agent varchar2(1000),
apex_session_id number,
rownum_clicked number,
info varchar2(4000) )
insert into click_log(
user_name,
click_ts,
ip_address,
user_agent,
apex_session_id,
rownum_clicked,
info )
values
( v('APP_USER'),
systimestamp,
owa_util.get_cgi_env('REMOTE_ADDR'),
owa_util.get_cgi_env('HTTP_USER_AGENT'),
v('APP_SESSION'),
v('P2_ID'),
'Clicked: ' || v('P2_NAME') );
commit;
function f_logClick(){
var req = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=LOG_CLICK',&APP_PAGE_ID.);
var gReturn = req.get();
req = null;
return gReturn;
}
<a href="http://www.amazon.com/" target="_new">http://www.amazon.com</a>
<a href="http://www.amazon.com/" target="_new" onclick="f_logClick();">http://www.amazon.com</a>
