Column Groups in APEX 4.0 Interactive Reports

A long time ago I wrote about adding Column Groups to Interactive Reports (IR) in APEX: http://www.talkapex.com/2009/03/column-groups-in-apex-interactive.html I created a new dynamic action plugin to do column grouping in APEX which can be downloaded here. Please read the help text in the plugin for full instructions on how to use it. Once integrated it should look like:


I also added additional JS debugging information. If you run your application in debug mode and look at the console you'll notice the following log information:


The additional logging is using the console logger wrapper that wrote: http://www.talkapex.com/2010/08/javascript-console-logger.html If you're writing your own plugins I suggest using it to instrument your code. It helped me debug some of my JavaScript issues while developing this plugin.

APEX 4.0: New Columns in APEX_WORKSPACE_ACTIVITY_LOG

Over a year ago I wrote about how to permanently store some of the log information from APEX: http://www.talkapex.com/2009/05/apex-logs-storing-log-data.html APEX 4.0 has some additional columns in APEX_WORKSPACE_ACTIVITY_LOG which you may want to store as well. The following query identifies the new columns:

Note: This query assumes you still have an old instance of APEX 3.2 and a new instance of APEX 4.0 on your database

1 2 3 4 5 6 7 8 9 10

SELECT column_name   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

In case you don't have an old 3.2 instance on your database the new columns are:

CONTENT_LENGTH
INTERACTIVE_REPORT_ID
IR_SAVED_REPORT_ID
IR_SEARCH
WS_APPLICATION_ID
WS_DATAGRID_ID
WS_PAGE_ID

If you permanently store the log information you may want to update your tables and scripts to store the new information.

APEX: How to Trigger Dynamic Action From Button

Someone recently asked me how to trigger a dynamic action from a button. At first I thought this was trivial question until I tried to do it and found it's not as straight forward as I expected.

The following steps cover how to create a dynamic action on a button:

Modify Button Template:

You'll need to modify the button template to allow for the button attributes to be applied to the button. This will allow us to use an ID to identify a button. To apply the button attributes go to Shared Components > Templates > Select the default button template (for most instances called "Button"). Add #BUTTON_ATTRIBUTES# to the button tag in the Template section. For example from:

<button value="#LABEL#" onclick="#LINK#" class="button-gray" type="button">

To:

<button value="#LABEL#" onclick="#LINK#" class="button-gray" type="button" #BUTTON_ATTRIBUTES#>

Create Button
On the page you're working on create a Region Button:

Button Name: TEST BUTTON
Label: Test Button
Button Style: Template Based Button
Button Template: Button (or what ever button template you modified)
Button Attributes: id="test_button"
Make sure the id is unique

Action: Redirect to URL
URL Target: javascript:return;

Create Dynamic Action
Create a Dynamic Action. When you get to the "When" section:

Event: Click
Selection Type: DOM Object
DOM Object: test_button
The DOM object represents the ID that you defined while creating the button.
Condition: No Condition

Create your True and/or False actions accordingly.


Now when you run the page and click the button it should execute your dynamic action.

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:

1 2 3 4 5 6

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)

1 2 3 4 5 6 7

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

-- 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:

1 2 3 4 5 6 7 8 9

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:

1 2 3 4 5 6 7 8

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:

1 2 3 4 5 6

$ 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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:

1 2 3 4 5

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

APEX Page Locks

When developing APEX applications in a team environment you usually don't want multiple developers working on the same page at the same time. This can cause a lot of confusion and unexpected effects with the application.

APEX has a great feature called Page Locks which enables developers to lock a page so that they're the only one that can work on it at a given time. Other developers can view the page, but can not make any changes while the page is locked.

To lock a page, click on the lock icon located in the top right corner of the page as displayed in the following picture:


You will need to enter a comment and then click the Lock Checked button. For the comment it is recommended that you enter what case/bug number you're working on and what impact it may have on the page. The history of these comments, both for locking and unlocking the page, is logged. Before you invest a lot of time with detailed comments it's important to note the following points that may be relevant within your environment.

Page locks are not retained when an application is copied or exported. This makes sense as you don't want page locks to propagate in an export file.

Like page locks, the lock history is not retained when an application is copied or exported. If you tend to move the development copy of your application between workspaces this may be an issue. This may effect the level of detail for lock comments that you require the developers on your team to use.

If you want to backup the page lock comment history you can access them from the following table:

