Sunday, June 17, 2007

ODTUG Kalidescope Conference

I attended the Advanced APEXposed 2007 training and the ODTUG Kaleidoscope conference. Both the beginning and advanced Oracle Application Express (ApEx) training were sold out.
On the conference web page there is a listing of all the conference’s technical sessions. I copies of these papers on CD if you are interested.
For each training or technical session I attended, I have listed below the abstract of the session along with what we did or special things of note from the session.

ODTUG ApExposed (Advanced Oracle Application Express Training)

Welcome & Keynote
Mike Hichwa, Oracle Corporation

Hands-On Ajax and JavaScript
Scott Spendolini, Sumner Technologies, LLC

Web applications are quickly maturing. More client/server-like functionality is appearing on the Web each day, making sites easier to use and navigate. APEX itself included several libraries which make adding these types of features much easier. This session will outline when and when not to use Ajax and JavaScript in your APEX applications. It will provide an overview of the built-in APEX JavaScript libraries. You will build a number of different Ajax and JavaScript components, from instantaneous validations to dynamic select lists, and learn how to deploy them in your own APEX applications.
• Added “is required” validation to an application so the user does not have to submit the whole page to the server just to get a “field … is required” error message. The user get the error message as they attempt to exit the field.
• Added automatic uppercasing of fields which fires when the user moves out of a field.
• Added hiding and showing of page items based on the value of other items on the page.
• Added hiding and showing of a field and its label.
• Added “check all” option for checkboxes in a multiple row region.
• Added a pop-up window.
• Added a confirm message (OK or Cancel).
• Added an on-demand process so when a value is changed on the page its description is automatically updated without requiring the user to submit the page to the server.
• Added an on-demand process so when a value is changed on the page a select list’s list of values is automatically updated without requiring the user to submit the page to the server.
• Added logic to refresh a report region without having to rerender the whole page.
• NOTE: User ApEx validation processes in connection with Javascript validations because users can turn Javascript off in their browser.


UI and Design
Scott Spendolini, Sumner Technologies, LLC

Most developers are not graphic designers, and most graphic designers are not developers. And with good reason! Despite this, the expectations to create a well-designed site often lie squarely on the developer’s shoulders. This session will demonstrate the entire process of replicating an existing user interface in APEX. It will discuss the methodology used when creating the templates, discuss the different tools available, and cover Section 508 concerns.
• To create a new theme start with a copy of the built-in theme #4.
• Use subscriptions when standardizing themes across applications.
• Do not load theme images in the /i/ directory. Create your own virtual directory off of the /i/ directory.
• Use TABINDEX to control proper ordering of tabbing from item to item in a page.


Security
Raj Mattamal, Sumner Technologies, LLC

As important as it is, security is almost always added to an application as a last step, if at all. Designing your APEX application with security in mind is critical and should not be overlooked. This session will discuss how to build secure APEX applications from the ground up so that a hacker cannot exploit them. Time will also be spent on discussing how to segment functionality inside of an APEX application so that only authorized users can perform specific tasks. It will examine what can be done to secure the underlying data of an APEX application. Finally, it will outline a list of things that need to be secured in an installation of APEX, as well as the associated infrastructure components.
• For testing use “Open Door” authentication. It allows you to login and any user ID.
• Turn on session state protection.
• Check for SQL Injection on regions which refer to search field(s) or regions based on a SELECT statement generated by PL/SQL.
• Check for what I call “Javascript Injection”. User enters Javascript into a data entry field and submits the page. When the page is rendered, the Javascript he entered is functional. To counter this in PL/SQL there is the UTL_URL.ESCAPE function or the Javascript escape and encode functions.

High Fidelity Reporting: Hands-On PL/PDF
Josh Millinger, Sumner Technologies, LLC

