ORDS 3 Early Adopter – First Look

This article builds on the ORDS blogs I have been writing but moves forward a little to look at the ORDS version 3 early adopter and specifically getting it up and running.

ORDS version 3 is a big leap forward for the product and supports much more than earlier releases.  Some of the new features include:

• Support for Oracle NoSQL Database technology (data can be created, queried and updated using REST technology)
• JSON document store
• Auto generation of REST endpoints (this allows data in tables to be used via REST)

Along with this is the much heralded simplification of the ORDS installation and to that I would say yes and no.

Yes there is a simplified installation path which can be used however if you have installed previous versions of ORDS right up to version 2.0.10 then you may actually find this does not help.

The two different installations can be invoked via the command line and this is the first real difference.

The previous installation was called from the command line java –jar ords.war in this version however this calls the simplified installation.  To call the standard installation the command is java –jar ords.war install advanced which will run through the install you will be used to from previous versions.

Let’s then look at the simplified install, it is 6 steps as opposed to 10 and does fast track and make a few assumptions which, if you are installing locally to the same machine as your DB with a fairly standard install, should run through without problem.

The problem I hit was with an error about port sharing which I struggled with as this install does not let you specify a port like the advanced path does.

Every time it tried to start I got the same error:

The previous installation was called from the command line java –jar ords.war in this version however this calls the simplified installation.  To call the standard installation the command is java –jar ords.war install advanced which will run through the install you will be used to from previous versions.

Let’s then look at the simplified install, it is 6 steps as opposed to 10 and does fast track and make a few assumptions which, if you are installing locally to the same machine as your DB with a fairly standard install, should run through without problem.

The problem I hit was with an error about port sharing which I struggled with as this install does not let you specify a port like the advanced path does.

Every time it tried to start I got the same error:

2014-12-11 13:26:50.891:WARN:oejuc.AbstractLifeCycle:main: FAILED ServerConnector@3d1f468f{HTTP/1.1}{0.0.0.0:8080}: java.net.BindException: Address already in use: bind

As you can see for the section in bold it was 8080 that was in use and it took some help but I finally realised that the XDB listener was listening on this port.  The installer is also trying to use this default port so you need a way to move this listener and that can be done with the following SQL.

C:\>sqlplus /nolog

SQL> connect

Enter user-name: system

Enter password: <enter password>

 SQL> Exec DBMS_XDB.SETHTTPPORT(4040);

This presumes that 4040 is a free port and will not cause any issues you can of course choose another port if you need to.

This will then allow your ORDS 3 to run and listen on port 8080 from which point you can start to investigate the powerful features of the product which I will expand upon in articles to come.

Personally I still like the more complex but more configurable options offered by the advanced installer which admittedly is a few more steps but you can specify your own port and avoid the clash I encountered.  The advanced version will also support a more complex architecture which may well be needed in many organisations.

ORDS No Ordinary Tool

This article is the first in a series which will look at the Oracle REST Data Services (ORDS previously known as the APEX listener) and APEX and the features that you can unlock.

This first article will examine at a high level what ORDS is and what it offers and explain the fundamental concepts.

ORDS is a Java EE-based alternative for Oracle HTTP server which allows a great deal of functionality including command line based deployment and configuration, enhanced security and most importantly RESTful Web Services.

ORDS can also be deployed using technologies such as WebLogic, Glassfish and Apache as well as from the command line as previously mentioned.

This articel will look at ORDS as a standalone service as this is the most basic installation and offers most of the powerful features of ORDS without having to go through the more complex installation.

The first thing to note is that the standalone installation is not supported in production environments and you will see this message when you start it up.  This is purely due to the fact that it does not support HTTPS and load testing has not been carried out by Oracle so they are not able to benchmark the level at which support would be offered.  However when you start up it is the easiest and quickest way to get things running, prove the concept and then deploy into another container.

When running standalone ORDS utilises the Oracle Grizzly as the framework which offers a stable and well established JEE environment.  This is a component of the Glassfish application and as such offers some but not all of the functionality and high end stability.

