Maximize region and JET Charts

When using JET Charts in APEX regions, I often enable the “Show Maximize Button” in the live template options of the regions. This allows users to view the region in a “full screen” display mode. However the JET chart height is NOT following and will stay the same height, unless we use a little CSS.

First, lets apply the “Maximize region button” to the region:

maximize_option

Live template options

After applying this, we get a the following icon in the region corner:

maximize_region

When pressing the maximize button, the region is blown up to full screen, but notice the height of the chart:

maximize_before

Maximized region – but height is not maximized

The chart only takes up the same height as before – when it was constrained in the small region. So what my users want is that the height scales up to the full screen (as the width is doing).

In order to accomplish this we can use some CSS. Please note that you need to give the JET chart region a “static ID”. In my case the ID is “orders”. When looking into the HTML behind the maximized region, this is what it looks like:

css

HTML showing the region header

The region has gotten a “is-maximized” class when the user clicked the button. Also notice that below the “orders” region div, APEX creates another div containing the actual chart component. This div is always called <static ID>_jet. In my case this is “orders_jet”. We can use that information to construct the CSS needed to increase the height.

.t-Region.is-maximized #orders_jet {
height:82vh!important;
}

This CSS snipped can be pasted into the “Inline CSS” property on the page which contains the JET Chart region.

The actual height should be adjusted according to your needs. In my case its 82% of the total viewport height. Now when I maximize, the page looks like this:

maximize_after

Maximized region with full width and height

What the users wanted, the chart is maximized in both height and width.

Hope you find it useful.

/Martin

APEX 18.1 – Upgrading your installation

Hi,

Yesterday the new version of Oracle Application Express (APEX) was released. Oracle is now naming their product with Year.Quarter, so the new release is 18.1.

The upgrade procedure is basically the same as when you perform a new installation. In my case, APEX is running on a cloud server hosted by “upcloud”, running the Oracle XE 11g database and the winning combination of Apache Tomcat and Apache HTTP Server as the application/web tier.

The total upgrade time for my installation is approx: 25 minutes all-inclusive. The steps to perform are:

A. Backup the server

Most cloud providers has a backup button somewhere. So use this before performing the upgrade. Time: 5 seconds.

B. Transfer the APEX software to the server

I used “scp” to copy the product (zip file) to the server. Time: 8 seconds.

C. Unzip the APEX software

Using the Linux “unzip” command, unzip the product.  Time: 10 Seconds

D. Install APEX in the database

As the “oracle” Linux user, start sqlplus and perform the installation. Time: 21 minutes.

# sqlplus / as sysdba

SQL> @apexins SYSAUX SYSAUX TEMP /i/

E. Copy images to the web server images directory