With APEX 3.0, creating PDF reports will be as simple as just a couple of clicks. However, there are some limitations to the mechanism used in APEX 3.0 which will limit what it can be used for. PL/PDF, a third-party product, is a PL/SQL-based solution that enables you to generate PDF documents from the Oracle Database. PL/PDF, like APEX, is PL/SQL based, does not require any additional hardware, and is extremely flexible. This hands-on session will outline when to use PL/PDF vs. the built-in PDF engine in APEX 3.0. It will also provide a quick overview on how to install PL/PDF, and will then walk you through the creation and deployment of a PL/PDF report integrated with APEX.
• PDF reports are created in PL/SQL procedures.
• There is no visual editor of the report.
• Can start with PDF and overlay information on it for filling in printable forms.
• PL/PDF costs $500 per database

Managing APEX
Joel Kallman, Oracle Corporation

You’ve installed APEX at your organization, people have started to build and deploy applications with it, and before you know it, you have another mission-critical component that needs to be managed. In this session, the Oracle APEX team will discuss the architecture of APEX, how it was designed with scalability in mind, and how to use features of the database and APEX itself to assist in managing it. It will also discuss how to manage the APEX e-mail queue, identify and enhance the performance of slow queries, and prevent poorly written ones from degrading the performance of your system.

APEX APIs
Raj Mattamal, Sumner Technologies, LLC

The APIs provided with APEX are quite powerful. They extend the functionality of APEX so that it can be integrated into named PL/SQL procedures and functions, create data-driven dynamic forms, allow for custom authentication functions, and integrate with LDAP, to name a few. This session will examine the most popular features of the APEX APIs and provide working examples to illustrate how to invoke them. It will also discuss the benefits of and when to use data-driven forms in your applications.

2007 ODTUG Kaleidoscope Conference Presentations I Attended

Database Tasks with Oracle SQL Developer
Sue Harper, Oracle Corporation

Oracle SQL Developer provides the database developer with a convenient way to perform basic database tasks. Connected to an Oracle database, users can browse, create, edit, and delete database objects; create, edit, and debug SQL and PL/SQL code; manipulate and export data; and create reports. This demonstration-rich session briefly positions the tool, and then quickly walks through a series of activities that a user might do in any day-to-day activity; from creating a connection, to creating basic objects and then writing and debugging PL/SQL code. With particular focus on new features in SQL Developer 1.1, this talk will also address more complex aspects, such as extending the tool, and a few plans for the future.


APEX and Project Management
Patrick Cimolini, Cayman Islands Government

Application Express (APEX) is a robust and productive development tool, however, even APEX projects can be late, over budget, and of dubious quality if they are not well managed. Looking at APEX from a project manager's perspective, the following questions are addressed: How long will a project take? What tasks needs to be done? How long will a single task take? When should the tasks be done and in what order? Who performs the tasks? What skills are required? How are source code and versions managed? What quality can be promised and delivered to the customer? Quantitative metrics and a production application will be used to illustrate the points.


Polishing Below the Surface with Ajax
David Schleis, Wisconsin State Laboratory of Hygiene

It has been suggested by the world’s premier Oracle development users group that decisions concerning hardware and operating system are secondary, and now “it’s all about the web.” The technology that allows for the creation of sophisticated Web-based applications that behave like desktop applications is Ajax, or Asynchronous JavaScript and XML. Ajax allows for some very flashy user interface features to be included on a Web page, but it can also be used to perform less glamorous duties. This presentation provides an introduction to Ajax, including a brief overview of its component technologies, as well as examples of under-the-covers functionality that can be designed into new pages or used to enhance existing applications.


Getting XML Data into and out of Oracle with PL/SQL
Ken Atkins, Creative Design Associates

XML is very powerful as a method of transporting data between different applications or databases. Because of this, PL/SQL developers are often faced with the task of creating XML documents from data in normalized Oracle tables, or of storing the data in XML documents back into normalized Oracle tables. This session will present various tools and techniques that are available to the PL/SQL developer to read, write, and manipulate XML documents with the express purpose of getting the XML information into and out of standard Oracle tables.


Flex Your APEX: Implementing Oracle E-Business Suite Flexfields in Application Express
Shane Bentz, InterVarsity Christian Fellowship/USA