1 2 3 4 5 6

SELECT *     FROM apex_040000.wwv_flow_lock_page_log    WHERE lock_flow = :app_id ORDER BY lock_page, action_date

Demo APEX Right Out of the Box

If you want to quickly try out the new features after installing APEX 4.0 you don't need to do anything! APEX now comes with a built in demo.

To access this demo, log into a workspace and click the "Learn more..." button.


On the right hand side you'll see a list of features to demo.


If you're curious, the tables that the demo is using are: APEX_040000.WWV_DEMO_DEPT and APEX_040000.WWV_DEMO_EMP.

APEX IR: Subscriptions with Bind Variables and VPD

Starting in APEX 4.0, Interactive Reports (IR) now have the ability for users to automatically get reports emailed to them. This is a great feature to allow end users to subscribe to data extracts rather than developers writing custom code.

To enable subscriptions select the Subscription option in the IR Report Attributes page:


Users can subscribe to email notifications by selecting the Subscription option in the Actions menu



Before implementing subscriptions they're some things that you should be aware of.

If your query uses bind variables, they will not be bound in the emailed reports. For example, supposed you had a select list of departments, P1_DEPTNO. If you then had an IR that listed all the employees in the department it would look like:

1 2 3 4 5

SELECT *   FROM emp  WHERE deptno = :p1_deptno

When a user is viewing the report in the application they'll get some employees for each department. If they subscribe to this IR then they'll get no rows returned as no value is bound for :P1_DEPTNO.

If you use the VPD feature in Oracle (Shared Components > Security Attributes > Virtual Private Database) it doesn't appear to be fired before the query is run as part of the subscription. I tested this by calling a log procedure in the VPD section. When the report is generated for an email subscription no log was registered in my log table.

I wouldn't classify either of these cases as bugs, however it's important for you to know what subscriptions can and can't handle before leveraging the subscriptions feature in a production application.

APEXposed 2010

I'll be giving a few presentations this year at APEXposed 2010 in Dallas, Texas.

This will be my first APEXposed 2010 event and I'm really excited to go since they're some excellent presenters this year. If you're new to APEX this will be an excellent opportunity to learn from some of the best in the APEX community.

If you haven't already done so I encourage you to register soon to take advantage of the early bird rates.

I'll be doing 2 presentations and participating in a workshop. I've included the abstracts for them below:

APEX Plug-ins: One of the most anticipated new features in APEX 4.0 is Plug-Ins which allows developers to declaratively extend existing functionality in APEX. This presentation will go over the different types of Plug-Ins and how to create them. Primary focus will be on Item, Process, and Region type Plug-Ins.

Creating your first Plug-In can be intimidating, especially for developers new APEX developers. Some tips to help with obstacles that developers may encounter will be discussed.

Team Development: Managing a development project can be a cumbersome activity without the right tools. Beginning with 4.0, APEX now includes a software development management tool that integrates with your APEX applications.

This presentation will go over these tools and how they can help manage the development life cycle of your APEX application.

Authentication Workshop: I'll be participating in this workshop. Here is the abstract

Who are you, really? This workshop will demonstrate many methods of answering that question, from built-in authentication schemes to custom single sign-on. LDAP, RSA, home-grown. We will provide methods to solve many common authentication requirements.

ODTUG: How to be Creative

At ODTUG Kaleidoscope I promised that I would post my slides from my presentation. Here is a copy of the How to be Creative presentation.

The demos can be found here:

Apex Dictionary: http://www.talkapex.com/2010/07/missing-views-from-apexdictionary.html
and http://www.talkapex.com/2008/11/how-to-list-apex-dictionary-views-using.html

Tooltips: http://www.talkapex.com/2009/09/tooltip-help-in-apex-alternative-to.html

Standardized Help: http://www.talkapex.com/2009/10/standardizing-help-text-in-apex.html

APEX Logs: http://www.talkapex.com/2009/03/custom-authentication-status.html

Custom Error Messages: http://www.talkapex.com/2010/09/custom-error-messages-in-apex.html

Custom Error Messages in APEX

As developers we try to prevent unhandled exceptions from occurring for end users. They can occur in any program or language, and APEX is no exception (pardon the pun).

When an unhandled exception happens, users are presented with an error message which is similar to the following:


This error message isn't very user friendly and most users won't know what an "ORA-..." message means. The other issue with this screen is that it does not provide any feedback to developers. If the user does not report this issue developers won't know that it is happening.