Images from the APEX zip file extract must be copied the web server images directory (in my case the Apache directory behind the “/i” alias. Time: 15 seconds.

F. Restart the application server

Restart tomcat. Time: 10 seconds

And we are done:

Thank you to the Oracle Application Express team. I can now create even more awesome applications, better integrated and faster than ever.

Martin

 

SQLDeveloper Datamodeller – Connecting data to processes

I have been looking for a way to specify which data is actually used by a process in the SQL Developer datamodeller. Now its time to document it (so I dont forget).

Screen Shot 2017-12-23 at 12.32.01

SQLDeveloper Datamodeller – Data flow diagram

In order to link data with process, you must go through the following steps:

  1. Create a “Flow” between the process and the Datastore (red line with arrow)
  2. Edit the flow, and create a new “Information structure”
  3. Edit the information structure and navigate to “Attributes”
  4. At last you can choose entities and attributes that should be linked to the process (use the + sign).

 

Screen Shot 2017-12-23 at 12.40.26

After making this connection, the information will show up in the process attributes also:

Screen Shot 2017-12-23 at 14.35.12

Happy modelling.

/Martin

 

Autogenerating APEX applications from SQLDeveloper process models – using Blueprint

Since the introduction of the new APEX “Blueprint” feature (currently only available on apex.oracle.com), we can create APEX applications using a “blueprint”. The blueprint can be supplied in JSON format which opens up a number of great possibilities. As far as I know, the Blueprint feature will be available (outside apex.oracle.com) in the upcoming 5.2 version.

Screen Shot 2017-11-23 at 19.34.33

This blog will outline the overall steps for autogenerating applications from models. Everything mentioned and used in this blog is available on Github:

https://github.com/martinbnielsen/apex-sqldeveloper-lowcode

APEX Blueprint enables generation of applications with a lot of add-on features (Access control, Help pages, Email templates…), but also the ability to pre-define the pages containing our table driven page components (grids, IR, forms…). Below is a snippet of what this looks like in the blueprint JSON:

“pages”: [
{
“page”: “10”,
“pageType”: “blank”,
“pageName”: “Home”,
“pageIcon”: “fa-home”,
“pageIsHomePage”: true,
“help”: “”
}
,
{
“page”: “20”,
“pageType”: “interactiveGrid”,
“pageName”: “Departments”,
“pageIcon”: “fa-table-pointer”,
“reportImplementation”: “TABLE”,
“interactiveGridIsEditable”: true,
“table”: “DEPT”,
“help”: “”
}

So, now that we can auto-generate our APEX applications from JSON, all we need is to convert the processes from our design phase into JSON.

This is where the Oracle SQLDeveloper Datamodeller features comes in handy. The best kept secret of SQLDeveloper, is the wealth of features for designing both the data and process model. You can access “models” via the menu : “View -> Data Modeller -> Browser“. In my example I have designed the process model containing composite processes and primitive lower level processes which represent the functionality I want in my APEX application:

Screen Shot 2017-11-23 at 19.52.20

In order to map the model processes to APEX pages, I have added som JSON annotations into the process notes, like this:

The are many variations of these JSON properties in the new Blueprint format. The best way (for now) is to try out difference page types using the APEX Blueprint creation page, and then check out the JSON that is generated from this.

Once the process model is defined (drawn) in SQLDeveloper, it can be exported to a Reporting Scheme (a database schema of your choice, which will contain all the information from your design models). Here is a script for creating the schema for this:

create_reporting_schema.sql

Once the reporting schema is created, we can transfer all information from the SQLDeveloper models into this schema (DMRS_xxx tables). The export into the reporting schema is performed by:

To extract relevant information the these DMRS_xx tables, please see these database views which contains examples for extracting process and datamodel information:

lowcode_views.sql

Ex.:

create or replace view lc_processes as
select d.design_ovid, d.design_name, date_published, published_by, de.diagram_name, p.process_id, p.parent_process_id, p.process_name, p.process_number, p.process_type, t.text, t2.text note
from lc_designs d
join datamodel.DMRS_DIAGRAMS de on DE.DESIGN_OVID = D.DESIGN_OVID
join datamodel.dmrs_processes p on p.diagram_id = de.model_id and p.diagram_ovid = de.model_ovid
left join datamodel.dmrs_large_text t on t.ovid = p.process_ovid and t.type = 'Footnote'
left join datamodel.dmrs_large_text t2 on t2.ovid = p.process_ovid and t2.type = 'Note';

(Remember to grant SELECT on the DMRS tables you want to query from your application schema).

Now that the model information is available we can create some PL/SQL which extracts the model information and generates JSON in the format that the APEX Blueprint expects. In the Github project (apex directory) you will find a application called:

f105_reporting_schema_app.sql

The application will generate Blueprint JSON for the model design of your choice using this PL/SQL package (procedure generate_blueprint_json). Basically this PL/SQL procedure is traversing the processes from the reporting schema and generating JSON (using APEX_JSON), mimicking the JSON which is generated by the APEX Blueprint generator (apex.oracle.com -> Application Builder -> Create -> Blueprint).

LC_TOOLS.pkb

Pasting the generated JSON output into APEX Blueprint will create the starting point for you application directly. Here is an example:

Summary

Even though it will probably never be possible to generate the complete application based on a process model (and some annotations in this), it gives us a pretty good starting point, having the menu structure and basic pages generated for us. This lowcode approach means we get the following APEX elements without even starting the APEX Application Builder:

  • Authentication
  • Navigation Menu
  • Navigation Bar
  • APEX Pages (Editable grids, Interactive reports, Forms, Calendars), already pre populated with the table and other options you have entered into the process description (JSON annotations).
  • Much more – provided by APEX Blueprint

It will actually be enough if the application is a simple table maintaining application :-).

I wonder if the future will bring even more SQLDeveloper Modeller / APEX integration. One can only hope.

/Martin B. Nielsen

IR – Single records view in multiple columns

The APEX Interactive reports (IR) are great for providing easy data (view)  access for end user. Under the “Attributes” section for the IR region, the “Single record View” can be enabled, providing a more detailed view of 1 specific (selected) record:

Single record View – Standard

If you want a better overview, this can easily be achieved by adding the following CSS (Themeroller, CSS file…). Please adjust the widths/padding to your own needs:

.a-IRR-singleRow-row {
 display: inline;
 }

.a-IRR-singleRow-name {
 width:200px;
 }

.a-IRR-singleRow-name, .a-IRR-singleRow-value {
 padding:8px;
 }

.a-IRR-singleRow-value {
 min-width: 300px;
 }

Then users will see the single record view like this instead (showing multiple columns – depending on your screen size):

Single record view – after CSS modification

Happy APEX developing

Martin

APEX 5 – Oracle JET Chart integration

Video

Oracle JET is the new Oracle Javascript framework, based on many well establishes JS libraries, bundled together, and added visualisations etc.

I really like the new chart types which adds a lot of control to developers and also gives the end-users some eye candy (see the movie).

I wanted to include these charts in an APEX 5 project, so in this first POC I did the following:

  • Downloaded the Oracle JET Demo which included relevant JS libs and CSS
  • Copied the Oracle JET directory to my webserver
  • Created a region type plugin to render the chart
  • Included the relevant CSS and JS files in the plugin
  • Added HTML code and onload javascript code in the plugin (ex. http://www.oracle.com/webfolder/technetwork/jet/uiComponents-barChart-default.html)
  • Changed toolbar images to use icons from the Font Awesome library

My initial version of the plugin looks like this now:

 

The next step will be to implement:

  • AJAX Data fetch
  • Handling of refresh events (Dynamic Actions)
  • Parameters to control toolbar and other Oracle JET Chart options

Oracle JET Charts will (as far as I can read) be available in APEX 5.1. I am looking forward to this, because these charts seems to have a lot of potential.

Will post more when I progress further with this plugin.

ODTUG Kscope 15 – Lessons learned I

Safely returned to Denmark after a great ODTUG Kscope 15 experience with lots of fantastically talented APEX entusiasts from around the world.

Kscope 2015

We (my college Tine and myself) basically attended all the APEX sessions we could during the conference. I will try to sum up the essence of what we leaned in a number of blogs which will be published over the next week or two.

APEX Episode 5:  A New Frontier

Joel Kallman held a great and very motivating session where he smashed the myth that APEX cannot be used for enterprise grade application development.  See more under #letswreckthistogether. Joel referred to a great quote from Billy Verreynne:

What does any business application deal with? DATA! That is the core. That is what drives the business. Applications come and go. Data is forever. Where does the data live? In the database. The database is the core. The database has been that since the 80’s. Is still that. Focus on the core. Design for the core. Leverage the core.

Having established that, where and what and how is the best way to deal with the data? INSIDE the database. Not in Java Beans. Not outside the database where you have to re-invent the wheel ito concurrency controls and caching and all that. What the hell for!? Can a bunch of Java Beans provide better caching and concurrency than Oracle? Of course not!

Thus the fastest and most optimal way to deal with DATA, the core of the bussiness, residing in your Oracle Data Server, is using PL/SQL

You can read the whole conversation here: https://community.oracle.com/message/885538

Joel actually has a very good answer to this:

Billy,

1) You will be called a dinosaur for such archaic thinking.
2) You rule.