However there are a lot of features that you can use in the latest versions of ORDS, it is now even possible to administer the APEX listener from SQL developer which is a very powerful tool and allows you to validate your changes before finalising them.

So what does ORDS actually give me is the question that many people ask, well once you have configured it and got it up and running in standalone mode on your machine you can start to investigate this.

Let’s first look at what ORDS actually stands for Oracle Representational state transfer (REST) Data Services.  This means that your Oracle Database can be exposed to the Restful Web and also be the PL/SQL engine that APEX uses.

REST is now the dominant Web service design model and has helped to push out SOAP and to an extent WSDL as it is simpler to use.  Basically it is a set of principles around system resources and how they are transferred over HTTP using many clients and multiple languages.  This means you open up the ability to interact with web resources massively giving a real advantage when it comes to interoperability.

Another key is the concept of stateless requests, in that complete independent requests are sent which include all the data needed to fulfil the request so modern computer topology may see it forwarded, routed, load balanced or failed over without any state needing to be held at intermediate steps to ensure the request still makes sense.  This greatly simplifies the server side component design as without a state to synchronise with an external application a layer of complexity is removed.

In essence then what ORDS offers is the ability to interact with your database, and a number of other Web Resources in a simplified manner without the need for some of the more complex and expensive hardware and software implementations.

The installation is a relatively easy thing to do needing a download of ORDS from here http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html and some fairly straightforward configuration in the form of SQL and XML.  After that point it is a case of running as per a standard java war file.

This will run a standalone version of ORDS as a basic listener, in the following articles I will go into the configuration, deployment into other Web Services, use with APEX, advanced uses and what I see future uses and integrations are shaped like and will be utilised.

Replacing Discoverer with APEX and BI A Perfect Match

I have already looked at the fact that APEX is a perfect replacement for Discoverer in an earlier blog post and that inbuilt tools such as the powerful APEX interactive report give an update to what can be done with Discoverer.  The fact that your developer can write one report, hand it to ten different people and they can use it in ten different ways is incredibly powerful.  The need for development every time a report is required is removed and this is a time and cost benefit for every business.

Added to that is the fact that administration and the need for client installed applications are also minimised as the administration is much simplified compared to Discoverer and the reports will all run in a browser.  This also unties your business from having to build machines which will work with the current client install of Discoverer.

Some businesses may just want to use Discoverer style reports but gain the massive benefit that APEX is fully in support and being updated by Oracle as a core technology which Discoverer is not and this is something that can be done easily.

Discoverer Report

APEX Classic Report

 As you can see from the above images the familiar reporting style can still be utilised in your business while also leveraging the lightweight web browser based and fully supported APEX technology and this can be a good starting point towards future developments.

There are however a lot more businesses that want more from their reporting solution and the ability to do things such as produce printed collateral, formatted emails and dashboards from their information and this is where the technology gap occurs.

What do I mean when I refer to the technology gap?  I am referring to the fact that until recently the requirements above could only really be met by moving to something like Oracle’s OBIEE product.  Oracle Business Intelligence Enterprise Edition is a great product but it also offers a huge number of features many of which are just not required by many businesses looking to move from Discoverer.  For example the sending of formatted emails from reports was previously the domain of BI Publisher using OBIEE iBots or Delivers as it is often called.  This works very well but it does mean an expensive purchase for your business for a product which will not be utilised to its potential.  Often the fact that a business is still using Discoverer and has not moved to OBIEE is an indicator that this realisation has come and a decision made.

This then leaves you stuck with a product which is either out of or moving out of support and no obvious alternative.  Some businesses are looking at other reporting solutions but these can be costly, hard to configure and not supported fully as they are not Oracle on Oracle or native technology so often it can be a tricky path to walk.

