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

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.

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

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

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