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

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

Oracle APEX 5 Classic Report Templates

Oracle APEX 5 Classic Report Templates are worth exploring and incorporating into your applications. Discovering their power requires a bit of effort. There are few blogs and the APEX 5 on-line manual does not seem to address these tools. to discover the required columns and the features that the APEX team tucked in there for us. The example below has been created with the Universal Theme (42) with a Classic Report. The Classic Report Region Appearance Template is “Hero” and the Classic Report Attributes Appearance Template is “Alerts”.

Conclusion

The completed example includes design elements from both the report data template and from the region template. The design elements include information visible when displaying the region and/or report with simple data, and with information discovered with Inspect Element. Use together, the Oracle APEX team has given us very powerful tools to provide a rich user experience without having to modify templates and make a deep dive into custom style sheets.

Taking a look at the example,

  • Region Title
  • Region Icon using a page item (&P10001_FA_ICON.)
  • Report data fed to the data template using column aliases as seen in a blank report
  • A link column fed by a primary key and displaying an edit icon using Font Awesome
APEX Hero Report

Introduction

Reports have two templates that can be applied. The wicked smart folks at Oracle have used very consistent means of accessing the templates. This means clicking and finding them is easy! Naming them is more difficult. When collaborating with a colleague, I quickly discovered the difficulty. When I said: change the template to Alerts, the instructions were ambiguous.

APEX Report Attributes
  1. Classic Report Attribute Appearance Template is heretofore named “Data Template“
  2. Classic Report Region Appearance Template is heretofore named “Region Template“

In both cases, the right-most column of my Page Designer is called Attributes and there is a section header “Appearance” with a select list for templates.

1. Classic Report Data Templates

Classic Report data templates have been a part of Oracle Application Express for a while. With APEX5 and the universal theme (UT), they have taken a significant step.

1.1 Looking Back

The most previous releases of APEX did include Region Templates and Data Template. As memory aide, common Data Templates are displayed below. The names and appearance changed with the application’s theme.

Older Report Templates

1.2 Universal Theme Report Data Templates

Today the list is simplified.

Classic Report Data Templates

The options are:

  • Alerts
  • Badge List
  • Cards
  • Comments
  • Search Results
  • Standard
  • Timeline
  • Value Attribute Pairs – Column
  • Value Attribute Pairs – Row

Hint number one, ignore the names, play use them as they fit into your design and workflow. I have gone through the on-line manual to find more information about these templates. Not finding much, I’ve played to see what these nuggets give us.

Hint number two, go explore.

1.3 Exploring…

1.3.1 Establish a Baseline

I created a classic report using the default “standard” region template. Wanting to isolate the data from the presentation of the data, I used this query:

select 1,'two', 'three' from dual

I know exactly what this looks like as a classic report in a standard region with a standard data template:

Classic Report

1.3.2 Data Alerts Template

Making exactly one change, I’ll modify the data template (Classic Report Region | Attributes | Appearance | Template)
setting it to “Alerts”. My display now looks like this:

Alert Template

Cleverly, I am being told what to name the columns in my report. What this is telling me is I have three columns that will display. The columns must be aliased:

  • ALERT_TITLE
  • ALERT_DESC
  • ALERT_ACTION

The Alert Title will display with some sort of header tags appearing larger (<H2>). I’ll modify my query…

select 
    1         ALERT_TITLE,
    'two'     ALERT_DESC,
    'three'   ALERT_ACTION 
from dual

Then redisplay…

Alert Template

Leaving this alone, I’ll change the data template again, this time to Comments.

1.3.3 Comments Template

Comments Template

There is a mystery about the circle… I’ll use inspect elements from my browser to look at the rest of that content.

Comments Template

The value inside the circle derives from a column with the alias USER_ICON. There is an opportunity to feed HTML/CSS modifiers to the circle using columns with the aliases: COMMENT_MODIFIERS and ICON_MODIFIERS.

1.3.4 Timeline Template

