Monday, October 25, 2010

APEX Hash Tag on Twitter: #orclapex

The old Oracle APEX hash tag on twitter was #apex. If you search for #apex on Twitter you get a lot of non Oracle APEX related topics as apex is a common name.

Today I proposed a new hash tag for Oracle APEX related content on Twitter. Thanks to Scott Spendolini for coming up with the final name and Dan McGhan for providing feedback.

When posting on Oracle APEX related content on Twitter please us #orclapex

Thursday, October 21, 2010

APEX Reports: No Limit Downloads

When configuring a standard report in APEX you can define the maximum number of rows that the query will return. This setting is called Max Row Count and can be configured in the Report Attributes tab. If you leave it blank it will default to 500 rows.


It is recommended that you set the Max Row Count with a low value. Using a small number will help improve performance since it reduces the amount rows APEX must count in order to display the pagination information. On a business perspective, it may not make sense to return thousands of rows for a user to view online.

What if the user wants to retrieve all the data when they download the report? From the end users perspective this makes sense since they may want to extract all the data to do some custom analysis.

My first thought was to use a page item for the Maximum Row Count. I quickly discovered that it only takes a numeric value. As a work around you can control the rows returned directly from the SQL statement. Here's an example of this:

- Create a large table:

CREATE TABLE large_emp
AS
SELECT *
FROM emp
CONNECT BY LEVEL <= 5;
- Create standard report
Create a standard report using the following query. Set the Max Row Count to 999999999 (i.e. some very large number)

SELECT ename,
job,
sal,
comm
FROM large_emp
WHERE ROWNUM <= CASE WHEN :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN ROWNUM ELSE 500 END;
You are manually defining the Max Row Count by adding in ROWNUM predicate.

When users views the report in APEX it will only display a maximum of 500 rows. When they download the report the csv file will contain all the rows.

A caveat with this approach is that if your report has more than 500 rows you won't see the "... of more than 500" rows message as part of the pagination. Instead users will see "rows ... of 500" in the pagination. Users may be misled to think that the report only has 500 rows of data.

When I reviewed this technique I was concerned about performance issues when viewing the report in APEX. Here are the TKPROF outputs of the original query (with Max Row Count = 500) and the alternate query with the ROWNUM predicate (Max Row Count = 999999999):


-- 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)
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.

Wednesday, October 20, 2010

Oracle XE and APEX: Where is my Trace File?

In APEX you trace your session by adding &p_trace=YES at the end of your URL. For example: http://fcapex40:8080/apex/f?p=101:1:2603384364125271::NO&p_trace=YES Please refer to the APEX documentation for more information regarding traces in APEX.

The documentation states that the trace file is located in the following directory:

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

This isn't the case if you're using the Embedded PL/SQL Gateway, which is how APEX is run on Oracle XE. Thanks to Jari for answering my question on the APEX forum about this: http://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=1771547

For APEX installations that use the PL/SQL Embedded Gateway (i.e. Oracle XE) the trace file is located in the following directory:

SQL> SHOW PARAMETER background_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /usr/lib/oracle/xe/app/oracle/
admin/XE/bdump

To find the trace file that was created for your session:

$ cd /usr/lib/oracle/xe/app/oracle/admin/XE/bdump
$ grep -l "APP_SESSION" *.trc
#example
$ grep -l "2603384364125271" *.trc

Tuesday, October 19, 2010

APEX Region Errors - Part 2

Disclaimer: This is an advanced post that discusses and modifies some of the inner workings of APEX.

In my previous post on APEX Region Errors I wrote about logging region errors in APEX. In this post I'll cover how to automatically be notified when a region error occurs and store some additional log information.

It's important that you actually read my previous posts on APEX Region Errors and Custom Error Messages in APEX. The rest of this post will assume that you've read both of these posts.

Before we continue we need to review how the activity log works. APEX_WORKSPACE_ACTIVITY_LOG is a view which references APEX_040000. WWV_FLOW_ACTIVITY_LOG. WWV_FLOW_ACTIVITY_LOG is a view which joins APEX_040000.WWV_FLOW_ACTIVITY_LOG1$ and APEX_040000.WWV_FLOW_ACTIVITY_LOG2$. The first paragraph of APEX Logs: Storing Log Data provides a brief description of why the logs are stored in two tables.

In order to have advanced log information and notify an administrator when a region error occurs we will apply a trigger on each of the log tables.

Disclaimer (again): Modifying anything in the APEX schema will put your APEX instance in an unsupported state. Please proceed with caution. I take no responsibility for any negative outcomes about this. Joel Kallman wrote about The Perils of Modifying the Application Express Metadata in regards to Oracle support.

- Install Logger and compile the sp_log_error_page procedure.
Install logger and sp_log_error_page in the schema that your APEX application will run against.
Logger: http://logger.samplecode.oracle.com
sp_log_error_page: http://www.talkapex.com/2010/09/custom-error-messages-in-apex.html

- Apply trigger to log tables
Apply triggers to both of the activity log tables so that we can be notified right away when a page or region level error occurs.

As SYSTEM compile the following triggers:

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;

And... (differences are highlighted)

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;


Now when an page or region level error occurs you will have advanced logging information and you have the option to be notified by email. The log information will be stored in your schema in the LOGGER_LOGS table

APEX Region Errors - Part 3

Monday, October 18, 2010

APEX Region Errors - Part 1

A while ago I wrote about how to handle and customize the default unhandled exception error page in APEX: http://www.talkapex.com/2010/09/custom-error-messages-in-apex.html The method described in the post catches page level errors and can notify administrators immediately when an undhandled exception occurs.

Region level errors are not caught using the method above. An example of a region error is when you have a query that has divisor is equal to zero error. To simulate this error create a standard report region with the following query:

SELECT *
FROM emp
WHERE 1 / 0 = 0 -- Causes an ORA-01476: divisor is equal to zero

This query will return a "ORA-01476: divisor is equal to zero" error message in APEX.


Starting with APEX 4.0 region level errors are logged in the APEX Activity Log. The following query identifies both page and region level errors:

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

I strongly recommend that you frequently scan the activity logs for errors. Part 2 of will describe a way to be automatically notified of region level errors.


They're a few caveats that you should be aware of:

- As mentioned, the activity log identifies both region and page level errors. APEX_WORKSPACE_ACTIVITY_LOG.ERROR_ON_COMPONENT_NAME identifies the region or process that the error occured in.

- When multiple region level errors occur only the first error is logged in the activity log.

- From my limited testing, errors in Interactive Reports are not logged. Their may be other types of regions that don't log errors as well.

Given the caveats listed above querying the activity logs help identify some, but not all, errors that happen in your application.

APEX Region Errors - Part 2