Joel

It is great that there is a movement for spreading this important message so we can have more APEX applications in the world :-).

Interstellar: The Universal

Shakeeb Rahman from the Oracle APEX development team walked through the new universal theme (42), and talked about the conversion from existing themes, and how to use the Theme Roller.

When converting from other themes (Theme 24, 25, and 26), a migration guide is available here : apex.oracle.com/ut

Under the migration guide, a Bookmarklet is available to assist when mapping templates. This bookmarklet is a piece of Javascript code which can be used during the migration. To install this, please follow the above link and navigate to the “Bookmarklet” under the “Migration Guide”. To install the bookmarklet, drag the bookmark into your bookmarks bar.

Stepping through the migration, it looks like this:

2015-07-12 02.28.53 pm

 

Note the selection of “Reset Grid” and “Match template classes”. The match is set to “No” because this will be done by the bookmarklet.

2015-07-12 02.37.54 pm

See more details here : https://apex.oracle.com/pls/apex/f?p=42:2000:::NO:RP::#

Theme Roller

Shakeeb also demonstrated how easy it is to use the Theme Roller. The way to work with UI customisation has drastically changed in APEX 5. Where in earlier versions we were creating new CSS content in order to customize, now this is all done visually using the Theme Roller:

2015-07-12 02.41.08 pm