With every passing day, the functional boundaries between traditional client-server applications and Web applications decrease. New technologies such as SOAP and Ajax enable Web applications to function and perform like traditional fat-client software. In this session, I will show how to integrate the E-Business Suite flexfield functionality into Application Express. By leveraging the power of Ajax, Application Express can use descriptive flexfields to create rich Web applications—applications that can change shape and purpose on-the-fly, enhancing the user experience by not reloading the page and minimizing future coding by developers.


APEX by Example: Shared Components
Dimitri Gielis, Ordina Belgium

Oracle Application Express (APEX) is a quickly growing development environment. The initial goal of APEX was to have something 'simple', a wizard-driven development environment to compete with MS Access or to replace Excel files. People still use the wizard a lot, but if you would really like to use the force of APEX, you can use Shared Components. This session covers all available Shared Components; it tells you what it is for, why to use it, and how to use it in a real environment with the "DG Tournament" application.


Advanced Database Development with Oracle SQL Developer
Sue Harper, Oracle Corporation

SQL Developer provides a convenient way to perform basic database tasks. Here we look at some of the activities you might perform, running complex scripts, normalizing data, and remote debugging. Advanced reporting capabilities include graphical displays and master-detail reports. We demonstrate a few existing extensions to SQL Developer and show you how to build and include your own extensions.


Advanced Application Express Programming Techniques
Michael Hichwa, Oracle Corporation

Learn Michael Hichwa's perspective on how to develop Application Express (APEX) applications from data model to user interface. Get recommendations for source code management, upgrades, and customizations. See how to exploit new Oracle APEX 3.0 features. See techniques to improve and test performance.


Building Interactive Web Reports with Application Express
Jeff Hohman, Oracle Corporation

This presentation will show the reporting abilities of Applications Express, will demonstrate how to have multiple SQL statements displayed on one page going to different data sources, and will demonstrate the drilling and manner of which variables can be passed around to other reports. It will also show other Applications Express components (ex. Graphs, Forms, etc.) integrated into the same dashboard.


APEX Lessons from the Trenches
Bill Holtzman, National Air Traffic Controllers Association

Application Express enables rapid and efficient development of secure and enterprise-level Web applications. Like any platform, there are numerous tricks of the trade that one learns only after getting waist-deep in the coding. The National Air Traffic Controllers Association (NATCA) has been digging successfully in the APEX gold mine for three years and has a wide assortment of coding techniques to share with those undertaking or considering expanding their use of APEX.


Managing Large Application Development Using Oracle Application Express
David Peake, Oracle Corporation

This presentation will outline developing large applications using Oracle Application Express (APEX). It will be based on personal experience managing a diverse team of developers and personally developing APEX Applications on a comprehensive system with close to 1000 individual applications. Development on this application started over four years ago and has successfully been implemented as the primary enterprise-wide OLTP system for a large law enforcement agency with thousands of users.

Tuesday, June 12, 2007

A GUI intensive ApEx Application

This blog will start out as a laundry list of GUI-type issues that I run into while developing a very GUI intensive. My initial impression is that I should be able to address these issues with either normal HTML DB page design or JavaScript. We are running HTML DB version 2. My background in screen development is Oracle Forms in which one can develop very “client-heavy” GUI applications.

I will briefly describe the application. I cannot specify exactly what type of data the application is based on, but I can describe the application at a high enough level that the topics can apply to many other sets of data.

The application is basically a “user reporting criteria gathering” application. The user assembles a study which is a collection of one or more types of reports, specifies the criteria for the reports, and clicks the run button. The user then receives one or more emails with the reports in Excel format as attachments. The HTML DB application handles the study definitions, report criteria, and run button. The data gathering is done as a DMBS_JOB in PL/SQL. It takes data from the large source tables, summarizes it, and saves it to results tables. An Excel process is alerted. Excel places the summarized data into a worksheet, attaches the worksheet to an email, and sends the email to the requester. This is the second application I have done using this same design. I worked on another application a couple years ago that was based on Oracle Forms.

Issue 1: A Generic User Prompter

As part of the application the user will have the ability to copy a study or a report in a study. So the screen will have to prompt the user for the new name of the study or report. I decided to utilize the JavaScript prompt method to prompt the user. I created a function that you can pass prompt text to display and the name of the page item that should receive the user's response as well as a default value.

