Accessing SSL encrypted websites using UTL_HTTP and Oracle Wallet Manager

Introduction
If you have used the UTL_HTTP package in PL/SQL to call upon external web pages or services, you might have seen following error message come by:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1130
ORA-29024: Certificate validation failure

It indicates that the web site you are trying to access is in fact SSL encrypted and requires a valid certificate to read.
Most modern browsers download this automatically when visiting any encrypted page, but to do this in a PL/SQL procedure, a couple of manual steps need to be taken.

This is where Oracle Wallet Manager comes in. It is shipped with the DBMS software and can typically be found in the $ORACLE_HOME/bin folder.
What this program does, is to facilitate the process of storing certificates in a single file in PKCS #12 format, called a Wallet.
Next, it suffices to simply add a reference to the wallet in your PL/SQL code to be able to use these certificates when accessing SSL encrypted content.

Sample case
Let’s take the following piece of PL/SQL code:

DECLARE
 lo_req  UTL_HTTP.req;
 lo_resp UTL_HTTP.resp;
BEGIN
 lo_req := UTL_HTTP.begin_request('http://www.google.be');
 lo_resp := UTL_HTTP.get_response(lo_req);
 dbms_output.put_line(lo_resp.status_code);
 UTL_HTTP.end_response(lo_resp);
END;
/

This will output the status code “200”. This implies the page returned HTTP 200: OK, indicating the request succeeded.

If you change the URL from http://www.google.be to https://www.google.be, the same command would raise exception “ORA-29024 Certificate validation failure” as mentioned earlier.

Wallet Manager
From the command line, start the wallet manager by simply entering ‘owm’ after having set the correct Oracle environment parameters.
In Windows the program can be found in the start menu under /<oracle version folder>/Integrated Management Tools/Wallet Manager.

Wallet_01
First, let’s create a new wallet.
From the menu, select Wallet, New…

A prompt is shown to enter a password for the Wallet. For this example, we’ll use “test1234”.

Wallet_02

After entering the password, a prompt asks to create a new certificate request, which we will not do for now.

By default, a number of trusted certificates are created within the wallet. These can be seen in the overview screen.

Wallet_03
Now, let’s focus on our google.be website.

First thing to do, is to export the actual certificate from this website, together with the rest of the trust chain. The easiest way to do this is by using a regular browser application. Following screenshots are from Firefox, but a similar approach can be taken with Internet explorer or Chrome.

Go to the website, and click on the padlock in front of the URL.
Wallet_04

Click on “More information” and then “View certificate”. In the Details tab, export each of the certificates in the hierarchy (3 in this case).
Wallet_05

Then go to the Wallet manager, and import all 3 trusted certificates.
Wallet_06

Finally, save the wallet to a desired location. Make sure it’s a folder that can be accessed by the Oracle user, and is preferably not accessible by unauthorized users!
In this example, /home/oracle/Wallet/ is chosen.

Wallet_07

Wallet_08

Now we can return to our SQL*plus session. One extra statement should be added to the script:

UTL_HTTP.SET_WALLET (‘file:<path to the wallet folder, don’t include the filename!>’,<the wallet password>);

This statement should be executed before the begin_request step.

Here’s the full output of the script:

[oracle@myorcl12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 27 14:11:43 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lo_req  UTL_HTTP.req;
3    lo_resp UTL_HTTP.resp;
4  BEGIN
5    UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234');
6    lo_req := UTL_HTTP.begin_request('https://www.google.com');
7    lo_resp := UTL_HTTP.get_response(lo_req);
8    dbms_output.put_line(lo_resp.status_code);
9    UTL_HTTP.end_response(lo_resp);
10  END;
11  /

200
PL/SQL procedure successfully completed.
SQL>

In a follow up post, I will explain how to achieve the same result using the orapki command line utility instead of Wallet manager.

Oracle Reports Generation with Docufy

In a previous blog post we explained already why we decided to build our own report generation tool. In this post we will explain the main principles and components the tool consists of.

First of all, Docufy is a tool for developers: for APEX developers … surely, but in fact for anyone who is developing against an Oracle database and who needs a printable output. In the current version, the printable document is a .DOCX file.

How does it work?
Docufy consists of two major components:
1) a front-end application (UI), used for defining the metadata that describes the documents. The actual UI is written in APEX and is available in all 4.x versions. We are also working on a Eclipse-RCP version of the UI.
2) a PL/SQL engine, which contains all the intelligence and generates the desired output