You’ll observe the pattern repeated with the Timeline template. The APEX development team provides you the names of column aliases to feed the template.

Timeline Template

Using inspect element through my browser, I can take a look at additional features the template provides us.

Timeline Elements

The template include rich features. While the #USER_AVATAR# is obscured in the sample template, it is fully visible when you look under the hood. You now also see that you can pass other information through to the template.

What fields can be passed to the Event Template:

  1. Visible Data:
    • #USER_AVATAR#
    • #USER_NAME#
    • #EVENT_DATE#
    • #EVENT_TYPE#
    • #EVENT_TITLE#
    • #EVENT_DESC#
    • #EVENT_ICON# (presumed Font Awesome)
  2. HTML/CSS Modifying data
    • #EVENT_MODIFIERS#
    • #event_attributes#
    • #USER_COLOR#
    • #EVENT_STATUS# (CSS class definition)

Let’s modify our simple query to explore these nuggets…

select 
    'C'                    USER_AVATAR,
    'cmoore'               USER_NAME,
    '20DEC15'              EVENT_DATE,
    'BLOGGING'             EVENT_TYPE,
    'Report Templates'     EVENT_TITLE,
    'Look through the various data templates for APEX 5 classic reports' EVENT_DESC,
    'fa-book'              EVENT_ICON,
    'user_blue'            USER_COLOR,
    'status_late'          EVENT_STATUS,
    'event_exciting'       EVENT_MODIFIERS
from dual

It is time to jump into the Theme Roller and add definitions for the silly stuff above. I am going to throw in definitions that are bold, obvious, and unambiguous (not necessarily attractive!!). Refining into a design comes after I understand the tool.

Theme Roller

For this situation, I put the following text into my CSS

.status_late {
  background-color: #FF0000;
}
.event_exciting {
  background-color: #FFFF00;
}
.user_blue {
  color: white;
  font-weight: bold;
  background-color: blue;
}

The results are displayed below:

Comments Colored

1.3.5 Other Templates

Use the same techniques to unlock the features of the other templates. A recommended process of exploration is illustrated in this blog entry:

  1. Create a silly query that generates limited data.
  2. Then display the results through a classic report
  3. Explore the data template
  4. Revise the query with column aliases
  5. Use Inspect Element in your browser
  6. Revised the query adding CSS nuggets
  7. Rinse, lather, repeat…

You have got 9 such templates to play with. That’s hours and hours of playing.

2. Report Region Templates

Report Region Templates modify the settings for the region. They leave the arrangement of data alone, yet provide a visual context for the data. There are thirteen region templates. These templates are available for regions regardless if they are classic reports or not. You can put a classic report into an interactive report region.

Region Templates

The thirteen region templates are:

  1. Alert
  2. Blank with Attributes
  3. Buttons Container
  4. Carousel Container
  5. Collapsible
  6. Hero
  7. Inline Dialog
  8. Interactive Report
  9. Login
  10. Standard
  11. Tabs Container
  12. Title Bar
  13. Wizard Container

Why not put a very basic report into each of these containers and seeing what they do? You may notice if you stick a classic report into a region with a Login template, the title is displayed in big clean letters and centered. The region has a bit of shadow to the right.

For the remainder of this blog entry, I am going to use the Alerts Data Template as shown in 1.3.2 above. With a baby step forward, I’ll use a query based on data to populate my report:

select
  a.first_name || ' ' || a.last_name ALERT_TITLE,
  'Status: ' || Status_name || '<br/>' ||
  ' Ranking: ' || ranking
  ALERT_DESC,
  asset_pk ALERT_ACTION

from cadr_asset a
left join cadr_status_type t on
    t.status_pk = a.status_fk
where a.asset_pk = :P10001_ASSET_PK

I have my three required aliases:

  1. Alert_Title
  2. Alert_Desc
  3. Alert_Action

2.1 Alert Description

I’ve tossed a little HTML into the second column, so I’ll need to make sure that the 
does not get parsed out. I set Escape Special Characters to NO.

Region Templates