There is another answer and that is APEX and BI Publisher integrated which will ensure you get the results that you require and so much more.  Why is BI Publisher even needed?  The issue is that APEX reports look as they do out of the box, you get what you see in most cases and people want more and understandably so.  This is where the integration to BI Publisher comes in as it allows customisation of your data output meaning reports can be branded, text formatted and logo’s added.

You can go from a standard printed APEX report:

To formatted material which could be used in any number of situations and over which you have full control:

Signatures can be added, graphs from derived values used, formatted tables based on conditions and basically anything that you might want to add into a report output can be produced.

There are also some other massive advantages of using this  solution for example the printing is seamlessly handled and integrated into APEX, it is easy to extract from multiple data sources and report bursting is supported allowing splitting of reports and multiple delivery mechanisms.

The final point means that with the click of a button in APEX you can easily send multiple formatted emails to as many recipients as you require with no further interaction needed.  All of this is enabled easily and seamlessly as it leverages native Oracle technologies to talk to your Oracle database using fully supported methods.

This then shows why APEX and BI Publisher is genuinely an ideal match and bridges that gap between the Discoverer tool and the high end multi-faceted tool suits such as OBIEE.  This solution allows your business to bring a fresh and modern system to the production of data reports and formatted collateral which can be distributed how you choose.  Your business does not have to look at trying to use non Oracle reporting tools with Oracle thus simplifying support and development.  Finally it puts the control and power back into the hands of your business using a cost efficient, straight forward and powerful solution which you can utilise however suits you.

Apex and Oracle Discoverer

One of the most important business needs is the ability to report on data and often on an ad hoc manner producing reports and variations of them quickly and easily.  For many years the tool of choice has been Oracle Discoverer which is a tool-set for ad hoc querying, reporting, data analysis, and Web-publishing for the Oracle Database environment.

 Increasingly Discoverer has become outdated and Oracle Premier Support has ended for the Discoverer product.  This is coupled with the fact that the old style of the Discoverer Admin and tool set is not in line with modern needs and can be difficult to support and manage.

Some of the solutions available such as full Oracle BI can be cost prohibitive for many organisations so another solution is required and this can be found by using Oracle Application Express (APEX).

One of the most powerful features of APEX is the interactive report, which is a well-established feature leveraged by many who use APEX. It allows you to build a report once, and then allows end users to modify it to meet their needs, within controlled boundaries. If they want columns re-ordered, or filtered, highlighted, shown/hidden, etc., they can make those changes themselves, without waiting for an admin or developer to make these changes as was often the case with Discoverer. Despite the very powerful nature of these reports the slight downside, is that it still requires a developer to build the initial report, or update the query if someone comes up with additional columns that should be included.

To ease this problem teams within Oracle have created a new packaged application called “Data Reporter”.   This allows the creation of quick and simple ad hoc reports which suit business needs and can be presented as a simple report.

A dashboard style report can also easily be put together which reflects a number of different reporting aspects on one page.

The raft of graphing and data presentation options also far exceeds anything that can be done natively with Oracle Discoverer.

It is even simple to produce a calendar of upcoming activities or of dated actions inside your database using this tool.

The reports can also be produced in PDF format on the fly which is a must for most businesses when business data driven reports are being sent around.

All of the reporting and different styles can be created using simple to use wizards and previewed in a web browser before they are fully released.  The reports can also be bespoke to each user’s login based on the data sets they have access to which again makes the administration and on gong support far simpler.

The fact that there is no End User Layer issues which have often been the bane of many an organisation using Discoverer and the removal of a need for specific administration tools such as Discoverer Administration and Desktop also massively simplifies the process required to produce reports and this is another huge bonus.  Other issues such as reports created by a  user who may have left and now cannot be changed as they are still the owner also disappears when APEX is used instead of Discoverer.

Using the reporting in this way via APEX also allows you to leverage other aspects of the APEX capabilities and gives one place where this can be built and managed from.  This improves productivity and reduces both time to live and cost of implementation.

