Wednesday, September 29, 2010

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:

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

Thursday, September 16, 2010

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.

Wednesday, September 15, 2010

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:

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.

Tuesday, September 14, 2010

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.

Sunday, September 12, 2010

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:


Standardized Help:

APEX Logs:

Custom Error Messages:

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:

- Install Simple Modal Plug-in
Install the Simple Modal plugin into your APEX application: 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.

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

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,
v_db_name VARCHAR2 (30);
v_schema VARCHAR2 (30);
v_scope VARCHAR2 (255);
-- 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
-- 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
-- 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)
-- Include all Application Items
SELECT item_name, v (item_name)
FROM apex_application_items
WHERE application_id = p_application_id)
logger.log_information (x.item_name || ': ' || x.item_val, v_scope);

-- Email Notification
-- Send Mail

apex_mail.send (p_to => p_email,
p_from => '', -- 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 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:

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

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

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

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

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

Thursday, September 9, 2010

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"

SELECT ap.page_id,
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