1) The APEX UI
The main objective of APEX UI is to give the developer an easy interface to configure his document. There are different screens where you can enter the metadata of your ‘printable’ report. This metadata consists of:
- one or more SQL statements
- minimal 1 Word-template (DOCX)
- and optionally input parameters

Following screenshot gives you a general idea of how the UI looks like:

APEX-UI

2) The PL/SQL Engine
The PL/SQL Engine retrieves the metadata which is defined via the UI as input for a given document. It executes the SQL statement(s), binds them with the parameter(s) and gets the specified DOCX-template.
Thereafter, this template is unzipped. As you may know, a DOCX-document is an amalgam of different XML-files. The engine replace all data tags in the document.xml file by the corresponding business data.
At the end all XML-files are packed again in a DOCX-output-document, which is returned as a BLOB variable to the calling program.

Following picture gives a schematic overview of the concept:

So, summarizing:
Docufy is a RAD-tool for generating operational reports. With its intuitive APEX UI, you easily configure your documents by adding DOCX-templates and SQL-queries. The engine is written in PL/SQL, which makes installation, integration and maintenance a walk in the park. The tool comes as a packaged application for APEX 4.x.

Follow @docufy on twitter
www.docufy.be

Are You The Smartest Contest (AYTS) 2013

This battle for all the Oracle Partners in Belgium and the Netherlands is organized by Oracle (Yves Van Looy(BE) and Charles Billar(NL)). All employees of these Oracle Partner companies can compete to be the Oracle smartest of their company for that year. Just like last year, the contest was also aiming to provide an in-depth architectural knowledge about seven different technology tracks.
Like previous editions of AYTS, iAdvise joined this year’s  AYTS contest.

ayts
This year, 17 people represented iAdvise, and tried to win the prestigious price: a business travel to the Oracle HQ in San Francisco and Las Vegas.
Six out of seven tracks were covered by the iAdvise team: Security, Data Integration, BI & Applications, HW & Software combination, Service Integration and Database & options.

This year, iAdvise also improved the oraevents site which is used for this contest. Many new features and functionalities were added to improve the user experience of this tool.  The usability and new functionalities were clearly appreciated by all the contenders, all credits goes to the iAdvise apex team who really made this a very nice tool to work with!

Of course we also had a top 3 for iAdvise: Tuur made it as ‘nummero uno’ for the security track, second place went to David for the BI track and on the third place Kristof who also followed the security tracks. Congratulations to all three!

So we had some high scores on different tracks, which ones again proves that iAdvise is very proficient in several technologies.
But even more important was that Tuur got a gigantic high score of 39/40, something that nobody ever achieved before in the AYTS history and it will likely take some time before someone will achieve an equal or higher score.
He is without question the very convincing winner of this edition, and will enjoy his trip to the States! Congratulations Tuur!

Next year will be without question very exciting, since there are a whole lot of new technologies coming up our way and of course iAdvise will be one of the participating companies!

Checkboxes in editable reports in APEX

We have all been there, we need to create an editable report and one of the columns contains a checkbox. So how should you handle this?

If you are using one of the recent APEX versions the easiest way is a tabular form. Just edit the column attributes of your checkbox column and at display as select “Simple Checkbox”. At the list of values definition type “Y,N”, where Y is the value the column will get when the checkbox is checked.

Tabular form checkbox

