Antivirus service for APEX

OK – so now its time to launch the Filescan” service I have been nursing over the last couple of months. The service is a a cloud based SAAS solution written in APEX, PL/SQL, Nodejs and Python (yes, I love programming languages) :-).

If you want to skip the rest of the introduction, and just try it out – here is the address:

https://filescan.mbndata.dk

The problem this service is solving, is one faced by many of my customers who have internet facing solution (ex. APEX Applications), which allows uploading of various types of files. These files could be infected by viruses, malware, trojans etc., so optimally we want to block these files so they never enter our internal servers, and are never opened by our internal user.

This is what Filescan does – blocks infected files during the upload process, and BEFORE they reach your databases or file systems. The service is REST based, which means you can use it from virtually any client or programming language. We supply “out of the box” calls for APEX and PL/SQL for ease of use in your Oracle based solutions.

Runs as a cloud service

The service i a SAAS service in the cloud (Frankfurt). That means you dont have to install any local server software, handle virus database updates etc. A simple REST call to the service will scan the file and return the result. The file scanning software used is ClamAV^®, which is an open source antivirus engine for detecting trojans, viruses, malware & other malicious threats. More details about ClamAV here: https://www.clamav.net/. We update the ClamAV virus definition database 4 times a day.

Adding protection to APEX Applications

Filescan currently supports virus detection of files uploaded to APEX_APPLICATION_TEMP_FILES. To add this protection to you APEX applications, there is a simple PL/SQL API which is supplied as part of the service. Ex:

The above is implemented in a page process (PL/SQL), which runs before your code that stores/uses the uploaded files. The PL/SQL API (fs_apex_api_pkg and fs_api_pkg) can be downloaded, once you sign up for the service.

The service supplies API’s for Oracle PL/SQL and Oracle Application Express (APEX), however you can use the service from any tool/language, which supports REST service calls.

Monitoring uploaded files

Once the above code is implemented in your application(s), you can be notified (preference) if users are attempting to upload infected files:

We also supply a dashboard for monitoring the file uploads:

And also a searchable list of uploaded files:

Usage and support

The service is currently supplied as a “free” service, which you are welcome to try out on as many applications you would like. If you experience any problems/issues with this, we will fix these, and also keep the service uptime as close to 100% as possible. We can be reached at: support@mbndata.dk.

Plans are to make this service available as a commercial supported solution, once we have evaluated the feedback from users.

Hope you will enjoy the solution, and hopefully it will provide a safer environment for you APEX applications.

To learn more, visit: https://filescan.mbndata.dk

/Martin

Advertisement

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:

Live template options

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

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

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:

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:

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).

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).

 

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

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.

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:

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

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.

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:

 

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

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:

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:

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 🙂

 

 

250.000 APEX Udviklere

Antallet af Oracle APEX udviklere på verdensplan er oppe på 250.000. Produktets popularitet og udbredelse bliver ved at at stige i takt med nye versioner af APEX, samt en stigende popularitet. Se mere her.