2.2 Alert Action

In my Alert Action column, I pass a primary key. In this case, I create a link column where the link text is:

<span class="t-Icon fa-pencil"></span>

Putting the pieces together in a standard region template, the result looks like this:

Alert

2.3 APEX 5 Hero Template

I have not yet crack all of the secrets of this template. I’d love to read someone’s post on this region. The abilities of this template exceeds the scope of the entry. The template does have an font awesome icon sitting in the box.

2.3.1 Hero Template: Icon

Exploring the template with the browser’s Inspect Element, I can see that I can feed an icon through to the region. In APEX Page Desinger, in the pre-rendering area, I have dropped a bit of PL/SQL after the data are fetched. This query populates a page item called P10001_FA_ICON with a value. The value must be a valid icon from the Font Awesome suite or from one of your own efforts in your CSS. For this example, I’ll use both. Icons ‘fa-ban’ and ‘fa-question’ are legitimate Font Awesome entries, whereas ‘fa-1’ through ‘fa-5’ are in my style sheet (see blog entry APEX5 Font-Awesome Letters).

BEGIN
select
    case 
      when reservist = 'ELIGIBLE' and ranking = 1 then 
          'fa-1'
      when reservist = 'ELIGIBLE' and ranking = 2 then 
          'fa-2'                  
      when reservist = 'ELIGIBLE' and ranking = 3 then           
           'fa-3'                  
      when reservist = 'ELIGIBLE' and ranking = 4 then            
           'fa-4'                  
      when reservist = 'ELIGIBLE' and ranking = 5 then            
           'fa-5'                  
      when reservist = 'NO_INTEREST' then 'fa-ban'
      else 'fa-question'
    end 
    into
    :P10001_FA_ICON
from cadr_asset 
where asset_pk = :P10001_ASSET_PK;
END;

I’ll drop put the icon in the region attributes as the icon.

Hero Region Icon

2.3.1.1 Font Awesome Style Sheet Entries

Just to keep everything needed in one post, here are the entries to the CSS for numbers in Font Awesome. I used the Theme Roller and Custom CSS to add these:

.fa-1:before {
content: "\0031";
}
.fa-2:before {
content: "\0032";
}
.fa-3:before {
content: "\0033";
}
.fa-4:before {
content: "\0034";
}
.fa-5:before {
content: "\0035";
}

2.4 Oracle APEX 5 Classic Report Templates

Exploring, making mistakes and being curious is what got most of us into the world of software development. Like so many, I feel rewarded when I discover gold in the work of others that I can capitalized on. Explore Oracle APEX 5 Classic Report Templates and help the community better understand what is still hidden in there!

</qed>

Oracle APEX 5 as REST Client

Combining APEX_WEB_SERVICE.MAKE_REQUEST and the APEX_JSON toolkit, writing an interface to an external application has become a bit easier for Oracle APEX developers. I encourage folks to go play and share what they find. A few weeks ago when wanted to write an Oracle API for MailChimp, I floundered for days. Vincent Morneau (@vincentmorneau) with Insum Solutions coached me. Together, we found where I had gone wrong, put the tools I needed in my hands and off we went. With these tools, writing an interface to anything with a REST API become approachable.

The process of POSTing data out to a host with JSON formatted data is in Part 2.

Topics

  • Opportunities
  • Obstacles
  • History
  • REST Client

Opportunities

Through REST API, I can share data with Microsoft Outlook and GMail. New York City has REST API for their open data initiative. I found that on 01DEC15 at 23:15 (11:15p) a “fatigued/drowsy” driver in a sports utility vehicle ignored a traffic light. The accident was on Nostrand Ave at Beverly Road in Brooklyn. No injuries reported, no cyclists killed. I don’t why I need these data in my APEX application, but I can get it if needed using a REST API with the City of New York.

While the tools streamline the HTTP request and managing the data, the process of requesting data over the rather public internet, puts one in the position of a supplicant (see picture above). In many cases, the instructions are pretty good and easily found.