function prompter(p_prompt, p_destination, p_default) {
var reply = prompt(p_prompt, p_default);
if (reply != ' ' || reply != null) {
html_GetElement(p_destination).value = reply;
}
else {
html_GetElement(p_destination).value = null;
}
}


Issue 2: Cascading Select Lists in a From Region

There are two Select Lists in a form (data entry page) region. The values in the second Select List are dynamically based on the value that the user selects in the first Select List. The first Select List is the type of report the user wants. The second Select List is the format in which the report should be rendered. You could have two rendering formats for the same report. One format could be a graphical chart. The other could be just a numerical dump of the detail data.

I did not want to force a page submit to populate the second Select List because it would fire the Automatic Row Processing which would attempt to commit to the database. I tried playing with the request values upon submit of the page, but could not get that to work. So after playing around with a couple other options I decided to implement an AJAX JavaScript solution provided by Carl Backstrom. It is pretty slick.


Issue 3: Cascading Select Lists in a Tabular From Region

This is a little harder because you have multiple Select Lists in the region. In the development process I ended coding this region 4 different ways before deciding on one way. I used both wizard generated tabular form regions and manually created form regions. In trying to implement an AJAX solution I read a method that Vikas demonstrates of being able to access particular items in particular rows in a tabular form. I was able to get AJAX solution to work, but I decided to “silently” submit the page for the user once they made a selection in the first Select List. The rendering of the page then populated the dependent Select List with the appropriate values.


Issue 4: Show Parent (Study) in a Tree of Reports

I wanted to display the structure of the study and its reports in one tree structure. I was easily able to display a tree of parent and child reports, but wanted the root of the tree to be the study name. So I created a view which UNIONed in the study name as the root of the tree and all the parent reports and their children as children of the root. The tree item in the page region then listed the study as the root with the reports underneath it like:

Image and video hosting by TinyPic

Issue 5: Allow User to Save Partial Studies

We needed the ability to allow the users to partially save a study. In other words allow the user to define part of a study and at a later time come back and edit the report. I used normal wizard generated HTML DB screens. There was nothing difficult here. I allowed screens to save the data to the database. The tricky part was not allowing the user to run partially entered study. Also we needed a way to communicate to the user what parts of the study need to be fixed or corrected. I handled this with a Study Validater function and a Report Validater function.

At the top of the “edit study” page I put a region that displays which reports in the study the user needs to correct or do something to. This region calls the Study Validator passing in the study ID of the study the user is editing. The function returns a table of records. Each record indicates which report in the study the user needs to modify.
We created a table type which allows us to display the rows returned by the function. So our region SELECT statement was as simple as:

SELECT ROW_NUMBER() OVER (ORDER BY NULL) task, text description
FROM TABLE(check_study(:P3_STUDY_ID))

See technical details

The run study button calls the Study Validater. If the validater returns no rows, then the study is submitted to be run. If the validator returns rows then a message pops up indicating that the user has report modifications to make.

Similarly I have a Report Validator. It provides the details of what the user has to do for a given report before they can submit the study to run.


Issue 6: Checkbox in a Tabular Form

The tabular form was wizard generated. I wanted the last item in each row of data to be a checkbox resulting in a “Y” for yes or an “N” for no to be inserted into the column in the database. I could have done this as a checkbox and coded an extra page process to assign the column’s value to each row in the database, but I decided to use a Select List based on a Yes/No list of values. It is not as fancy or flashy as a checkbox, but I was able to leave all the inserts, updates, and deletes to the wizard generated code.

I will add more as I encounter them.

Saturday, June 09, 2007

Blogging Topics

I have two Oracle-related topics tbal I decided to blog about.
1) I will be attending the ODTUG Kalidescope conference this year in Daytona, FL. I will also be attending the Advanced ApEx training.
2) I am about to start 5th Oracle Application Express (ApEx) application. Being the 5th is not significant. Being very GUI itensive is. There is going to be more client side functionality then any Apex I have done so far. Being from an Orale Forms background I tbought it would cool to do a "This is how I would do it in Forms and how I did it in ApEx" blog.