But what if you have multiple editable reports that have this requirement on one page? Then it starts to get interesting, since you can no longer use tabular forms.

With multiple editable reports we will be making our own editable report by using the API APEX_ITEM. You can read more about the APEX_ITEM API here.

We first create a report, and in our query we add our “active” column. We create two items there using the APEX_ITEM API: a checkbox and a hidden item. The parameter p_idx is the number  that apex uses to identify the items and write them in an APEX collection when the page is submitted. This has to be unique on the page. We set the value of both items to the id of the column. Why we need these will become clear later on.

SELECT APEX_ITEM.HIDDEN(p_idx =>1, p_value => id)

             ||APEX_ITEM.CHECKBOX(p_idx => 2, p_value => id , p_attributes => DECODE(active,’Y’,’checked=”checked”‘, NULL)) active

FROM MYTABLE

Next we go to report attributes, edit our active column and set display as Standard Report Column. This will allow APEX to render this properly.

Column attributes for APEX_ITEM API

Before we precede let me explain how checkboxes work. In HTML a checkbox that is not checked has no value it is considered NULL. This is something you will have noticed when you create a checkbox page item in APEX in a form. So if we loop over our APEX collection containing the checkboxes we will only loop over the checkboxes that have a value. This is no issue when you only need it to delete rows, but let me show you what happens if you try to use it to update rows. Let’s assume we have two columns, one contains our ID, and one contains our Checkbox with value Y.

APEX_ITEM.HIDDEN(p_idx => 1, p_value => id) APEX_ITEM.CHECKBOX(p_idx => 2, p_value => ‘Y’)
1 Checked
2 Not checked
3 Checked
4 Not checked

Assume we then loop over our first collection and do an update statement in our table:

FOR i in 1..APEX_APPLICATION.G_F01.COUNT LOOP

UPDATE MYTABLE SET active=NVL(APEX_APPLICATION07.G_F02(i),’N’)

WHERE id = APEX_APPLICATION.G_F01(i);

END LOOP;

Looks correct doesn’t it? Well it isn’t. When our process goes over the first row it will update correctly. When he tries to update the 2nd row he will update it wrongly to ‘Y’. And the 3rd row will give an error. That is because our 2nd APEX collection only contains two rows. It does not contain the rows that are not checked.

So now that I explained the problem let’s have a look at the solution.

DECLARE

l_yesno VARCHAR2(1);

TYPE t_checkboxes IS TABLE OF VARCHAR2(1);

l_checkboxes t_checkboxes := t_checkboxes();

BEGIN

FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP

FOR j IN  1..APEX_APPLICATION.G_F02.COUNT LOOP

IF APEX_APPLICATION.G_F01(i) = APEX_APPLICATION.G_F02(j) THEN

l_yesno := ‘Y’;

END IF;

END LOOP;

l_yesno := NVL(l_yesno,’N’);

l_checkboxes.EXTEND;

l_checkboxes(i) := l_yesno;

l_yesno := ‘N’;

END LOOP;

 

FORALL i IN INDICES OF APEX_APPLICATION.G_F01

UPDATE MYTABLE

SET ACTIVE = l_checkboxes(i)

WHERE id = APEX_APPLICATION.G_F01(i);

END;

We start by looping over our APEX collection containing our ID, inside that same loop we loop over the APEX collection with our checkboxes. Both contain as value our ID. If the values match, then the checkbox containing that ID has value ‘Y’. We insert this into a PL/SQL collection that we made for this purpose.

Lastly we do an update in our table, to set our new values. Notice how we only did one update statement using FORALL, and by doing so we limited our context switch to just one, and boosted our performance.

I now hope that everyone got a better idea of how they can deal with checkboxes rather easy, using only PL/SQL and APEX API’s.

Bryxx has launched!