The MailChimp wrote clear, well-organized directions.

MailChimp API

Vendors who host data complicate the process due to security concerns. For example, the MailChimp instruction set is available only after you’ve logged in. The instructions for AWS S3 authentication string awes me.

APEX_WEB_SERVICE.MAKE_REQUEST improves our ability to share data with others. I flounder when reading code written with the UTL_HTTP package. I just don’t have the background in HTTP to even read some of the code. As a database programmer, I am able to follow code that looks like this:

  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url               => l_url,
    p_http_method       =>  'GET',
    p_username          =>  G_API_USER,
    p_password          =>  G_API_KEY
    );

The Oracle REST request opens doors allowing us all to explore opportunities without the obfuscation of manually creating HTTP headers and HTTP calls. This tool kit was introduced in APEX 4.0.

Obstacles

I did NOT figure out the process without help which left me a little frustrated. Frankly, I kept following pathways with really big sign posts which always brought me to the wrong place. Two factors influence my aimless stumbling:

  • The processes are asymmetrical.
  • The processes improve and change.

Asymmetrical REST processes

Hosting RESTful services involves large obvious buttons in APEX. On the other hand, configuring requests is not technically in APEX. When an APEX application becomes a supplicant, one manages the data and the requests from PL/SQL.

  • Hosting REST – configure Application Express as a web service provider
  • Request data via REST – use PL/SQL

I did not help myself when I expected to “just figure it out”. I bounced around the corners of APEX like a lost Rumba.

Do NOT press the big green button!

APEX RESTful Services

The volume of data about APEX REST obtained in search results further obscures the process.
The signal-to-noise ratio inverted against me as most data I found did not help me configure APEX retrieve data from other hosts.

New Processes

With new processes evolving with Oracle in support of APEX 4.2 then APEX 5, newer blogs and newer instructions need to be written. The internet does a poor job of forgetting anything. Documents are not normally stamped “Obsolete”, old and new reside side by side.

History

The PL/SQL library “Alexandria” lives and serves well, though some of the code shows age. Reading API for Google Maps, Amazon Web Services, and others daunted me. The Alexandria tool kit is visible in every schema on our server. The code stands as an authority thereby forming my impression of how to search for topics, and how to approach topics.

I let older tools hinder my willingness to step into this realm. The tools are good and improving!

Tool Discussed

  • APEX_WEB_SERVICE.MAKE_REQUEST
  • APEX_JSON
    • APEX_JSON.parse
    • APEX_JSON.get_varchar2
    • APEX_JSON.get_number
    • APEX_JSON.get_date
    • APEX_JSON.get_count

REST Client

As of 2015, APEX_JSON utilities allows developers to readily manage JSON formatted data from within an application.

You will need the instructions from the host. You will want a JSON viewer sitting in a browser. You will need to see and know the data before, during and after you write the code.

The flow looks like this:

  • Setup tables
  • Initialize variable, test parameters and set your work environment
  • GET the data through APEX_WEB_SERVICE.MAKE_REQUEST
  • Store these data in a CLOB
  • Parse that CLOB for JSON data.
  • Error check the response
    • log errors
    • handle errors
    • raise error
  • Work through the data, one element at a time
  • Loop through arrays & Write your data to a table
  • Communicate success

Setup

Set up tables that mimic the data structures at your host. While maybe not how and were you ultimately need these data, it makes the API significantly simpler. Furthermore, the API may be portable. Start with an anonymous script that calls APEX_WEB_SERVICES and captures the JSON output as a blob. Turn on the DBMS Output, copy the clob contents and past it to an on-line JSON viewer such as JSON Editor Online.

function get_account_details 
  return boolean
as
  l_url           varchar2(4000);
  l_clob          clob;
begin

 l_url := G_URI_ROOT ||
 '/lists';
  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url               => l_url,
    p_http_method       =>  'GET',
    p_username          =>  G_API_USER,
    p_password          =>  G_API_KEY
    );
dbms_output.put_line(l_clob);
  return true;
