SELECT column_nameIn case you don't have an old 3.2 instance on your database the new columns are:
FROM all_tab_columns
WHERE owner = 'APEX_040000'
AND table_name = 'APEX_WORKSPACE_ACTIVITY_LOG'
MINUS
SELECT column_name
FROM all_tab_columns
WHERE owner = 'APEX_030200'
AND table_name = 'APEX_WORKSPACE_ACTIVITY_LOG'
ORDER BY column_name
- Create standard report
CREATE TABLE large_emp
AS
SELECT *
FROM emp
CONNECT BY LEVEL <= 5;
You are manually defining the Max Row Count by adding in ROWNUM predicate.
SELECT ename,
job,
sal,
comm
FROM large_emp
WHERE ROWNUM <= CASE WHEN :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN ROWNUM ELSE 500 END;
I'm not a performance guru and I can't comment too much on these outputs. If you think this will have negative effects on performance please add your feedback as a comment on this post.
-- Both outputs are for VIEWING the report (not downloading it)
--
-- Original Query
-- Max Row Count = 500
-- Run for displaying rows 1~15
SELECT ename, job, sal, comm
FROM large_emp
order by 3 -- Automatically added by APEX
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 501 0.65 1.42 14 3417 3 501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 504 0.65 1.42 14 3418 3 501
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
501 SORT ORDER BY (cr=3417 pr=14 pw=2053 time=1417580 us)
579194 TABLE ACCESS FULL LARGE_EMP (cr=3417 pr=0 pw=0 time=1158549 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write temp 70 0.00 0.00
direct path read temp 2 0.00 0.00
-- *************************************
-- New Query
-- Max Row Count = 9999999999
-- Run for displaying rows 1~15
SELECT ename, job, sal, comm
FROM large_emp
WHERE ROWNUM <= CASE WHEN :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN ROWNUM ELSE 500 END
order by 3 -- Automatically added by APEX
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 1 0
Fetch 501 0.38 0.53 0 3417 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 505 0.38 0.54 0 3417 1 500
Misses in library cache during parse: 1
Parsing user id: 40 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
500 SORT ORDER BY (cr=3417 pr=0 pw=0 time=535125 us)
500 COUNT (cr=3417 pr=0 pw=0 time=3376 us)
500 FILTER (cr=3417 pr=0 pw=0 time=2298 us)
579194 TABLE ACCESS FULL LARGE_EMP (cr=3417 pr=0 pw=0 time=5212959 us)
SQL> -- Run as SYSTEM
SQL> SHOW PARAMETERS user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /usr/lib/oracle/xe/app/oracle/
admin/XE/udump
SQL> SHOW PARAMETER background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /usr/lib/oracle/xe/app/oracle/
admin/XE/bdump
$ cd /usr/lib/oracle/xe/app/oracle/admin/XE/bdump
$ grep -l "APP_SESSION" *.trc
#example
$ grep -l "2603384364125271" *.trc
CREATE OR REPLACE TRIGGER giffy.trg_apex_activity_log1_air -- Where "giffy" is the schema that I am running my APEX app in. Change accordingly
AFTER INSERT
ON apex_040000.wwv_flow_activity_log1$
FOR EACH ROW
WHEN (new.SQLERRM IS NOT NULL)
DECLARE
v_count PLS_INTEGER;
BEGIN
-- Check if the application belongs to this schema
SELECT COUNT (application_id)
INTO v_count
FROM apex_applications
WHERE application_id = :new.flow_id
AND owner = 'GIFFY'; -- Schema that I am running in
IF v_count > 0 THEN
sp_log_error_page (p_scope_prefix => 'apex.region_error',
p_application_id => :new.flow_id,
p_page_id => :new.step_id,
p_oracle_err_msg => :new.SQLERRM,
p_apex_err_msg => '{component_type: ' || :new.sqlerrm_component_type || ', component_name: ' || :new.sqlerrm_component_name || '}',
p_email => NULL);
END IF;
END;
CREATE OR REPLACE TRIGGER giffy.trg_apex_activity_log2_air -- Where "giffy" is the schema that I am running my APEX app in. Change accordingly
AFTER INSERT
ON apex_040000.wwv_flow_activity_log2$
FOR EACH ROW
WHEN (new.SQLERRM IS NOT NULL)
DECLARE
v_count PLS_INTEGER;
BEGIN
-- Check if the application belongs to this schema
SELECT COUNT (application_id)
INTO v_count
FROM apex_applications
WHERE application_id = :new.flow_id
AND owner = 'GIFFY'; -- Schema that I am running in
IF v_count > 0 THEN
sp_log_error_page (p_scope_prefix => 'apex.region_error',
p_application_id => :new.flow_id,
p_page_id => :new.step_id,
p_oracle_err_msg => :new.SQLERRM,
p_apex_err_msg => '{component_type: ' || :new.sqlerrm_component_type || ', component_name: ' || :new.sqlerrm_component_name || '}',
p_email => NULL);
END IF;
END;
SELECT *
FROM emp
WHERE 1 / 0 = 0 -- Causes an ORA-01476: divisor is equal to zero
SELECT apex_user,
application_id,
application_name,
page_id,
page_name,
view_date,
apex_session_id,
error_message,
error_on_component_type,
error_on_component_name -- Region/Process that caused the issue
FROM apex_workspace_activity_log
WHERE error_message IS NOT NULL
SELECT *
FROM apex_040000.wwv_flow_lock_page_log
WHERE lock_flow = :app_id
ORDER BY lock_page, action_date
SELECT *
FROM emp
WHERE deptno = :p1_deptno
/**
* Logs unhandled error message to Database
* Logs:
* - APEX and Oracle error messages
* - All application level items
* - All page items for p_page_id
* - Items on other pages that are not null
*
* Logs are stored in logger_logs
* Requires: https://logger.samplecode.oracle.com/
*
* @param p_scope_prefix Scope prefix used in logger
* @param p_application_id
* @param p_page_id Page that error occured on
* @param p_oracle_error_msg Oracle error message
* @param p_apex_error_msg APEX error message
* @param p_email Email to be notified of error. If null, then no notification email sent.
* @author Martin Giffy D''Souza http://www.talkapex.com
*/
CREATE OR REPLACE PROCEDURE sp_log_error_page (p_scope_prefix IN VARCHAR2,
p_application_id IN apex_applications.application_id%TYPE DEFAULT v ('APP_ID'),
p_page_id IN apex_application_pages.page_id%TYPE,
p_oracle_err_msg IN VARCHAR2 DEFAULT NULL,
p_apex_err_msg IN VARCHAR2 DEFAULT NULL,
p_email IN VARCHAR2 DEFAULT NULL)
AS
v_db_name VARCHAR2 (30);
v_schema VARCHAR2 (30);
v_scope VARCHAR2 (255);
BEGIN
-- Set scope for logger
v_scope := p_scope_prefix;
-- Add Uniqe Identifier to scope
v_scope := LOWER (v_scope || 'unhandeled_exception{session_id: ' || v ('APP_SESSION') || ', guid: ' || SYS_GUID () || '}');
-- Log the initial error to be kept permanently
logger.log_error ('Unhandeled Exception', v_scope, 'Oracle Error: ' || p_oracle_err_msg || CHR (10) || CHR (10) || 'APEX Error Page Message: ' || p_apex_err_msg);
-- Log the information to help Dev team in production
FOR x IN (SELECT 'APP_USER' item_name, v ('APP_USER') item_val FROM DUAL
UNION
SELECT 'APP_SESSION' item_name, v ('APP_SESSION') FROM DUAL
UNION
-- Include all the items from the current page
SELECT item_name, v (item_name) item_val
FROM apex_application_page_items
WHERE application_id = p_application_id
AND page_id = p_page_id
UNION
-- Include all the non-null page items
SELECT item_name, item_val
FROM (SELECT item_name, v (item_name) item_val
FROM apex_application_page_items
WHERE application_id = p_application_id
AND page_id != p_page_id)
WHERE item_val IS NOT NULL
UNION
-- Include all Application Items
SELECT item_name, v (item_name)
FROM apex_application_items
WHERE application_id = p_application_id)
LOOP
logger.log_information (x.item_name || ': ' || x.item_val, v_scope);
END LOOP;
-- Email Notification
IF p_email IS NOT NULL THEN
-- Send Mail
apex_mail.send (p_to => p_email,
p_from => 'martin@talkapex.com', -- CHANGE THIS!
p_body => 'An unhandled exception happend in an application. Please search logger logs for: ' || v_scope,
p_subj => 'Unhandled Exception in: ' || p_application_id);
END IF;
END sp_log_error_page;
DECLARE
BEGIN
sp_log_error_page (p_scope_prefix => 'apex.demo.', -- Enter what ever you want to help identify your apex errors in the log tables
p_application_id => :app_id,
p_page_id => :p200_page_id,
p_oracle_err_msg => :p200_ora_msg,
p_apex_err_msg => :p200_apex_msg,
p_email => '' -- Enter your email address here
);
END;
SELECT *
FROM logger_logs
WHERE scope = 'apex.demo.unhandeled_exception{session_id: 652754467566839, guid: 901e0663a0896b35e040007f0100049a}'; -- Replace this scope with the scope that is sent in the email
SELECT ap.page_id,
ap.page_name,
irc.column_alias,
irc.help_text
FROM apex_application_page_ir_col irc, apex_application_pages ap
WHERE irc.application_id = :app_id
AND irc.application_id = ap.application_id
AND irc.page_id = ap.page_id
AND irc.help_text = 'No help available for this page item.' -- Default help text message
SELECT username
FROM dba_users
WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%')
AND USERNAME NOT IN (
SELECT 'FLOWS_FILES'
FROM DUAL
UNION
SELECT 'APEX_PUBLIC_USER' FROM DUAL
UNION
SELECT SCHEMA s
FROM dba_registry
WHERE comp_id = 'APEX')
DROP USER FLOWS_030000 CASCADE; -- Where "FLOWS_030000" is the username from the previous query
console.log('hello world');
$.console.setLevel('log');
$.console.log('Current Level', $.console.getLevel());
$.console.group('Available Levels');
$.each($.console.levels, function(i, val){
$.console.log(i);
});
$.console.groupEnd();
$.console.log(($.console.isApexDebug()) ? 'In APEX debug mode' : 'Not in APEX debug mode');
$.console.group('Demo Chaining');
//Notice how you can write this out all in 1 line!
$('.red').log('Before Red').css({color:'red'}).log('After Red');
$.console.groupEnd();
$.console.info('Turning off console');
$.console.setLevel('off');
SELECT e.*, SUM (e.sal) OVER () test
FROM emp e
CONNECT BY LEVEL <= 5
//From
if (this.action.attribute01)
//do something
//To
if (this.action.attribute01.toLowerCase() == 'true')
//do something
x = null;
console.log(x, x ? true : false);
x = '';
console.log(x, x ? true : false);
x = 'false';
console.log(x, x ? true : false);
x = 'true';
console.log(x, x ? true : false);
CREATE OR REPLACE VIEW vemp AS
SELECT * FROM emp
WHERE deptno = some_value_from_a_context
-- Create Context
-- ctx_vpd is our context name
-- pkg_vpd is the only place where we can modify values in this context
-- This provides a lot of security since single access point
CREATE OR REPLACE CONTEXT ctx_vpd USING pkg_vpd ACCESSED GLOBALLY;
-- VPD Demo Package from www.talkapex.com
-- @author Martin Giffy D'Souza - martin@talkapex.com
CREATE OR REPLACE PACKAGE pkg_vpd
AS
--
-- Login function for APEX to login users
-- Spec must be as is, as required by APEX for custom authentication schemes
-- For demo purposes we'll use ENAME = ENAME from EMP
-- @param p_username username
-- @param p_password password
-- @return TRUE or FALSE
--
FUNCTION f_login (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN;
--
-- Set context identifier
-- This will register our "key" which will be required each time we want to access a Context (name/value) pair
-- @param p_session_key in APEX use :APP_USER || ':' || :APP_SESSION
--
PROCEDURE sp_set_context_identifier (p_session_key IN VARCHAR2);
--
-- Set context value
-- i.e. Sets a value for the name/value pair
--
PROCEDURE sp_set_context_value (p_name IN VARCHAR2, p_value IN VARCHAR2);
--
-- Get Context Value
-- i.e. Get value for name/value pair
--
FUNCTION f_get_context_value (p_name IN VARCHAR2)
RETURN VARCHAR2;
--
-- To be run in Post Authentication Process in APEX
-- Sets some of the name/value pairs required for VPD
--
PROCEDURE sp_apex_post_auth;
END pkg_vpd;
/
-- VPD Demo Package from www.talkapex.com
-- @author Martin Giffy D'Souza - martin@talkapex.com
CREATE OR REPLACE PACKAGE BODY pkg_vpd
AS
--
-- Login function for APEX to login users
-- Spec must be as is, as required by APEX for custom authentication schemes
-- For demo purposes we'll use ENAME = ENAME from EMP
-- @param p_username username
-- @param p_password password
-- @return TRUE or FALSE
--
FUNCTION f_login (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
v_count PLS_INTEGER;
BEGIN
SELECT COUNT (e.ename)
INTO v_count
FROM emp e -- Notice how I'm not reference the view (vemp) it would return no rows at this point
WHERE LOWER (e.ename) = LOWER (p_username)
AND LOWER (p_username) = LOWER (p_password);
IF v_count = 1 THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END f_login;
--
-- Set context identifier
-- This will register our "key" which will be required each time we want to access a Context (name/value) pair
-- @param p_session_key in APEX use :APP_USER || ':' || :APP_SESSION
--
PROCEDURE sp_set_context_identifier (p_session_key IN VARCHAR2)
AS
BEGIN
dbms_session.set_identifier (client_id => p_session_key);
END sp_set_context_identifier;
--
-- Set context value
-- i.e. Sets a value for the name/value pair
--
PROCEDURE sp_set_context_value (p_name IN VARCHAR2, p_value IN VARCHAR2)
AS
BEGIN
dbms_session.set_context ('CTX_VPD',
p_name,
p_value,
USER,
SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER'));
END;
--
-- Get Context Value
-- i.e. Get value for name/value pair
--
FUNCTION f_get_context_value (p_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN SYS_CONTEXT ('CTX_VPD', p_name);
END f_get_context_value;
--
-- To be run in Post Authentication Process in APEX
-- Sets some of the name/value pairs required for VPD
--
PROCEDURE sp_apex_post_auth
AS
v_empno emp.empno%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
-- Get User Information
SELECT empno, deptno
INTO v_empno, v_deptno
FROM emp
WHERE LOWER (ename) = LOWER (v ('app_user'));
-- Set Context Identifier
pkg_vpd.sp_set_context_identifier (p_session_key => v ('app_user') || ':' || v ('app_session'));
-- Set Name/Value pairs
pkg_vpd.sp_set_context_value (p_name => 'EMPNO', p_value => v_empno);
pkg_vpd.sp_set_context_value (p_name => 'DEPTNO', p_value => v_deptno);
-- This demo won't highlight the Last Access date, but can be very useful to kill sessions in the back end that have not been access for a given period of time.
pkg_vpd.sp_set_context_value (p_name => 'LAST_ACCESS', p_value => TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END sp_apex_post_auth;
END pkg_vpd;
/
Create Custom Authentication Scheme
CREATE OR REPLACE FORCE VIEW vemp
AS
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno
FROM emp e
WHERE e.deptno = pkg_vpd.f_get_context_value ('DEPTNO'); -- Can only view employees in same department
/
SELECT *
FROM vemp
SELECT SYN.SYNONYM_NAME apex_view_name
FROM all_synonyms syn, all_views av, apex_dictionary ad
WHERE syn.synonym_name LIKE 'APEX\_%' ESCAPE '\'
AND syn.owner = 'PUBLIC'
AND SYN.TABLE_NAME = AV.VIEW_NAME
AND SYN.SYNONYM_NAME = AD.APEX_VIEW_NAME(+)
AND ad.column_id IS NULL
ORDER BY syn.synonym_name
SELECT *
FROM apex_workspace_activity_log
WHERE application_id = :app_id
AND page_id = :app_page_id
CREATE TABLE TAPEX_WORKSPLACE_REPORT_DL_LOG
(
WORKSPACE VARCHAR2 (255 BYTE) NOT NULL,
WORKSPACE_ID NUMBER NOT NULL,
APPLICATION_ID NUMBER NOT NULL,
APPLICATION_NAME VARCHAR2 (255 BYTE) NOT NULL,
APPLICATION_SCHEMA_OWNER VARCHAR2 (30 BYTE) NOT NULL,
PAGE_ID NUMBER NOT NULL,
PAGE_NAME VARCHAR2 (255 BYTE) NOT NULL,
REGION_ID NUMBER NOT NULL,
REGION_NAME VARCHAR2 (255 BYTE) NOT NULL,
VIEW_DATE DATE NOT NULL,
APEX_USER VARCHAR2 (255 BYTE) NOT NULL,
APEX_SESSION_ID NUMBER NOT NULL
);
/**
* Insert Download Report Record
* @param p_region_id Report Region ID
*/
CREATE OR REPLACE PROCEDURE sp_log_apex_dl_report (
p_region_id IN apex_application_page_regions.region_id%TYPE)
AS
BEGIN
-- Insert into report download log table
INSERT INTO tapex_worksplace_report_dl_log (WORKSPACE,
WORKSPACE_ID,
APPLICATION_ID,
APPLICATION_NAME,
APPLICATION_SCHEMA_OWNER,
PAGE_ID,
PAGE_NAME,
REGION_ID,
REGION_NAME,
VIEW_DATE,
APEX_USER,
APEX_SESSION_ID)
SELECT aapr.workspace,
AA.WORKSPACE_ID,
aapr.application_id,
aapr.application_name,
aa.owner,
aapr.page_id,
AAPr.PAGE_NAME,
aapr.region_id,
aapr.region_name,
SYSDATE,
v ('APP_USER'),
v ('APP_SESSION')
FROM APEX_APPLICATION_PAGE_regionS aapr, APEX_APPLICATIONS aa
WHERE aapr.application_id = v ('APP_ID')
AND AAPr.PAGE_ID = v ('APP_PAGE_ID')
AND aapr.region_id = p_region_id
AND aapr.application_id = aa.application_id;
END sp_log_apex_dl_report;
DECLARE
v_region_id APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
BEGIN
-- If CSV/HTMLD is for IR (does not factor in email IRs). FLOW_EXCEL_OUTPUT is for Standard Report
IF :request IN ('CSV', 'HTMLD')
OR :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN
-- Check for Standard Report
IF :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN
v_region_id := REGEXP_SUBSTR (:request, '[[:digit:]]+');
ELSE
-- Interactive Report
SELECT region_id
INTO v_region_id
FROM APEX_APPLICATION_PAGE_ir
WHERE application_id = :app_id
AND page_id = :app_page_id;
END IF;
sp_log_apex_dl_report (p_region_id => v_region_id);
END IF;
END;
SELECT AR.APPLICATION_ID,
ar.application_name,
ar.page_id,
ar.page_name,
ar.region_id,
ar.region_name,
tdl.downloads,
al.page_views
FROM APEX_APPLICATION_PAGE_REGIONS ar,
(SELECT tdl.application_id,
tdl.page_id,
tdl.region_id,
COUNT (tdl.application_id) downloads
FROM TAPEX_WORKSPLACE_REPORT_DL_LOG tdl
GROUP BY tdl.application_id, tdl.page_id, tdl.region_id) tdl,
(SELECT al.application_id, al.page_id, COUNT (al.application_id) page_views
FROM apex_workspace_activity_log al
GROUP BY al.application_id, al.page_id) al
WHERE AR.APPLICATION_ID = :app_id
AND ar.source_type_code IN ('SQL_QUERY', 'DYNAMIC_QUERY')
-- Downloads
AND ar.region_id = tdl.region_id(+)
-- Page Views
AND ar.application_id = al.application_id(+)
AND ar.page_id = al.page_id(+)
SELECT *
FROM apex_release
Note: That they're ways to get around this simple example using Report Templates and Column Formatting. I'm just using it for demo purposes
SELECT '"' || color || '' color
FROM my_colors
And modify each report column's attributes:
SELECT '"' || color || '' color_html,
color color
FROM my_colors
SELECT LEVEL c001, LEVEL c002, LEVEL c003, LEVEL c004, LEVEL c005, LEVEL c006, LEVEL c007,
LEVEL c008, LEVEL c009, LEVEL c010, LEVEL c011, LEVEL c012, LEVEL c013, LEVEL c014,
LEVEL c015, LEVEL c016, LEVEL c017, LEVEL c018, LEVEL c019, LEVEL c020, LEVEL c021,
LEVEL c022, LEVEL c023, LEVEL c024, LEVEL c025, LEVEL c026, LEVEL c027, LEVEL c028,
LEVEL c029, LEVEL c030, LEVEL c031, LEVEL c032, LEVEL c033, LEVEL c034, LEVEL c035,
LEVEL c036, LEVEL c037, LEVEL c038, LEVEL c039, LEVEL c040, LEVEL c041, LEVEL c042,
LEVEL c043, LEVEL c044, LEVEL c045, LEVEL c046, LEVEL c047, LEVEL c048, LEVEL c049,
LEVEL c050, LEVEL c051, LEVEL c052, LEVEL c053, LEVEL c054, LEVEL c055, LEVEL c056,
LEVEL c057, LEVEL c058, LEVEL c059, LEVEL c060, LEVEL c061, LEVEL c062, LEVEL c063,
LEVEL c064, LEVEL c065, LEVEL c066, LEVEL c067, LEVEL c068, LEVEL c069, LEVEL c070,
LEVEL c071, LEVEL c072, LEVEL c073, LEVEL c074, LEVEL c075, LEVEL c076, LEVEL c077,
LEVEL c078, LEVEL c079, LEVEL c080, LEVEL c081, LEVEL c082, LEVEL c083, LEVEL c084,
LEVEL c085, LEVEL c086, LEVEL c087, LEVEL c088, LEVEL c089, LEVEL c090, LEVEL c091,
LEVEL c092, LEVEL c093, LEVEL c094, LEVEL c095, LEVEL c096, LEVEL c097, LEVEL c098,
LEVEL c099, LEVEL c100, LEVEL c101, LEVEL c102, LEVEL c103, LEVEL c104, LEVEL c105,
LEVEL c106, LEVEL c107, LEVEL c108
FROM DUAL
CONNECT BY LEVEL <= 10
SELECT column_id,
column_alias,
display_order,
report_label
FROM APEX_APPLICATION_PAGE_IR_COL
WHERE application_id = :app_id
AND page_id = :page_id
apex_p601_setColumnIDandSubmit('311507131164520006'); // Enter your column ID here