In conclusion it seems to be a relatively simple decision for many businesses who are seeing Discoverer at the end of support and increasingly hard to administer internally but do not want to spend on a fully BI system which full feature set they do not have a need for.    Oracle APEX can produce robust, easy to use and administer and modern looking reporting all with a relatively low outlay on software and training.  It is also under support and still being developed and improved and the development of the reporting from the initial stage can really drive business information where it needs to g

APEX and EBS Integration

APEX and EBS Integration

This article will explore the integration and extensions that Oracle Application Express (APEX) gives you when using Oracle E-Business Suite (EBS).

There are two main ways in which APEX and EBS are integrated and these are:

• Including and integrating EBS data inside APEX.
• Customising EBS using APEX.

The other main goal is that this integration is seamless to the end user, it should be the case of one homogenous offering and not disparate systems which users have to login to.  APEX has this covered because you can use the same authentication (who can login) and authorization (who can see what) within your Oracle Application Express applications that are used within your Oracle EBS installation.  Although this seems like a small thing it actually shows how integrated the two are and makes the system far more palatable for an end user and easy to administer.

It is even possible to register fully integrated applications within the Oracle E-Business Suite so they are available from within the Oracle E-Business Suite menus.

The installation of the system is also simple as can be seen from the Oracle recommended deployment architecture below.

APEX architecture

To look at option one and the EBS data it is fairly straight forward and really only requires a small bit of work to get things connected so that APEX can start to fully integrate with the EBS data.  The best way to do this is to use views which almost any EBS system will already utilise and are simple to maintain and to support.

From this point APEX can be used to either report on the information available and allow you to edit the reports how you want to see them as well as choosing the data displayed or you can directly update/insert/delete records either directly or through the fully supported route of API’s which are freely available.  This means you can have a simple to use permission based front end to your database which would normally cost a great deal in money and time to develop.

A good example of this is outlined using the HR API which is called to create an employee; this is a programmatic feature which would require a good understanding of PL/SQL to invoke as well as a specific tool such as SQL Developer or cumbersome old fashioned forms.  Using APEX a simple form can be created which will call the API in the back ground and allow you to edit or add records using this simple web form.

This can be made available as your business chooses and can be as simple as an update form to more complex update and delete functions, all called from an easy to use APEX form.

Integrated update email tool, called from the EBS menu.

From here a task which either used a direct database connection or called the Oracle Forms can be done using a browser and web page which can be edited and changed to suit your business.

To address point two some of what is mentioned above comes into play in that a big use for APEX and EBS is a modernisation of old fashioned Oracle forms which become increasingly hard to use as a modern work force has never experienced this kind of interface.  Oracle themselves suggest that with the ability to integrate the security and use of the applications this is a major plus.  Secondly the ways that APEX forms run is also lighter touch for most businesses as Oracle APEX runs Native HTML Web Applications <> Forms uses Java Applets to run on the Web

Complex EBS workflows can also be customised and simplified using APEX which can call parts of functionality inside EBS making it both much easier to use and also and often crucially easier to support and error trap.

Another and key reason that APEX is a tool of choice for extending or modifying EBS behaviour is that it is supported.  For example if often to produce extensions or changes to EBS would have to be signed off by Oracle or taken as non-standard custom code.  Using APEX this is not the case as you are creating APEX applications to call standard functionality working in the way that you want.  This can be anything from different business logic for different countries to bespoke data presentation and use from the EBS system.

An increasing and major need for businesses is that of mobile applications and this is an area where APEX excels like no other.  Mobile applications supporting almost all major handhelds can be rapidly developed.  Then leveraging the ability to integrate with EBS and the API’s business processes and tasks can be carried out remotely using these applications and the inherent security that exists for the web applications.

This is an area which will just continue to grow and is currently only seeing the embryonic stage of where it can go.  EBS can be updated, workflows kicked off and completed all via mobile applications which can easily be created and managed.

This shows some of the ways in which EBS can be both integrated and extended using APEX, there are constantly new ideas and ways to use this technology being developed which allows any business to keep a modern looking system which is easy to use, easy to administer and provides a rapid and cost effective way to deliver.