end get_account_details;

Build a table that mirrors the data. Now maybe the data you see passes an array at that top level. Build your table to hold the elements of the array and know that when it comes time to read these data and save as records, you’ll be using a loop.

You’ll have the entire JSON object in memory on your server. You can reference any element easily using a path statement e.g. account.name or account.id, report_summary.open_clicks, etc. You might want to create field names such as account_name, account_id, report_summary_open_clicks. This will improve your debugging experience. Oracle limits field names to 30 characters, JSON doesn’t. As paths get wicked long, you’ll need to revise the schema.

Use the table structure for your own ROWTYPE array.

I did not use relationship constraints in the tables. In the event of a violation, I did not want my data exchange to break.

Initialize

For initializing, you’ll need values (l_values) and paths (l_paths) defined with the types provided.

function get_something  
return boolean
as
  l_url           varchar2(4000);
  l_clob          clob;
  l_values        apex_json.t_values;
  l_paths         apex_t_varchar2;
  l_count         number;
  l_temp_date     date;
  l_select_count  number;
  l_return        boolean := false;
  account_detail  mc_account_detail%ROWTYPE;
  error_entry     mc_error_log%ROWTYPE;
begin

Get the Data

As shown above, you’ll use MAKE_REST_REQUEST. This function drops data into a CLOB.

begin

  l_url := G_URI_ROOT;

  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url               => l_url,
    p_http_method       =>  'GET',
    p_username          =>  G_API_USER,
    p_password          =>  G_API_KEY
    );
...

Parse the Data

We now have APEX_JSON to convert CLOB or VARCHAR data to the JSON format.

  apex_json.parse (
    p_values => l_values,
    p_source => l_clob
    );

I now have the full data set at hand. You’ll need your JSON viewer to see what you are playing with. You can do this:

dbms_output.put_line (
    apex_json.get_varchar2(
      p_values => l_values, 
      p_path => 'status'
    )
    );

This will show you the value of ‘status’. In JSON, this looked something like this: {“status”:”ok”}

Want the value of ‘account.name’? In JSON, this looked something like this: {“account”:{“name”:”flintstone”}}

dbms_output.put_line (
    apex_json.get_varchar2(
      p_values => l_values, 
      p_path => 'account.name'
    )
    );

Error Checking

You’ll need to read the host’s API instructions for each request. Build yourself a little table for storing the errors (date, status, title, details, clob, URL). Before jumping into your juicy happy data look for the fowny face of an error.

  error_entry.error :=  
    apex_json.get_varchar2(
      p_values => l_values, 
      p_path => 'error'
    );
  if error_entry.error is not null then
    error_entry.error_date :=  
      current_timestamp;

    error_entry.error_title :=  
      apex_json.get_varchar2(
        p_values => l_values, 
        p_path => 'error.title'
      );
... ... ...
   
    insert into mc_error_log values error_entry;
    commit;
     RAISE_APPLICATION_ERROR(-20001, 'api_pkg.get_account failed with '|| 
      error_entry.error_title);
  end if; -- error occured

Work through Data

You’ve got tools with APEX_JSON for the following data types:

  • Get Varchar2
  • Get Number
  • Get Date
  • Get Boolean

Frankly, I captured and stored the boolean values as varchar. They came across as ‘true’ or ‘false’. That seemed as good as anything. I do recommend using the GET_DATE function. It saves working around a date format Oracle doesn’t love.

  account_detail.contact_country :=
        apex_json.get_varchar2(
          p_values => l_values, 
          p_path => 'contact.country'
         );
   
  account_detail.last_login :=
        apex_json.get_date(
          p_values => l_values, 
          p_path => 'last_login'
         );
     
  account_detail.total_subscribers :=
        apex_json.get_number(
          p_values => l_values, 
          p_path => 'total_subscribers'
         );

Loop Through Arrays & Save Data

We’ve got GET_COUNT to find the number of rows in an array. Start here and loop. In my example, campaigns is an array. In JSON, it looks like this