Once the desired colours has been set using the colour wheel, the new Style can be saved. To make this new style permanent, this should be set in the “User Interface” setup in APEX:

2015-07-12 02.42.16 pm

Next blog in a few days…

Enjoy

Martin B. Nielsen

 

APEX 4.2 Sneak preview – Mixing user interfaces

Playing around with the APEX version 4.2 (pre-production) on apex.oracle.com, I noticed that the sample application can switch between a web and mobile user interface (using the “mobile” navigation item:

Looking at the link to the mobile version of the application, it links to a page in the same application (as the web version). In this case, the “HOME_JQM_SMARTPHONE” page. This means it is possible to mix several User Interfaces in the same application. For now, the possible interfaces are “Desktop” and “jQuery Mobile Smartphone”.

The setup is done under Application Properties, in the new User Interface tab.

The result is a mix of desktop and mobile pages in the application:

The application can even be set up to “auto detect” the device which connects to the application.

The new concept of “Global pages” (instead og page zero), now allows us to create a global page per user interface (one for Desktop and one for the Smartphone).

Nice and very useful functionality for the APEX 4.2 developers I am sure there are developers out there who will want to separate the desktop and mobile into 2 application, but to have the freedom of mixing will allow us to deliver a complete application (in one packaged .sql file).

I cant wait until the 4.2 version is released !.

Dear customers, please call us and order lots of new desktop/mobile applications 🙂

 

 

APEX Best Practices

When building many APEX applications, it is good to think about some best practices concerning as you go along.

APEX is so easy to get started with, that many developers just start an application, as a prototype, and gradually this application is adapted by the end users, and “glides” into a production state. Thinking about at few simple guidelines during the development phase, can really help make the maintenance/enhancement phase a lot easier.

I have divided the best practices into 3 areas: Installation, Pre-development and Development. Many of the practices are learnings from APEX books, seminars, and experience from many APEX projects.

Installation

The APEX installation is fairly simple, however here are a few tips:

  • Install APEX in its own tablespace (I always install APEX objects in a tablespace called “APEX”). This way the APEX objects can be backup up separately if needed, and it is easier to control the growth.
  • The same goes for tablespaces for your APEX projects. I create 1 tablespace per APEX workspace (before reating the workspace). The database schema for the workspace should be created like “CREATE USER APP1 DEFAULT TABLESPACE APP1”. After creating the database user, create the workspace (based on the existing user).
  • Concerning choice of web server, the new APEX listener is really great for serving the dynamic APEX content from the database. However running the listener in “stand alone” mode should only be used when developing -not in production. I have seen that rendering static files in the standalone mode is not optimal.
  • Concerning your own (custom) static files, it is best to keep these in a separate directory on the server. Remember that when APEX is updated, the APEX image catalog will be replaced with files from the new installation.

Pre-Development

When starting APEX development, the following advice is good to remember:

  • Create seperate user accounts for the developers. At some point it could be necessary to see who changed what.
  • Setup “User Interface Defaults”. This setup is performed on table/column level, and is only necessary to do once. Setup as much as you can : Prompts, Help text, lengths, item types etc. These values are used when creating new components (regions, fields..) using the APEX wizards.
  • Determine the strategy for re-use. If the application components (List of values, templates…) can be re-used in other applications, you should consider created a “master” application containing re-usable components. The new application should then “subscribe” to the components in the master application.
  • Determine which theme to use. If all applications used in the company should have the same look and feel, consider setting up a company specific theme. Select one of the standard APEX themes, and modify this to your company needs.
  • Design the data and process model. The SQL Developer tool from Oracle includes the “Datamodeller” which can be a great help in creating these models. For larger applications, consider integrating the model with the APEX application. This can be achieved by exporting the model from SQL developer (into a “Reporting schema”).
  • Decide the security model for your application. A number of Authorization schemes should be created, and can then be used when creating new tabs, pages and other APEX components.
  • Decide the strategy for deployment between environments (dev/test/prod). Basically the APEX application (and database objects) can be deployed manually (using the standard DDL files, program files and APEX .sql file), or “packed” into a single .sql using “Supporting Objects”. The build in supporting objects is most useful, when the same application should be deployed on multiple environments (ex. when selling the application to multiple customers). If manuel deployment is chosen, consider creating a install.sql file which created database objects, and deploys PL/SQL program modules etc.
  • Set up version control. This is always a point asked by new users, since APEX version control differs from many other development environments. Know this: APEX has build in tracking (and reports) to show which developer changed what at wha time. The standard file artifact from APEX is a single .sql file containing the complete application. This singe file can be saved in a standard version control system (CVS, SVN…) however is can not really be used to show the application changes (its just a bunch of PL/SQL calls which builds the application). Consider using the APEXReportSplitter if there is a need to store and track  each APEX component in a file based versioning system.

Development

During the development phase, these tips can make life easier:

  • Place all PL/SQL logic in database packages. This way it is much easier to maintain the code, and use IDE’s which are best at coding in PL/SQL. Another benefit is that the stored PL/SQL program units can be re-used by other programs. It means keeping the presentation logic seperate from controlling and domain logic (MVC pattern). Note that SQL Developer has a tool for migrating PL/SQL code in APEX to packages.
  • Plugins can be downloaded from several sites, and used in the APEX application. This is a great way to improve the look and feel of the application without spending a lot of development time. The sites are APEX-plugin.com and the Oracle plugin repository.
  • When find yourself creating the same components several times, consider building your own plugins. Company wide plugins are highly efficient for keeping the same look and feel, and then the code has en single place of maintenance. When creating these enterprise plugins, the PL/SQL code behing the plugin can be placed in database packages (in a common database schema). So even if the plugin instance is loaded into many applications, the code behind the plugin can be maintained in one place (per database).
  • Page Zero: Another way to re-use with the application, is to place components on Page Zero. You can use the “Condition” to specify which page(s) the component should be displayed on.
  • Use Dynamic Actions instead of home made javascript. Dynamic Actions are declarative, which means other programmers can see that the logic exists on the page (instead of having this hidden as custom javascript on the page).
  • If you do resort to custom Javascript, consider using JQuery, so there is a better change of cross browser compatibility.
  • One of the greatest advantages in APEX is the Interactive Reports (IR). Use this whenever you can, because it works great for the in-experienced user, and at the same time gives great reporting capability to the more advanced end users. However take care concerning the query behind the report. Especially function calls in the column list can really drain the CPU.
  • Aliases: Use aliases for applications and for the pages which are linked directly by users. The application and page id can change, so a link containing aliases are better.

More on best practices (in Danish) here.

Happy APEX Developing, and let me know what should be added to the list 🙂

Martin B. Nielsen