On Tuesday, May 7 the Bryxx launch event took place. In the beautiful setting of the Flandria boat, and in the presence of a large number of customers, we revealed the services of this new venture. As a joint venture between the iAdvise and Contribute infrastructure teams, Bryxx will specifically focus on the middleware field.
In bringing together both expert middleware teams, we will focus on
  • Opening up your business critical web applications to your intranet or to the internet
  • Making sure that these applications, deployed on your middleware stack, are secure on all layers of the underlying architecture. Security from-data-to-browser
  • Streamlining and automating your process of development towards production
  • Providing you with the opportunities to outsource the maintenance of your private middleware cloud or to outsource your entire private middleware cloud
From a technical point of view Bryxx will dedicate its expertise to 4 domains:
  • Oracle Cloud Application Foundation (with web logic as the main driver)
  • Oracle Identity & Access management
  • Oracle Database Security
  • DevOps
With respect to these 4 areas of expertise, Bryxx provides strong consultancy profiles on all levels (pre-sales, infrastructure architects, senior implementation engineers, etc) to design, install, configure, maintain and monitor your middleware platform as well as to streamline the process of application development towards your preferred middleware solution.
When you add our managed services and hosted solutions offering on each of these domains to this package, with strong partnerships in the backend, we believe Bryxx has a strong and complete offering for all your middleware challenges !Our team of 14 dedicated and experienced middleware engineers is ready for you.
Want to know more?
Visit us at www.bryxx.eu or contact us at info@bryxx.eu
 bryxx1bryxx2bryxx3bryxx4

Mobile Business Development seminar in Belgium and The Netherlands(4 and 6 June)

iAdvise organises a free seminar about Mobile Business Development on 4(Antwerp, Belgium) and 6(Breda, The Netherlands) June.

In this seminar we will discuss Mobile Development and Usability(UX – User Experience) in Mobile Development.
But we also compare different solutions, give pros and cons about: APEX Mobile, ADF Mobile and Tabris.

Of course we will also show you best practices, problems and solutions,…

More information(Dutch): http://www.iadvise.eu/seminars

Docufy: Document Generation for APEX made easy!

APEX is well known for his RAD solution and the no-extra cost factor: APEX makes it possible to build on time and within budget your database application for the web. But an application is more then building pages for CRUD operations. Very often you also need to generate well looking documents. And this is standard not possible with APEX. A pity!  Within the Oracle world, Bi Publisher is by far the best solution. But from the pricing point of view, it does not really fit in the ‘cheap’ philosophy of APEX. Further, you can also look for Open Source solutions like Jasper Reports and Birt. Both have a community version that you may use for free.

Nevertheless, we decided to build our own tool. During the presentation at APEX World of last week, organized by OgH, we explained how we came to this decision. On different projects at the Flemish Government we always had the business requirement to generate output for MS-Word (.docx). We first tried to solve this requirement with Jasper Reports. This was fine for simple letters. But the end-user also wanted to change some of those documents after generation … and that was not so easy to do with the output created by Jasper.

So the idea for an own “document generator for Oracle” was born. The main objective was trying to bring some of the better features of other tools together in one solution:

  • Separation of data retrieval and document layout.  The layout is template driven: your template is a MS Word document (.docx). This leaves the possibility open to involve business users in the design and build of the desired output.
  • Simple, easy and light: we want the solution to be as much as possible in the database, in PL/SQL, with no extra software on a middle-tier
  • Metadata driven and nice user interface to manage those data
  • Integration in your application via a PL/SQL API. This application may be developed in the technology of your choice. We use it initially in APEX projects, but it can be integrated in any tool that can do a PL/SQL call (Oracle Forms, any java solution, Formspider, …)

Our custom solution evolved well: we already used it successfully in different projects. Therefor, we decided to make it available for the world and packaged the solution in a tool with the name Docufy. You can find more information on the Docufy-site or you may always send a mail to info@docufy.be.

With Docufy,
we proceed on the APEX philosophy,
there where APEX it self stops:
easy and quick generation of operational documents.