AOP Text Formatting

Introduction

While I love Oracle Application Express, I have experienced frustration with generating stunning printed material. APEX Office Print (AOP) has simultaneously expanded our team’s capabilities while reducing our effort. That’s a tool! Rather, that is the definition of a great tool. Our ancestors used antlers to dig in the dirt, then someone came along with the steel spade. I feel the same way about AOP. In a previous entry, we touched on the structure of the SQL needed to prepare the data for AOP. Three months later, I still refer back to this blog for quick answers and structures. In this blog, we will explore AOP Text Formatting.

In the recent months, our team has:

  1. Created data-rich reports with grouping, sub-totals, running totals all formatted in MS Excel and handed back as PDF
  2. Developed text-rich reports that generate glorious MS Word documents — Easy and affordable database driven Word documents
  3. Added both conditional formatting and conditional data to reports

Context

Edit AOP Report

For one of our commercial applications (Tempest-Bid), we create proposals. Customers use our software to respond to government requests for proposals. These are highly structured documents with hundreds (thousands) of building blocks, often involve multiple technical writers, dozens of resumes, and an output that must be perfect. For the purpose of this example and blog, we are simplifying the proposal down. We use the US Constitution as a sample of the process. And for this blog, we’ll show it being assembled in Oracle APEX and AOP.

Objectives

We are tool-builders. We pride ourselves on great support, but… we really don’t want to have to bill a customer because they want to change a footer, a font, or some other formatting within a report. Let’s give that power back to the user. AOP now let’s us share this with our customers. Want Arial 12? Go for it! Want narrower margins? Help yourself!

Not answers that have been routinely available within APEX.

In this entry, we will focus on

  • Sharing Template management with the customer
  • Linking AOP query with Templates

Setup

Our report menus are data driven (dynamic list). The basics are the source page (menu page) and the destination page (APEX page hosting the report). Incorporating AOP to this process means adding the fields needed to support the AOP plugin. Recently, we added a separate child table for report templates. We can load templates from our menu, identifying templates as “Global” or customers can add their own templates, which are then identified as “Private”.

AOP is also data driven. The data for the AOP report derives from a query. But so does the identification of the template. This means that you can provide a select list of available templates to your users. To get there, we needed a means of managing templates.

AOP Report Template

Open the collapsible region to review, edit, or add new templates to a report.

AOP Report Template List

Workflow

When writing an AOP query, we adopt the same thinking we use on Interactive Reports. What are ALL of the options that the user may want? What fields should we prepare for them? What formatting is required? The challenge for us is to anticipate needs down the road.

And…

And we need to prepare a template with full instructions on it for the user. We call this (in the data and in the image above) “Example”. We put all of the fields with definitions. We put instructions and coaching.

Preparing CLOBs

There is a trick when managing text in CLOBs. The data exchange between Oracle and AOP is done in JSON. The hand off strips the carriage return/line feed that maintains the integrity of paragraphs and lists. The guys at AOP look for a \n in the data, then substitute the CR/LF. It is for us to prepare the data with the \n.

  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"

This replace statement pulls the CR/LF — chr(10) chr(13) — from the data and lays in the \n as required by AOP.

Example

The Output

The following image is a report generated from data within Oracle, managed by APEX and “printed” by AOP into MS Word.

US Constitution

Discussion

While the example shown uses three fields, there are more available for the user on this report. Because we are curious geeks, here is the underlying query:

select
	'file1' as "filename",
  cursor ( select
     cursor (
select
  ,c.client_name                "client"
  ,p.proposal_name              "proposal"
  ,r.name                       "require_name"
  ,r.description                "require_description"
  ,r.wbs_sequence               "wbs_sequence"
  ,r.sequence_order             "require_sequence"
  ,r.proposal_section           "proposal_section"
  ,r.proposal_section_title     "section_title"
  ,sn.SUBMIT_ASSET_FK
  ,(select first_name || ' ' || last_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_first_last"
  ,(select last_name || ', ' || first_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_last_first"
  ,sn.TITLE                       "snippet_title"
  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"
  ,sn.APPROVED                    "approved"
  ,to_char(sn.CREATED_ON,'MM/DD/YYYY')  "created_on"
  ,sn.CREATED_BY                        "created_by"
  ,to_char(sn.UPDATED_ON,'MM/DD/YYYY')  "updated_on"
  ,sn.UPDATED_BY                        "updated_by"
  ,r.outline_sequence                   "outl_seq"
from bid_require_snippet sn
left join bid_client c on
  c.client_pk = sn.client_fk
left join bid_proposal p on
  p.proposal_pk = sn.proposal_fk
left join bid_require r on
  r.require_pk = sn.require_fk
where nvl(sn.archived,'N') <> 'Y'
  and nvl(sn.approved,'N') = 'Y'
  and sn.proposal_fk = :G_PROPOSAL_PK
order by r.outline_sequence, r.proposal_section 
    ) as "d"
 from dual ) as "data"
from dual

Example Template

With users being able to download templates, modify them, upload modifications or new ones, we wanted to provide instructions. The image below illustrates one of these “examples”.

US Constitution

-/qed

Oracle APEX Accessibility

Oracle APEX Accessibility

VPAT, Section 508, WCAG all deal address improving access to software. Oracle APEX has the tools; developers should include the techniques. What do we need to steer closer and closer to the various guidelines that deal with improving Oracle APEX accessibility?

  • Why write this blog in January 2017
  • Let’s look at some of the references, definitions and link to literature.
  • A few quick steps in Oracle APEX

Why write this now?

Oracle APEX 5.1 was released in the recent month. The 5.1 release takes a number of strides towards greater compliance with accessibility laws and guidelines. I was in the process of having to change a series of icons and links. This upgrade gave Storm Petrel the opportunity to improve our posture and position on these standards. I am making the changes anyway. It is all copy/replace anyway. Adding the extra bits is good for the world and cost us nothing.

Links and Lit

The terms and definitions for “accessibility” are captured in a variety of acronyms in the United States. Some reference specific US law.

Term: VPAT

Definition: Voluntary Product Accessibility Template

Link: https://www.state.gov/m/irm/impact/126343.htm

A VPAT is a vendor-generated statement that provides information on how a vendor’s products conform to the Section 508 standards. For software developers who develop applications used by federal, state, or local governmental agencies, compliance is required. Compliance is likely required by all sorts of quasi-governmental organizations too. And compliance is likely required by any customer who is a governmental contractor. Such regulations often flow through contracts.

Term: Section 508

Definition: Section 508 of the Rehabilitation Act of 1973

Link: https://www.section508.gov/content/learn/laws-and-policies

In 1998, Congress amended the Rehabilitation Act of 1973 to require Federal agencies to make their electronic and information technology (EIT) accessible to people with disabilities. The law (29 U.S.C. § 794 (d)) applies to all Federal agencies when they develop, procure, maintain, or use electronic and information technology. Under Section 508, agencies must give disabled employees and members of the public access to information that is comparable to access available to others. The United States Access Board discusses the Section 508 law and its responsibility for developing accessibility standards for EIT to incorporate into regulations that govern Federal procurement practices.

Term: WCAG 2.0

Definition: Web Content Accessibility Guidelines

Link: https://www.w3.org/TR/WCAG20/

Web Content Accessibility Guidelines (WCAG) 2.0 covers a wide range of recommendations for making Web content more accessible. Following these guidelines will make content accessible to a wider range of people with disabilities, including blindness and low vision, deafness and hearing loss, learning disabilities, cognitive limitations, limited movement, speech disabilities, photosensitivity and combinations of these. Following these guidelines will also often make your Web content more usable to users in general.

Term: ADA

Definition: Americans with Disabilities Act

Link: https://www.ada.gov/pubs/ada.htm

Oracle and APEX

Oracle does evaluate APEX for compliance with these laws and publishes a VPAT. The link for the 2015 VPAT statement (which is based on APEX 5.0.1) is here:http://www.oracle.com/us/corporate/accessibility/templates/t2-5214.html

A Few Steps

I love the modern looking links that include icons. On their own, they are not compliant with the rules described above. Screen readers need something to read: “eff-ay-pencil” is not helpful. A reader that says “edit” is a bit more helpful.

Improve your icon links with alt text and even titles.

<i class="fa fa-pencil" alt="Edit" title="Edit"></i>
<i class="fa fa-copy" alt="Copy" title="Copy"></i>
<i class="fa fa-eye" alt="View" title="View"></i>

Screen readers will read the business that follows ‘alt’. A nice short action verb describing the action is more helpful.

Color Contrasts

Within the Theme Roller, there are small check marks next to the color pairs. These check marks tell part of the WCAG story. The check indicates that the color contrast is minimally passing. Click on the Check Mark, and you will get a description of the Color Contrast Information. The more A’s you get the better you are: AAA has better contract than AA.

WCAG Color Contrast Information

Oracle APEX Accessibility

The Universal Theme seems to have AAA ratings. When we, developers, get all fancy, we may go astray.

-/qed

APEX Error Code apex.error.internal request-plugin

APEX_ERROR_CODE – APEX.ERROR.INTERNAL REQUEST – PLUGIN

By Stevie Dickerson, Storm Petrel

Problem Statement

Exporting then importing an Oracle APEX 5.1 application with a newer-style JET report generated an error indicating a problem with a plug in.

Context

Our team at Storm Petrel were excited to implement new APEX 5.1 features into our applications, so once our development server was upgraded to APEX 5.1, we immediately got to work updating our legacy charts to JET charts.

The charts themselves are beautiful and more modern than the previous APEX charts. We were really excited about the change and our production server was the next to be upgraded. Then…a problem happened.

I ran into a problem taking an application from our development server to our production server for early testing. This was the first application that contained APEX 5.1 features, specifically JET charts.

Problem Summary

When exporting then importing an APEX 5.1 application that includes a JET chart, an error message appears that reads:

APEX_ERROR_CODE - APEX.ERROR.INTERNAL REQUEST - PLUGIN=pf3MAeLukWRjbtrA_S-k-QB_0yrxbFWsFZ0XAjyEfQTc12od1TDnAnrwBS2uEmii

The error backtrace from our error log does not give much more information

ERROR_BACKTRACE

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FFF15455870       935  package body APEX_050100.WWV_FLOW_ERROR
00007FFF15455870      1003  package body APEX_050100.WWV_FLOW_ERROR
00007FFF15455870      1395  package body APEX_050100.WWV_FLOW_ERROR
00007FFF15455870      1484  package body APEX_050100.WWV_FLOW_ERROR
00007FFF154E9A48      2122  package body APEX_050100.WWV_FLOW_PLUGIN
00007FFF154E9A48      3004  package body APEX_050100.WWV_FLOW_PLUGIN
00007FFF173BF108      4533  package body APEX_050100.WWV_FLOW
00007FFF173BF108      5951  package body APEX_050100.WWV_FLOW
00007FFF157995A0         2  anonymous block

Problem Confirmation

Mark the JET chart regions as “NEVER”. If you load the page without issues, then you are likely. Problem Workaround/Resolution

After receiving assistance from the awesome staff at Oracle APEX, we found that we were hitting bug #25403748 – plug-in ajax request fail if component id is negative which has already been fixed in the upcoming APEX 5.1.1 patchset.

Until the APEX 5.1.1 release, the workaround to avoid the error message is:

  1. When exporting an application that includes JET charts, make sure that
  2. “Export with Original IDs” = No
  3. Import the application into the target environment like normal, then Voila!

No error messages.

Stevie Dickerson

Synchronize APEX RESTful Services

Now that we depend on RESTful services for generating all of our reports, a new challenge exists when promoting applications from development to production. How do we synchronize APEX RESTful service? This step has become mission critical now that all of our reports are generated with APEX Office Print (AOP).

At Storm Petrel, we run one server for development and one server for production. With SQL Development and other tools, keeping comparing the database is straight forward. When we promote an APEX release to production, we tick through a basic checklist: check packages, check table structures, move APEX app. Not too bad.

APEX RESTful services are not “in the database”, therefore SQL Developer is blind to them.

APEX RESTful services are not “in an application”, therefore exporting an application ignores them.

We need a tool to help us.

It won’t take long to guess, that we will use PL/SQL, APEX, and RESTful services.

Tool Kit

Without going into APEX, how do you find APEX RESTful services? They are in public synonym and views that the team at Oracle provide for us. You can explore this views with three queries:

Select * from apex_rest_resource_handlers;

Select * from apex_rest_resource_modules;

Select * from apex_rest_resource_parameters;

Select * from apex_rest_resource_templates;

Build View

The first exploration involves playing with data, for that we built a view based on this query:

select 
	h.workspace       "workspace", 
	t.module_name     "module_name",
	m.uri_prefix      "uri_prefix",
	h.uri_template    "uri_template",
	h.method          "method",
	h.require_https   "require_https",
	h.source          "source",
	h.last_updated_on "updated",
	h.last_updated_by "updated_by",
	h.created_by      "created_by",
	h.created_on      "created"
from apex_rest_resource_handlers h
left join apex_rest_resource_templates t on
  t.template_id = h.template_id
left join apex_rest_resource_modules m on
  t.module_id = m.module_id
order by h.workspace, t.module_name, m.uri_prefix, h.uri_template;

The data in here mimics what you see in the APEX RESTful services.

Synchronize APEX RESTful Services

Compare the screenshot above with the data below:

APEX RESTful Services PLSQL

Synchronize APEX RESTful Services

With the need to compare data from two servers, it made sense to create a RESTful service based on the same data that shows in the query from above. So that is what we did:

API Query

How to Compare

With a few hours, effort in a PL/SQL package, you can query the RESTful services from two servers. In our case, we called them simply, LEFT and RIGHT. You could use an Oracle JSON table and run queries from there, or just spin through the data that you have. We opted to spin through the data that we fetched from the two URLs (left and right).

We wrote three functions:

select apex_rest_pkg.template_count_match from dual;
select apex_rest_pkg.uri_mismatch from dual;
select apex_rest_pkg.query_mismatch from dual;

In the first case, we know if the number of URI templates match. While not super useful, it is a bit of a guide.

The second query gives us a formatted CLOB that details all of the APEX RESTful services that are missing on the “right” server.

The third query compares the JSON queries within the RESTful services. If there is a mismatch, it tells us the name of the module, URI prefix, and URI template. We always copy the literal text from the development server and paste it to the production server.

APEX Rest Package

Alternatives

There are some fine free JSON comparison tools on line. You can certainly paste your data there.

I do rather wish that the SQL Developer database comparison tool has a stupid tool for comparing the values within a source and destination table. It is out of the scope of the tool, and if it were there someone would try to grind through 100,000 records. We are programmers, so writing code that fetches data, compares it, and react constitute fundamental skills. If you need help or the code, catch me on twitter @cmoore_sp, I’ll ship it to you. I don’t mind.

Next Steps

In Oracle APEX, we can add a push button, display the results in a text block and even email them to the developer.

-/qed

Oracle APEX_APPLICATION Synonyms

Oracle APEX_APPLICATION synonyms are worth exploring. As is often said, APEX is written in APEX. That means means that any APEX developer can undertake using some of the tools that the APEX development teams left for us in this tool kit. Let’s take a look and find a few uses for the Oracle public synonyms that lay at our fingertips.

Public Synonyms

The first step is finding them. With SQL Developer, I use filters on Public Synonyms. In the illustration, I show a filter for NAME LIKE APEX_%

Oracle Public Synonyms

Best Targets

Many (or most) of these synonyms are views pulling data from multiple tables in the APEX home schema. These are relatively safe to explore because a developer can not update tables through these views.

select * from apex_applications;
select * from apex_application_tables;

What are the best targets? Start with familiar and identifiable data such as all applications, then take a look at the pages for applications.

The APEX Team Development data are available here as well:

  • APEX_TEAM_BUGS
  • APEX_TEAM_FEATURES
  • APEX_TEAM_FEEDBACK
  • APEX_TEAM_FEEDBACK_FOLLOWUP
  • APEX_TEAM_MILESTONES
  • APEX_TEAM_TODOS

How, Why, Where to Use?

There are ample opportunities to take advantage of the data within the tables. You can use query responses within APEX to regulate behavior of an application. Alternatively, you can query the data externally to evaluate the consistency of development work performed. The ambitious amongst us might plot to put together their own “advisor” tool set or capture activity logs as part of a quality assurance process.

Synonyms in an Application

A few examples to kick things off… Let’s envision a region defined on an Oracle APEX Global Page. I want it to display only when the page mode is “Normal”. I don’t want to waste space on dialogue boxes.

With my region, I add a “Rows Returned” condition with the following query:

select page_id
from apex_application_pages
where application_id = :APP_ID
and page_id = :APP_PAGE_ID
and page_mode like 'Normal%'

Ad-Hoc Advisor

If a team wants to explore the consistency of their work, a few queries may help. I have used it to find old button CSS from APEX 4. In the example below, I want to discover how many pages do not have help text:

select 
	page_id,
	page_name,
	page_title
from apex_application_pages 
where application_id = 101 
	and (
	help_text is null or
	help_text like 'No help is available for this page.'
			);

You can do the same for page access protection, pages requiring authentication, or button styles, or page item styles. Pretty much anything within your application, you can see if your approach has been consistent.

Are all of my cancel buttons grey?

Are all of my save buttons hot?

Here is an example for seeing how complete the help text is for items…

select
	page_id,
	page_name,
	item_name,
	display_as,
	label,
	item_help_text
from apex_application_Page_items
where application_id = 101
	and display_as <> 'Hidden'
	and display_as <> 'Display Only';

Quality Assurance Tools

The Oracle APEX development team provides us a view called APEX_DEVELOPER_ACTIVITY_LOG. This view provides insights on all development efforts within APEX. I have spent month and months exploring these data with the question: How do I improve the quality management process within APEX. Part of the answer sits here.

If I want to link:

  • Applications
  • to Releases
  • to Bug/Features
  • to coding efforts

Then part of my answer is in this view: APEX_DEVELOPER_ACTIVITY_LOG. (The other part exists as database changes that I can get from database views). Like a plastic shovel on a beach, I can now scoop all of the changes, additions, deletions made by developers into a table. I can link that activity with bugs or release data and the developer responsible. I can go further and use these data to develop a test plan. I pack these granular bits into my bucket and tip out structures that assist a development team. It is not just a change log. It is not just Big Brother watching, or another token in a Blame-Game of who buggered what. It become a means of linking building and supporting an application with the release notes, the change log, the test plan. It is a building block in robust software development.

At Storm Petrel, we’ve started, but that is it.

What Next?

Please ask me what would make these synonyms/views more powerful?

First, to really work with these data we do need a primary key, that unique think that allows use to link local data to these behind-the-scenes data. While some data are inherently unique, some just are not. The APEX_DEVELOPER_ACTIVITY_LOG is one such view. If you copy data from this view to a local table, the process of linking back is cumbersome.

Second, I have an easy time envisioning a packaged application that brings Team Development tools and developer activities together such that the application and its related data are portable. We lost such stuff when we moved from 11g to 12c. Team Development data was lost when we moved from 4.x to 5.x. And we are developers, we “improving” stuff (whether or not it actually improved!). We’re looking at this potential from the edge. APEX is a awesome tool.

-qed

Oracle APEX Page Type

Have you ever just needed to know what Oracle APEX page type or page mode you are on? In all of our applications, we use a region on the global page to provide a little useful feedback, context such as who is your current client, or number of days until your password expires. But all too often these nuggets of help clutter small modal dialogue boxes. Annoying?

Let APEX manage it for you!

Condition Attribute

APEX is rather consistent about having a condition attribute for pages, regions, page items, buttons, etc. While this list is not alphabetized, an order is present. Someone at Oracle decided on a presentation order for us with this idea that the most frequently used condition attributes are at the top:

At present (summer 2016), the list starts:

  • Rows Returned
  • No Rows Returned
  • SQL Expression

What query can I write that tells me if my page is ‘normal’, ‘modal dialog’, or ‘non-modal dialog’. If I can query for normal pages, then I can display my global page region on normal pages and suppress it when it would just be messy clutter on a small dialogue box. The order of the condition attribute list provides a hint: someone at Oracle thinks that the most common type of condition is based on whether a condition exists or does not exist within a query.

Oracle APEX Page Type Query

The folks on the Oracle APEX team have created views for us that allow us to tap into the inner workings of APEX. These are a series of queries I keep in my toolkit.

select 
  page_id,
  page_name
from apex_application_pages
where application_id = 122
order by 1, 2
;
select 
  *
from apex_application_page_items
where application_id = 122
  and page_id = 70001
;

select *
from apex_application_items
where application_id = 122
;
select * 
from apex_application_page_buttons
where application_id = 122
  and page_id = 70001
;

With a quick exploration of the view ‘APEX_APPLICATION_PAGES’, you will find a column ‘PAGE_MODE’ that describes what type of page it is: NORMAL, MODAL DIALOG, etc. With quick effort, you can create a query to find out if the current page, in the current application is normal or not. Please note that this query will be particularly happy outside of APEX as :APP_ID and :APP_PAGE_ID are defined within that context.

select 
  page_id
from apex_application_pages
where application_id = :APP_ID
  and page_id = :APP_PAGE_ID
  and page_mode = 'Normal'
;

Put it together

On page 0, the global page, you can add a region then put a condition on that region that will display only on normal pages as shown in the illustration below:

Oracle APEX Page Type

Planning Page Numbers

There are numerous advantages for planning page numbers in Oracle Application Express. The more obvious is that you can group pages and order then based on function. Finding a page from a list of pages becomes easier with a plan. Your list looks organized. Well, it is organized. The Christopher Columbus style stumbling ends and confident navigation begins.

There are numerous others that we have found over the recent years. I’ll come back to those. Last week, when playing with List Entry (application > shared components > list details > create / edit list entry), I looked at the List Current Entry for Conditions attribute. APEX wizards add pages using comma. Why vary, I did the same. But we are in our third new product this spring. Our internal standards and processes now impact how we number pages. These correspond to lists (menus).

So I found a nugget of good-stuff in APEX written by our friends at Oracle. You can use a SQL statement (or a PL/SQL statement) identify a current element of a list.

select :APP_PAGE_ID
from dual
where :APP_PAGE_ID between 70000 and 79999

In terms of a game, these are points. Coding manna points are earned when we let the tools do the work. Adding and managing comma or colon separated lists requires maintenance from humans.

Planning Page Numbers

Other Benefits

Copying Pages

The team at Oracle hint at this. They always number the login page 101. Many of us follow suit with the global page at 0. At Storm Petrel, the Help page is always 999 – a little tip of my hat at emergency services in the UK. Guess what, we want the Help Page in a new app, copy 999 to 999, done. The lost password page: 102. Again facilitating the copy feature between apps.

The bottom or last item on application menus tends to be administrative settings with a picture of a cog or a wrench. There then you find all the rather boring and so completely ordinary look up tables, configuration knobs and switches that systems require. For a while, we started these at page 90000. The landing page for the admin section on page 90000. The menu sequence number is 90000, the same at the page.

Menu Concordance

I admit that I do a lot of navigating between pages by running a page, using the application’s own navigation to hop around, then I press the “edit page” feature in the menu at the bottom. I don’t have to. Patterns emerge quickly. If your list (interactive report) sits on a page ending in 0 (zero), then the edit page likely sits on 1. Dancing between the two require the change of one digit. If 99000 is list of “Units of Measure” shown in an interactive report, then 99001 is the corresponding edit page.

Menu Concordance

Change the terminal digit from naught to one and done.

Commas or Queries

Hey anytime, I can step away from a delimited list that I must manage by hand, I’ll do it. If I were cleverer, I might have looked at this feature in Oracle with the same ears-up, tilted head perspective my dog too often uses at me. I might have asked: why is this here and how can I use it. Hum. Oh well, I got there in the end. And so did ole Christopher Columbus. Like him, I am excited to discover something already there, in place for years and used by others long before I saw it.

Digression

Now that we affirmatively know that Columbus didn’t actually discover America, can we get rid of his name day in October? My Viking ancestors drink and fight in Valhalla still fussin’. And my nephew’s Native American ancestors offer a unified “huh?”. Forgive the digression, such opinions belong outside of an Oracle blog.

-/qed

Oracle APEX 5 Sentry Function

Concord Minute Man

I decided to explore the Oracle APEX 5 Sentry Function that I started seeing in application parameters. On a newer custom application, I obediently copied the suggested code into a package. I put my trust into the great team at Oracle and nothing bad happened. That didn’t actually resolve my curiosity about what the thingy does and why is it is good for me.

The name is a pretty good clue: Sentry. A sentry stands a post and alerts others when issues arise. After a few hours of exploration, I see that the functions duties and its name are well matched. Let’s explore a bit together, eh?

My assumptions are this:

  • It’s new
  • It relates to a security posture
  • It incorporates older ad hoc processes
  • It comes with fully tested and suggested code

Therefore, I infer that the APEX development team is suggesting that we use it. But why hasn’t been answered for me…

My final question is: what can I use it for?

  • Prevent simultaneous logins
  • Log malicious actions

History & Definition

I performed an internet search engine search for the phrase “APEX Sentry Function” and for “Oracle APEX 5 Sentry Function”. I found an on-going series of posts and discussions about page sentry efforts and frustrations. Joel Kallman (@joelkallman) posted a page sentry function in a October 2010 blog. Christian Neumueller provides a definition on OTN

A sentry function defines whether the request should be allowed to be processed, because the session is ok. Since 4.1, we have an additional invalid session function that runs if the sentry returns false, before redirecting to the login page. A callback function can be used to continue processing when the login page is external. It runs when the external login succeeds and redirects back to APEX. The authentication function is used in combination with internal login pages to verify that the user credentials (username / password) are ok. If it returns true, the session gets updated with the username. Otherwise, the login page should show again, with an error message. The post logout function runs after the user logged out. It is sometimes useful for cleanup of session-related data or to tweak the redirect. […]The default already checks whether the session id and session cookie match, that should be sufficient. Only if you are trying to implement single sign-out, a sentry could check whether the session is already authenticated, but the REMOTE_USER became null. The invalid session function gets called when the sentry (either your custom one or the default) fails. If REMOTE_USER is not null, it can simply call APEX_AUTHENTICATION.POST_LOGIN to register the header value as the username, instead of doing the normal redirect to the login page. This authenticates the user without checking credentials with the authentication function. The callback function is not necessary in your case. The authentication function gets called when the user logs in, using the application’s login page (101). It can verify username/password for the cases where REMOTE_USER is null. This can be implemented either as an authentication plugin or with the custom authentication scheme. The former should be preferred for production code, the latter is sometimes useful for prototyping. The plugin and authentication item help texts for the various functions contain example code. Oracle OTN Christian Neumueller

While we attempting to define the role of a sentry function, let me post the help text from within APEX…

Enter the name of a function that is executed by the Application Express engine at the start of any request made to the engine, such as before each page is shown or processed, or an AJAX request is issued. It can be defined in the authentication’s PL/SQL Code, within a package or as a stored function.

If this function returns false (indicating perhaps that the user isn’t logged in),this marks the session as not valid and the Invalid Session Procedure will be invoked. After that, Application Express redirects to the URL defined in ‘Session Not Valid > Go To’. If the value of this field is empty, an internal default sentry is called that tries to establish a valid session by comparing the session ID in the URL and the session information in the authentication scheme cookie ( with a fall-back to the Application Express Builder cookie, if that fails).Oracle APEX Help Text

Context and Setup

The sentry function will not appear in all instances for example it is not an option when using the internal APEX authentication process. It is an option when using custom authentication.

This post does not discuss developing a custom authentication function with its related tables and such. Stepping away from the internal authentication process is requisite to playing with a sentry function.

Oracle APEX 5 Sentry Function

The help text provides the definition above, plus the code to run the function. Simply copy and paste into a package. In my case, my application and package are entitled Sunday (my normal blogging day, you see).

Oracle APEX 5 Sentry Function

How to watch the watchers?

Well, I have a silly little package called “trace_pkg” modelled on Steve Feuerstein’s (@sfonplsql) work. I pass a parameter and value, the trace_pkg drops the data in a table so I can study it.

function sentry_basic_auth
return boolean
is
    c_auth_header   constant varchar2(4000) := owa_util.get_cgi_env('AUTHORIZATION');
    l_user_pass     varchar2(4000);
    l_separator_pos pls_integer;
begin
  trace_pkg.trace_parameter (
    P_CONTEXT   => 'Sunday package sentry_basic_auth',
    P_PARAMETER => 'when',
    P_VALUE     => current_timestamp
  );
  trace_pkg.trace_parameter (
    P_CONTEXT   => 'Sunday package sentry_basic_auth',
    P_PARAMETER => 'c_auth_header',
    P_VALUE     => c_auth_header
  );
    trace_pkg.trace_parameter (
    P_CONTEXT   => 'Sunday package sentry_basic_auth',
    P_PARAMETER => 'l_user_pass',
    P_VALUE     => l_user_pass
  );
    trace_pkg.trace_parameter (
    P_CONTEXT   => 'Sunday package sentry_basic_auth',
    P_PARAMETER => 'APP_ID',
    P_VALUE     => v('APP_ID')
  );
  trace_pkg.trace_parameter (
    P_CONTEXT   => 'Sunday package sentry_basic_auth',
    P_PARAMETER => 'APP_PAGE_ID',
    P_VALUE     => v('APP_PAGE_ID')
  );
...

This tells me:

  • When the function ran
  • What parameters it saw
  • And what page it was called from

Before looking, I wondered if it ran on a clock or an event or how it decided trigger.

The simplest answer is that it fired at each page load. After re-reading the definitions and information posted by Joel and Christian, the more complete answer is: Any request to the APEX engine.

Testing

I logged out of my application and returned to the login page (page 101).

Login URL

Then in the URL, I manually keyed in the edit user page number: 90101.

Modified URL

So, I am:

  • Not authenticated
  • Not authorized
  • Entering a APEX page

What will my sentry say? Let’s look…

Trap and Trace of Sentry Function

The username, in blue, is nobody. And I can see when I attempted to load page 90101 (edit user) in application 146 (my Sunday blog sandbox application).

What did APEX do? Well, it kept me on the login page. So the action of the sentry function wasn’t super obvious. Without the sentry function, you still don’t get in. With the sentry function, I can track, measure, monitor such actions.

What to do with the Sentry Function?

Prevent Simultaneous Logins…

The first answer is that you can use this function to limit users to one login at a time. Oh, there is some development and debugging effort involved. I present this as an idea to explore. Maybe you store the session ID, and timestamp in the user table. With the sentry function you confirm that each time the user goes from page to page that there is only one session ID. You’d have to have pretty good time-out processes and a user logout function to clean up. For a company that sells access to software by the username, this is interesting.

Log malicious actions…

With a bit of branching logic in the sentry function, you can create a log of events that might bring concern. Insert records into a log table with forensic data. Granted the username of “nobody” is of little value, but with work you can fetch out their IP address, browser data, country, and more (based on your skills). Put this information into a log with a timestamp and track the frequency of bad actors attempting bad actions.

Let the sentry do its duty and don’t ignore their cries from the dark.

Next Steps

It is up to you and your team on how to manage your security posture. I am going to start including the sentry function into applications. And I’ll explore both the ideas above.

I started this exploration simply wondering why is this here and what will it do for me.

-/qed

Media Capture from Phone with Oracle APEX

Do you want to capture images, video or audio into an APEX application directly from your phone?

Media Capture from Phone with Oracle APEX is easily accomplished natively within APEX. No need for JavaScript, no need for bridging apps. If you develop software using Oracle Application Express, then you have all of the tools that you need.

Step 1

Add a file browse page item to your page. Give it an appropriate name and set your storage time as desired. For this example, I am using APEX_APPLICATION_TEMP_FILES.

APEX_APPLICATION_TEMP_FILES

Step 2

Add custom attributes to the “Advanced” section of the page item attributes as shown in the illustration.

Custom Attributes

Step 3

Save it as you normally would. Need help on processing APEX_APPLICATION_TEMP_FILES take a quick look at my blog entry from August 2015.

Step 4

Try it. Pick up your phone, log into your app, navigate to the page.

Caveats…

I’ve tried this only on my Android phone.

Options

Audio?

Try

accept=”audio/*” capture=”microphone”

Video?

Try

accept=”video/*” capture=”camcorder”

-/qed

Data Preparation for AOP

Background

The team at Storm Petrel are enthusiastic about using AOP (APEX Office Print) for developing high-quality and professional reports directly from Oracle Application Express. Our expectation is that with AOP, the cost of developing terrific looking reports will be reduced (significantly?!) and that we will gain greater flexibility with report layout. Data preparation for AOP has a few tricks.

We have been collaborating with APEX RND during a trial period with AOP. We find the support excellent. We have learned that new techniques must be employed to step into their world. The purpose of this blog entry is to assist other (and help remind us) what these techniques are.

Using an inventive tool really does require that one not just walk in the moccasins of another, but see through their eyes as well.

Context

Writing database reports requires two processes:

  • Designing the report for printing on paper;
  • Developing the data structures for the report.

Our industry has been separating these processes for decades. Collectively we have said “let’s use the best tool for each job.” Word processors and spreadsheets have become standard tools for presenting data neatly and attractively. And SQL is our industry’s best tool for organizing data. BI Pub has been doing this for years (although BI Pub always broke my MS Word or Excel and generated more frustration than freedom).

APEX RND employs SQL for data management and word processors/spreadsheets for layout and design. Y es, they let you use power point too. The output is flexible: PDF, Word, Excel, or HTML5.

We will return to related topics in the weeks to come. Even in an old friend like Word, or Excel, there are tricks and rules.

Future Entries

  • Preparing MS Word templates
  • Preparing MS Excel templates
  • Formatting numeric values
  • Subtotals with GROUP BY ROLLUP

Prepare the Payload

You are sending all of the components of a report to an engine that generates your report. The components are the report layout instructions and the data to populate the report. AOP has you do this in one query creating a single JSON data set. Like a payload on a rocket, all required “stuff” must be present when docking at the destination.

The payload bakes advantage of the power present within the JSON data structures. Data are nested. Records within fields.

The most fundamental aspect of the AOP payload are two “fields”:

  • filename
  • data

The Oracle SQL queries shown below are written in with tools we have on our desks such as SQL Developer. The output is implicitly JSON. One does not need to convert or make an explicit declaration for JSON.

The query framework to create the payload is:

select
  'file1' as "filename",
  cursor ( select
  	cursor ( select
      -- place holder for header fields
      cursor (
				-- place  holder for detail query
        ) as "detail"
			-- place holder for header tables, joins and where
    ) as "header"
 from dual ) as "data"
from dual

If you require a report that does NOT have specific header data, the framework is simplified. The payload requirements remain the same:

  • filename
  • data

The simplified structure does not include the header data set. It looks like this:

select
	'file1' as "filename",
  cursor ( select
     cursor ( select
           -- place holder for fields
           -- place holder for tables, joins and where clause
    ) as "detail"
 from dual ) as "data"
from dual

Inside – Out

Start your query from the inside. Build out – Build up. The very last step of the process involves wrapping the query into the AOP structure. SQL Developer, APEX SQL Workshop, APEX classic and APEX interactive reports do not tolerate the nested structure required by AOP. These tools do not display data presented as JSON. Even our good friend SQL Developer adds extra characters into the JSON output that annoys on-line JSON editors.

Develop solid queries with the existing tools.

For my example, I will generate a report from the standard Oracle “Demo” data using the table DEMO_ORDER_ITEMS. With subsequent blog entries, we will explore GROUP BY ROLLUP and adding richness to the report. The first payload ought to be low risk and simple – more Sputnik, less Enterprise. I’ll come back to add subtotals and such.

Detail Query

Starting with a familiar query on a familiar data set, let’s display the order items for one customer while providing an extended total for each line.

        select
          o.customer_id             "customer_id",
          i.order_id                "order_id",
          i.order_item_id           "order_item_id",
          p.product_name            "product",
          i.quantity                "qty",
          nvl(i.quantity,0)     
            * nvl(i.unit_price, 0)  "extended"         
        from demo_order_items i
        left join demo_orders o on
          o.order_id = i.order_id 
        left join demo_customers c on
          c.customer_id = o.customer_id
        left join demo_product_info p on
          p.product_id = i.product_id
        where o.customer_id = 1
               
               ;

The results of this query should look like this:

Query Results

Header Query

Let’s go create a header query. We want the data to be related and come together when put it in the AOP payload, so use table aliases and criteria that will subsequently prepare for the marriage of header and detail queries.

    select
        c1.customer_id                 "customer_id",
        o1.order_id                    "order_id",
        c1.cust_first_name || ' ' || 
            c1.cust_last_name          "customer",
        c1.cust_street_address1        "addr",
          c1.cust_city || ' ' ||
          c1.cust_state || ' ' 
          || cust_postal_code           "city",
        trunc(o1.order_timestamp)       "ord_date"
    from demo_orders o1
    left join demo_customers c1 on
      c1.customer_id = o1.customer_id
    where o1.customer_id = 1
;
Header Query Results

The results ought to resemble, the image below:

Lower Case

JSON is case sensitive. Oracle and Oracle APEX are not particularly case sensitive. The default case for column aliases in Oracle is all capital letters. Save yourself the four hours I wasted, make sure that every column has an alias, and make sure that the alias is small letters.

To do this, aliases must be presented in double-quotes and typed in lower case. Your quick validation is the column headings displayed in SQL Developer. If all in lower case, you will be a happier and more joyful person.

Use Lower Case

Short Aliases

Use short column aliases! You will be keying these into MS Word or MS Excel. If your alias is longer than your data, then you’ll be setting yourself up for frustration when laying out your report. For example, a standard date written in the US style is “MM/DD/RR”, 8 characters long. If our alias is longer than 8 characters, e.g. “activity_start_date”, then MS Word will force your column to be long enough to hold the phrase {activity_start_date}. Arguing with Word is a waste of time. Plan ahead. Use short aliases.

Data Preparation for AOP

Use copy/paste to wrap the query into the needed structures. Don’t over think it. When doing data preparation for AOP, see the elements and make small steps.

Replace the single “select” at the top with this:

select
	'file1' as "filename",
  cursor ( select
     cursor ( select

And stitch the parts together. You will need to separate the header query. Tease the columns away from the FROM and WHERE clauses.

select
  'file1' as "filename",
  cursor ( select
  	cursor ( select    

        c1.customer_id                 "customer_id",
        o1.order_id                    "order_id",
        c1.cust_first_name || ' ' || 
            c1.cust_last_name          "customer",
        c1.cust_street_address1        "addr",
          c1.cust_city || ' ' ||
          c1.cust_state || ' ' 
          || cust_postal_code           "city",
        trunc(o1.order_timestamp)       "ord_date",
  
   cursor ( 
   
      select
          o.customer_id             "customer_id",
          i.order_id                "order_id",
          i.order_item_id           "order_item_id",
          p.product_name            "product",
          i.quantity                "qty",
          nvl(i.quantity,0)     
            * nvl(i.unit_price, 0)  "extended"         
        from demo_order_items i
        left join demo_orders o on
          o.order_id = i.order_id 
        left join demo_customers c on
          c.customer_id = o.customer_id
        left join demo_product_info p on
          p.product_id = i.product_id
        where o.customer_id = 1
        
        ) as "detail"
        
    from demo_orders o1
    left join demo_customers c1 on
      c1.customer_id = o1.customer_id
    where o1.customer_id = 1
    
    ) as "header"
    
 from dual ) as "data"
from dual   

;

It will look like this when done. I leave the extra spaces. It lets me see through the required structures for AOP.

Simple Query

The preparation for a simple query without header data will look like this:

select
  'file1' as "filename",
  cursor ( select
    cursor (
      select
          o.customer_id             "customer_id",
          i.order_id                "order_id",
          i.order_item_id           "order_item_id",
          p.product_name            "product",
          i.quantity                "qty",
          nvl(i.quantity,0)     
            * nvl(i.unit_price, 0)  "extended"         
        from demo_order_items i
        left join demo_orders o on
          o.order_id = i.order_id 
        left join demo_customers c on
          c.customer_id = o.customer_id
        left join demo_product_info p on
          p.product_id = i.product_id
        where o.customer_id = 1
        
        ) as "detail"
  
 from dual ) as "data"
from dual   

;

Output

Here is where faith steps in. The data you get is now darn close to JSON data. I use SQL Developer.
There seem to be some errant greater-than (GT) and less-than (LT) characters throughout the data. I cannot take this data to a JSON formatter and look at it.

In the next installment, I’ll show you a trick given to us by AOP to capture the data midstream while in AOP to display it.

JSON Output

Dumping the output into a text file it reads like this:

{<customer_id=1,order_id=2,customer=John Dulles,addr=45020 Aviation Drive,city=Sterling VA 20166,ord_date=01/01/2014,detail={<customer_id=1,order_id=2,order_item_id=103,product=Business Shirt,qty=3,extended=150>,<customer_id=1,order_id=2,order_item_id=104,product=Trousers,qty=3,extended=240>,<customer_id=1,order_id=2,order_item_id=105,product=Jacket,qty=3,extended=450>,<customer_id=1,order_id=2,order_item_id=106,product=Blouse,qty=3,extended=180>,<customer_id=1,order_id=2,order_item_id=107,product=Skirt,qty=3,extended=240>,<customer_id=1,order_id=2,order_item_id=111,product=Mens Shoes,qty=2,extended=220>,<customer_id=1,order_id=2,order_item_id=112,product=Wallet,qty=2,extended=100>,<customer_id=1,order_id=2,order_item_id=108,product=Ladies Shoes,qty=2,extended=240>,<customer_id=1,order_id=2,order_item_id=109,product=Belt,qty=2,extended=60>,<customer_id=1,order_id=2,order_item_id=110,product=Bag,qty=4,extended=500>,}>,}

You are probably asking: where’s my report? Wo ist mein Bericht? Où est mon rapport? Dónde está mi informe?

Stay tuned…

-/qed