As part of my ODTUG presentation I demonstrated how to alter the default error page to provide a user friendly error message and provide an instant notification to developers that an error has occurred. This post will describe how to do implement a user friendly error handling method in APEX 4.0.

Special thanks to Roel Hartman and Learco Brizzi for providing the ideas behind this.

- Install Logger
Logger is an open source package written by Tyler Muth. It's an excellent tool to quickly allow developers to instrument their code. Though it is not required, this demo references it. A copy of logger is available here: http://logger.samplecode.oracle.com/.

- Install Simple Modal Plug-in
Install the Simple Modal plugin into your APEX application: http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/simple-modal.html. When you download this plugin, the zip file contains 2 plugins. One to show the modal window and one to close the modal window. It is recommended that you install both if you plan to use it in other applications. Only the "Simple Modal - Show" plug is required for this demo.

- Create Error Procedure
Compile this procedure in your schema. It will log all the page items, application items, and all other not-null page items.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

/**  * 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;

- Create Error Page
This page will display a user friendly message to the user. For the purposes of this demo Page 200 will be created to handle error messages.

Create Page
Create Page 200

Create a HTML region
Region Name: Unknown Error
Source: An unhandled error occurred. A notification has been sent to the system administrator.

Create a Region Button:
Button Name: Back
Action: Redirect to URL
Execute Validations: No
URL Target: javascript:window.history.go(-1);

Add the following hidden items:
P200_PAGE_ID
P200_ESCAPE
P200_ORA_MSG
P200_APEX_MSG

Create Computation:
Item: P200_ORA_MSG
Point: Before Header
Type: PL/SQL Expression
Computation: REPLACE(:p200_ora_msg,:p200_escape,':');

Create Dynamic Action:
(select Advanced)
Name: Show Error Message Modal
Event: Page Load
Action: Simple Modal - Show
- Esc Close: No
- Change Opacity and Background Color as desired
Select Type: Region
- Region: Unknown Error

Create Page Process:
Type: PL/SQL
Name: Log Error
Point: On Load - Before Header
Source:

1 2 3 4 5 6 7 8 9 10 11 12

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;

- Change Error Template
Go to: Shared Components > Templates
Select the default Page Template (for my demo mine was: One Level Tabs - Right Sidebar (optional / table-based)
Error Page Template:

1 2 3 4 5 6 7 8 9

<script type="text/javascript">   $('body').hide();   var apexEscape = '*@*'; //Used to replace semi colons in the value so does not invalidate URL   var oraMsg = $('.ErrorPageMessage').html().replace(/:/g,apexEscape);   var apexMsg = '#MESSAGE#'.replace(/:/g,apexEscape);   window.location.href='f?p=&APP_ID.:200:&APP_SESSION.::&DEBUG.:200:P200_PAGE_ID,P200_ESCAPE,P200_ORA_MSG,P200_APEX_MSG:&APP_PAGE_ID.,' + apexEscape + ',\\' + oraMsg + '\\,\\' + apexMsg + '\\'; // Add backslashes to avoid comma issue </script>

- End Result
When you have an unhandled exception the end users should see a message like:


You can view all the log information by running the following query:

1 2 3 4 5

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

APEX IR: Column Help

I was working with an Interactive Report (IR) in APEX 4.0 and noticed that when the column header is clicked there's a Column Information button:


When clicked it displays the column help:


At first I thought this was a new APEX 4.0 feature and then I tested it on a 3.2 instance and noticed that the column information button was present as well.

Either way, it's a really nice feature to have since you can provide column information to users.

The following query helps to identify which IR columns have the default "No help available for this page item." message. You may want to change this since it says "... page item." rather than "column"

1 2 3 4 5 6 7 8 9 10 11

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

APEX Spring Cleaning

Each time you do a major upgrade APEX creates a new schema. It does not remove the older schemas, which allows you to roll back to previous versions in case something happens.

I was upgrading some older instances of APEX and realized that I still had some of these older schemas lying around and decide that it was time to do some spring cleaning (I realize that isn't exactly spring time, unless you live in Australia).

From the APEX installation guide (http://download.oracle.com/docs/cd/E17556_01/doc/install.40/e15513/otn_install.htm#CBHBABCC) here's how to identify and remove older versions of APEX.

Please read the documentation and understand what exactly you're doing before you do this!

- Identify old APEX schemas

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

- Remove old schemas
Connect as SYS using the SYSDBA role then run:

1 2 3

DROP USER FLOWS_030000 CASCADE; -- Where "FLOWS_030000" is the username from the previous query

JavaScript Console Logger

Note: This now has a new name, Console Wrapper, and has been moved to a Google project. Please go to http://www.talkapex.com/2011/01/console-wrapper-previously-js-logger.html for more information.

If you've been developing APEX applications for while, or any web applications for that matter, you'll eventually leverage the browser console. If you've used Firebug, then you've had the console accessible to you.

In case you're not sure what console is, it allows you to display debug information in a nice console window within your browsers. Most (i.e. all browsers but IE) are console enabled. The most common use of console is the console.log command:

1	console.log('hello world');

Console has a lot of great features. One drawback with it is that you have to remove calls to console before putting your code into production since not all browsers support console.

Removing instrumentation code before going into production can be annoying, especially if you need to debug it later on. To resolve this issue I've created a console wrapper. This allows you to leave your debugging calls in production code. Here are some features:

• Works in all browsers. If run in IE nothing will happen, but no error message.


• Allows you to set Levels. By default no messages will appear.


• Support for jQuery chaining (see examples).


• Will automatically set level to "log" if run in APEX and APEX is in Debug mode.

A copy of the console wrapper is available here: Download $logger_1.0.0.

The only change that you'll need to make is call $.console instead of console. The download file includes a HTML file with demos. I'd recommend looking at the .js file as well for inline documentation.

Here's an example along with its output:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

$.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');

For more information about console please read the following articles:

- Console APIs: http://getfirebug.com/wiki/index.php/Console_API
- Firebug console example: http://getfirebug.com/logging
- Console tutorial: http://www.tuttoaster.com/learning-javascript-and-dom-with-console/

APEX Plugin: Simple Modal

I just published another plugin called Simple Modal: http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/simple-modal.html

This plugin allows you to use any region(s) (or DOM object) in your APEX application as a modal window.

When developing this plugin I learned a few more things that may be useful when developing plugins:

- Scope Creep: When developing a plugin you can make it do a lot of things. This may lead you to try and include extra unnecessary functionality. Try to remember the goal you're trying to achieve, or more importantly, what you're developers will try to achieve with the plugin.

- Instrumentation: I'm a big fan of Tom Kyte and really like the emphasis he puts on Code Instrumentation. You may want to add some debug information in your JavaScript code to help you understand what is going on. As part of this plugin I included a logger package which is essentially a wrapper for console but will work in all browsers. I'll write about this in another post and include the final copy for general use.

APEX 4.0 Interactive Reports - Customize Wait Display

Over a year ago I wrote about how to customize the APEX IR wait logo (http://www.talkapex.com/2009/04/apex-interactive-reports-customize-wait_28.html). If you read that post you'll notice it's quite lengthy and can be intimidating if you're new to JavaScript.

With APEX 4.0 this is a lot easier to do since they're plugins to declaratively add this functionality. This post will go over how to customize the APEX IR Wait logo in APEX 4.0. You can try a demo here: http://apex.oracle.com/pls/apex/f?p=20195:3200

- Create an IR report region

1 2 3 4 5

SELECT e.*, SUM (e.sal) OVER () test       FROM emp e CONNECT BY LEVEL <= 5

- Install Plugin

- Download the Simple Modal plugin: http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/simple-modal.html
- Shared Components / Plug-ins / Import
- They're 2 plugins included in the zip file. Import both of them (Show and Close)

- Create Show Dynamic Action

- RClick on the IR region and click "Create Dynamic Action:
- Advanced
- Name: Show IR Wait
- Next
- Event: Before Refresh
- Selection Type: DOM Object
- DOM Object: apexir_WORKSHEET_REGION
- Note: We're using the DOM object and not the region since we can port this example to Page 0 and it will apply to all your IRs
- Next
- Action: Select Simple Modal - Show
- You can modify some of the plugin attributes here if you'd like
- Next
- Selection Type: DOM Object
- DOM Object: apexir_LOADER
- Create

- Create Close Dynamic Action

- RClick on the IR region and click "Create Dynamic Action:
- Advanced
- Name: Close IR Wait
- Event: After Refresh
- Selection Type: DOM Object
- DOM Object: apexir_WORKSHEET_REGION
- Next
- Action: Select Simple Modal - Close
- Create

Now when you run the IR it'll make the screen modal while it's reloading the data. If you want to run on all IRs then you can add this dynamic action to Page 0.

If you run the demo in a console-enabled browser (Firefox, Chrome, Safari) you'll notice that the plugin includes some additional debug information. I'll be posting the logging JavaScript package that was used in the plugin soon.

APEX Plugin: Debug Mode and Console

When running an APEX application in debug mode, it will provide debug information in the console regarding any plugins that get executed. Note: Console is supported by most browsers

Here's an example of the output:

APEX Plugin: Javascript Attributes

Recently I was developing an APEX dynamic action plugin and ran into a bit of an issue. One of the custom attributes that I defined was a yes/no attribute which returned "true" or "false" respectively.

When I was testing my plugin it always behaved as though the user had selected yes/true even though they selected no/false. After some debugging I saw that the plugin values in Javascript were strings. Clearly strings are not booleans and they behave differently when evaluated.

To resolve this problem I just changed my if statement:

1 2 3 4 5 6 7 8 9

//From if (this.action.attribute01)   //do something     //To if (this.action.attribute01.toLowerCase() == 'true')   //do something

If you're new to JavaScript, here's how JavaScript handles strings when doing comparisons:

1 2 3 4 5 6 7 8 9 10

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

Here are the results:


As you can see a string returns true if it contains some data.

Validation Messages

When you have an apostrophe (') in your last name you soon realize that a lot of sites don't handle them properly when entering your information. Normally I get a simple error or my last name will be converted to "D" or "Souza". Today I tried to register for a demo for a fairly popular application and received the following error message:


The error message says "Last Name contains errors". Someone clearly didn't do their job when creating this registration form. Besides the fact that I couldn't register with my last name, stating that my last name "contains errors" doesn't really make sense.

I know this post isn't specific to APEX but this is just a reminder when defining validations to use meaningful error messages.

ODTUG: Enhanced APEX Security

At ODTUG Kaleidoscope I promised that I would post my slides from my presentation. Here is a copy of the Enhanced APEX Security presentation.

The two demo's can be found here:
http://www.talkapex.com/2009/05/enhancing-apex-security.html
http://www.talkapex.com/2010/07/poor-mans-vpd-in-apex-and-oracle-xe.html

I hope to post the slides from the other presentation, How to be Creative, sometime next week.

Poor Man's VPD in APEX and Oracle XE

At this year's ODTUG Kaleidoscope I gave a presentation called "Enhancing APEX Security". A copy of the presentation can be downloaded here.

As part of the presentation I discussed how to create a "Poor Man's" VPD using Oracle XE. The main concept was to simulate basic VPD on a non Enterprise Edition (EE) (VPD is only available on EE). This post will cover how to do this. Please note, since this is for demonstration purposes I have kept things very simple and it is by no means a complete solution.

Before you can review the code, we need to discuss some of the basic architecture and technology that will be used. I strongly encourage you to do some additional research on these topics if you plan to use this method in production.

Schema Setup

Assuming you don't have Oracle EE, you'll need a way to secure your existing schema. Lets say you had a schema called "DEMO". You'll need to create a new schema called "DEMO_PUB". The DEMO_PUB schema will not contain any objects. Instead, it will have synonyms which point to views and packages in the DEMO schema. Note, the DEMO_PUB schema will not have any access to the DEMO tables. All DML statements will be made via packages and procedures. The views from the DEMO schema will be "secure views" which will restrict access to the data. On the flip-side the DEMO schema will only grant SELECT and EXECUTE to views and packages respectively, to the DEMO_PUB schema.

You APEX applications should use the DEMO_PUB so that security logic is stored in the database rather than the front-end. This should help prevent developers from displaying data that end users don't have access to.

The following diagram, taken from my presentation, highlights the overall schema structure.


Contexts

For people unfamiliar with Contexts, the easiest way to describe them is a globally accessible container of name/value pairs. The container is only accessible if you have the correct key. Oracle has some great reference material on this (search for VPD or FGAC) so I won't cover this any further Here's a diagram to illustrate Contexts.


The key to "Poor Man's" VPD is to leverage context values in your views to restrict the data. To demonstrate this in pseudo code, if you wanted to restrict access on the EMP table to only employees in your department you'd write a view like this:

1 2 3 4 5

CREATE OR REPLACE VIEW vemp AS SELECT * FROM emp WHERE deptno = some_value_from_a_context

The Code

Hopefully you understood the small bit of background information I wrote before. Here's how to implement a very simple "VPD" enabled application

1 2 3 4 5 6 7

-- 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;

Create pkg_vpd spec

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

-- 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; /

Create pkg_vpd body

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94

-- 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 view which leverages the context

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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 /

Create Custom Authentication Scheme

Shared Components / Authentication Schemes
Create
From Scratch
Name: VPD Demo
Page Sentry Function:
Session Verification Function:
Invalid Session Target: Page in This Application - 101 Login
Pre-Authentication Process:
Credentials Verification Method: Use my custom function to authenticate: return pkg_vpd.f_login
Post-Authentication Process: pkg_vpd.sp_apex_post_auth;
Cookies:
Logout URL: wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:1

Change current Authentication Scheme to "VPD Demo". This will vary between APEX 3.x and APEX 4.0

Create a Region Report with:

1 2 3 4

SELECT * FROM vemp

Now login to your application with "KING/KING". Notice how you only see 3 rows in the report? Now logout and login with "MARTIN/MARTIN". You should now see 6 rows returned. As you can see, none of security was handled in the front end.

APEX Plugin: Syntax Highlighter

I finally got around to developing my first APEX Plugin!

To summarize this plugin is a code syntax highlighter based on http://alexgorbatchev.com/SyntaxHighlighter/.
. This is the same syntax highlighter for code samples on this site. You can download it here: http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/syntax-highlighter.html.


Since this was my first APEX plugin that I created I thought I'd give some pointers to help others writing their first plugin:

- Decide on a feature you want to add. This can be harder said than done. If you're new to JavaScript etc, then you may want to create an example in a html file so that you can reference it later on.

- Keep it simple. Chose a simple feature to add. If you decide to add something very complex you may just get lost.

- Put your PL/SQL code in a package/function so that you can develop using a 3rd party tool such as Toad or SQL Developer. This will allow you to quickly debug your code. Once it's working you can extract it and put it inline with your plugin

- Look at other examples. View other plugins and see how they were built etc.

How do they do it?

Have you ever seen something in the APEX Developer application and wonder how the APEX team built it? Since APEX is built in APEX you can actually see the code for the development environment. The way to do this is to install APEX in APEX. When you download APEX you also get all the development/builder application files:



Simply install each of the applications into a workspace:



Note: Since some of the builder files are extremely large it does take a while to install and you may get a timeout error.

Missing Views from the APEX_DICTIONARY

A long time ago I wrote about how you can list all the apex views by querying APEX_DICTIONARY. They're some views that are still public but are not listed in the Apex Dictionary. The following query lists these views

1 2 3 4 5 6 7 8 9

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

APEX Report Download Logger

This solution was demoed at the ODTUG APEX Open Mic night

I developed a reporting application in APEX. The goal of the application is to help turn "data into information". Users should be able to make business decisions based on the reports and charts rather than exporting the report data and using spreadsheets to analyze it.

I've been a bit concerned that the application isn't achieving it's initial goal and that the reports are just used as data dumps. To validate this hypothesis I needed a way to track how many times a report was downloaded and compare it to the number of times it was viewed.

I can find out how many times a report/page was displayed by looking at the APEX logs:

1 2 3 4 5 6

SELECT *   FROM apex_workspace_activity_log  WHERE application_id = :app_id    AND page_id = :app_page_id

Note: this approach is not 100% accurate since it only logs the page and not the individual regions, however for this purpose it should do. I have tinkered with "Region Logging" and may write about it soon.

I still needed a way to track report downloads. Here's how I did it. A working demo can be found here: http://apex.oracle.com/pls/apex/f?p=20195:3120

- Create Download Log table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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

- Create procedure to store APEX download record

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

/**  * 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;

- Trigger the above procedure every time a report is downloaded:

Since Standard Reports (STD) and Interactive Reports (IR) download functionalities are handled differently I can't use an Applicatiobn Process to trigger the logging function. Instead, I'm going to leverage the VPD section in APEX. For those of you new to APEX, the Virtual Private Database (VPD) section in APEX is a section in APEX where you can put a block of PL/SQL code. This code gets run right at the beginning of the page, after the APP_USER is defined. The label of VPD is a bit misleading since the code doesn't have to do any VPD tasks. Initially I had planned to handle IRs and STD Reports using a different method but thanks to the guys at Purdue Pharma for reminding me that I can leverage the VPD section in APEX.

- Shared Components
- Security Attributes
- Virtual Private Database

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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;

- Comparison report:

Here's the query that I used to compare the downloads to the report views

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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(+)

How to Find Current APEX Version

Last week my DBA asked me what version of APEX we were running. Instinctively I loaded up the APEX development page and looked in the bottom right hand corner:



This worked, however he needed to dynamically obtain the current version for a script that he was writing. After some digging around he sent me the following query:

1 2 3 4

SELECT * FROM apex_release

Note: this isn't earth shattering but more for my reference

Conditional Validations in APEX

There may be some instances when you have validations on your APEX page that you only want to run if all the other validations have passed. A good example of this is if you have 2 fields, FROM_DATE and TO_DATE and have the following validations:
- V1: Ensure FROM_DATE is not null and is a valid date
- V2: Ensure TO_DATE is not null and is a valid date
- V3: Ensure FROM_DATE < TO_DATE

In this example you only want the last validation to run if the first 2 pass (i.e. both FROM_DATE and TO_DATE are valid dates). Currently there's no declarative way of doing this. i.e. validations don't support declarative dependencies.

A simple trick to get around this issue is to put a condition in some validations to only run if all the other validations pass. This may not work in all situations, however it should help you in some.

In the example about, modify the 3rd validation (V3) and set the condition to the following:
Condition Type: PL/SQL Expression
Expression 1: apex_application.g_inline_validation_error_cnt = 0

Now V3 will only run if all previous validations pass.

Special columns for APEX Standard Reports

I was testing APEX 4.0 on http://apex.oracle.com and I noticed that on Standard Reports you can add 2 types of "special" columns: Derived Column and Column Link. At first I thought this was a new APEX 4.0 feature, but then tested on a copy of 3.x noticed it was there as well! I apologize if this is old news to people but I found it to be a pretty neat feature.

The interesting thing about these columns is that you can add Derived Column and Column Link columns and it does not modify your existing SQL.

The only difference I can tell about these 2 column types is that Derived Columns don't contain the following attributes: List of Values, Tabular Form Attributes, and Column Link

How to only Display Column when Downloading

Sometimes you may have a column that contains inline HTML. A simple example of this is if you have a column called "color" and you want to display the color in the report. Your query would look like this:

1 2 3 4

SELECT '<span style="color:' || color || '">"' || color || '</span>' color FROM my_colors

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

If you were to download this report the "color" column would contain all the html (i.e. span tags etc). This may confuse users since they expected to see "red, green, blue, etc..." in their download file, but instead see the colors wrapped in a lot of html.

A workaround that I've used is to create 2 columns: color_html and color

1 2 3 4 5

SELECT '<span style="color:' || color || '">"' || color || '</span>' color_html,             color color   FROM my_colors

And modify each report column's attributes:

Standard Reports

color_html
Column Definition:
Include In Export: No

color
Conditional Display:
PL/SQL Function Body Returning a Boolean: return apex_application.g_excel_format

Interactive Reports

color_html
Conditional Display:
Request is NOT Contained within Expression 1: CSV,HTMLD

color
Conditional Display:
Request is Contained within Expression 1: CSV,HTMLD

Now when a user downloads the report they'll get the non-html version of the column. This solution works in APEX 4.0 and APEX 3.x

Interactive Reports with over 100 Columns

I ran into an issue last week where I had an Interactive Report (IR) with over 100 columns. All 100 plus columns displayed for end users, however I was only able to modify the first 100 columns. For example, the 101st column did not display in the report attributes screen (see screen shot below)



After some digging around I figured out how to modify columns that weren't displayed on the Report Attributes screen. This demo requires Firefox and Firebug. You should also be able to do this in Google Chrome. This demo works for APEX 3.x and APEX 4.0.

Build Large IR:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

Get the COLUMN_ID

This is the column that you want to modify that isn't currently displayed on the Reports Attributes page

1 2 3 4 5 6 7 8 9

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

Run

Go to the Reports Attributes screen (the screen that lists all the IR columns)

Open Firebug and go to the Console Window. Enter the following:

1 2 3

apex_p601_setColumnIDandSubmit('311507131164520006'); // Enter your column ID here