{
  "activity": [
    {
      "action": "open",
      "timestamp": "2015-10-20T13:02:38+00:00",
      "campaign_id": "1476a5b832",
      "title": "Tempest-Time 3.0"
    },
    {
      "action": "open",
      "timestamp": "2015-10-14T14:40:46+00:00",
      "campaign_id": "1476a5b832",
      "title": "Tempest-Time 3.0"
    }
   ],
   "total_items":2
 }

For our example then, we’ll do this…

   l_result_count := APEX_JSON.GET_COUNT(
    p_path      => 'activity',
    p_values    => l_values
    );    
 
 for i in 1 .. l_result_count loop

    l_campaign.activity_action :=
        apex_json.get_varchar2(
          p_values => l_values, 
          p_path => 'campaigns[' || trim(to_char(i)) ||'].action'
         );
         
... ... ...         
    select count(campaign_pk) into l_select_count
      from mc_campaign
      where campaign_id = l_campaign.campaign_id;

    if l_select_count = 0 then
      insert into mc_campaign values l_campaign;
    end if;
    if l_select_count = 1 then
      -- pickup primary key
      select campaign_pk into l_campaign.campaign_pk
        from mc_campaign
        where campaign_id = l_campaign.campaign_id;
      -- save record
      update mc_campaign set row = l_campaign
        where campaign_id = l_campaign.campaign_id;
     
    end if; -- l_select_count on mc_list 
    commit;
  end loop; -- l_result_count

Communicate Success

Using functions, I threw a boolean back at the calling procedure. Then you are done.

  return true;
end get_campaigns;

-/qed

APEX5 Font-Awesome letters

Background

In one of our applications, we have a thing called a PW. Throughout the industry (disaster response, that is), a PW is a real thing. While once-upon-a-time it was a worksheet in Excel describing a FEMA funded project, the “what” has morphed well beyond. I need an icon for PW. When I started this morning, I would have accepted “P”. I was not going to accept the PayPal “P”. With this trick, any of us can have standard letters and numbers present themselves as icon on APEX5 lists and navigation menus.

Font-Awesome PW

APEX5 Font-Awesome Letters

The effort requires a small bit of knowledge about ye olde ASCII chart, and a tiny bit about Custom CSS in Oracle APEX 5. I’ll layout the trick, then show how I got here. Then I’ll lay out the challenge for someone to improve on the process.

Fetch ASCII code

Ye olde ASCII chart shows me the following representations:

  • Capital P
    • Decimal: 80 *
    • Hex: 50
  • Capital W
    • Decimal: 87
    • Hex: 57

Modify Custom CSS

Display your page through Oracle Application Express, give a click on the Theme Roller, and add Custom CSS:

add the following nuggets to the CSS:

.fa-pw:before {
content: "\0050\0057";
}

You’ll improve speed by ‘saving as’ and setting to current theme. If not your current theme, it does find it. I saw blank space for a few milliseconds while it grepped for the CSS reference.

Modify List Entry

List Entry

Edit the image/class for your list entry and add ‘fa-pw’.

Save, refresh, etc. Smile!

How this works?

I searched for better ideas. Getting frustrated, I downloaded the font awesome CSS to my desktop computer unzipped it, opened it with Notepad++, then read their approach. The authors dropped a bit of information stating that they were using a designated private space for ASCII representation. And I read:

.fa-plane:before {
content: "\f072";
}

oh, says I to me (and my dog), they are substituting a phrase for ASCII, I can do that.

Other Ideas?

Has anyone found a different way? If so, please share!

*Footnote

Sadly, parts of the ASCII table reside in my personal non-volatile random access memory. Capital A is locked in the brain as 65. As an uber dorky kid, I spent time writing code that made the bell ding (ASCII 7) teletypes connected via acoustic couplers, following college handing writing reports required I provide carriage returns/line feeds directly to printers. I woke up this morning having counted letters from A to P and knowing I’d need the number 80 today.

-/qed