Accessing SSL encrypted websites using UTL_HTTP and Oracle Wallet Manager

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:

 lo_req  UTL_HTTP.req;
 lo_resp UTL_HTTP.resp;
 lo_req := UTL_HTTP.begin_request('');
 lo_resp := UTL_HTTP.get_response(lo_req);

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 to, 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.

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


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.

Now, let’s focus on our 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.

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

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

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.



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 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 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2    lo_req  UTL_HTTP.req;
3    lo_resp UTL_HTTP.resp;
5    UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234');
6    lo_req := UTL_HTTP.begin_request('');
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  /

PL/SQL procedure successfully completed.

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

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.

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!

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 or contact us at

Red Gate Deployment Suite for Oracle, a valuable component in your APEX ECO-System

When working with APEX, you also need a number of tools to improve the efficiency of your work. It isn’t enough to just have the APEX IDE at your disposal. Besides the APEX IDE for the application development, you may use a bunch of other tools. This is what I mean by the APEX ECO-System.

For the PL/SQL development and creation of the objects, such as tables, views, synonyms, sequences, … you may use SQL Developer. The DML scripts with the code for creating those objects may be generated by SQL Datamodeler. This tool is used to design the data model, which offers a graphical representation of the relationships between tables and offers the possibility to generate database objects automatically on our database.  Both tools are part of the Oracle Database Development tools, just like APEX.

Once the objects are modeled and generated on the database, you can start with the development of the application in APEX. After some time, when a first version of the application is ready, it can be deployed on a test environment. Without the aid of an external tool, it’s necessary to make a script of each package, trigger, sequence, synonym, table, view,… in order to create them on the test environment. And when you start with change requests and enhancements, it becomes even more complex!

After the end-user has tested the application and given his feedback, you normally will have to change your packages, tables,… or you will have to add some new functionalities, or you’ll have to change the existing ones. Without the aid of an external tool, you’ll have to make a script of each change you make, in order to deploy the changes on test as well. This is very time consuming and the risk of making mistakes is very big.

In the past, in my personal experience, it took me lots of hours to make a script for each column definition, table, relationship, package, trigger, view,… which has changed during the development cycle. It was very annoying and especially when you forgot one… It’s very time-consuming to find differences between the development and test environment.
But, since I got in touch with Red Gate, all those problems are gone!

Red Gate’s Deployment Suite for Oracle contains a tool that allows you to deploy schema objects and compare schema’s (Schema Compare for Oracle). But that’s not all. Unlike other tools such as SQL Developer, which  only gives the possibility to compare schema’s in a limited way, the Red Gate Deployment Suite allows you to deploy Data from one schema to another as well (Data Compare for Oracle). And last but not least, since the 12th of March, Red Gate extended their product portfolio. They also provide a tool for Source Control (=Version Control) of your database code! This is done by Source Control for Oracle!

A small overview…

Schema Compare for Oracle, allows you to make a full installation script of your database objects. This is very useful because you don’t have to worry about the question if all modifications are scripted, neither about the possible dependencies between the different objects. You are sure that everything on your development environment is scripted in a good way and, when you choose to deploy everything on the test environment, you are sure that it is executed in the right sequence.  Everything which has to do with the deployment of the schema objects is handled with the Red Gate Schema Compare tool, which is included in the Deployment Suite.

While Schema Compare for Oracle is used for objects, Data Compare for Oracle will take care of all your data. This is very useful when you have a test environment with a lot of reference data, which is entered by the Business. Don’t you recognize the situation where your end users have entered data while testing the application in a test environment assuming that this data would also be available in the production environment? This tool allows you to compare data on different schemas, and to deploy changes from the one to the other.

Very recently, Red Gate added an extra tool to his portfolio: Source control for Oracle. It all started with a live lab at KScope12 where every attendee could contribute to the first prototypes. Read more about it here. Less than a year later, a first version is downloadable. Since it is completely new, there is not yet that much experience with it.

Since iAdvise is an official partner of Red Gate, we’ve had the opportunity to test the tool before its official release. After a few months of testing, we can tell you that this is the first mature tool which really helps developers to manage their database code!

The whole idea is to give developers a tool to put schema objects on an SVN repository from the database and to pull those objects from the SVN repository, back to another database. If you ever tried doing this manually, you know that this can take up a lot of your valuable time. Source Control for Oracle will take the manual labor out of the picture and put the files on the SVN for you. While doing this, Source Control for Oracle will notice if an object already exists on SVN and the tool will ask the user what he needs to do with it. This will decrease the chances of anyone overwriting your work. You can also add a comment when you push something to the SVN. You can always read back and see what was changed, why it was changed, when it was changed, and by whom it was changed. In this way, Source Control for Oracle provides a whole version control system for database code.

One of the nice features of the tool is that when you define all your schemas in a SVN repository, Source control for Oracle will compare your schemas in the database automatically with the definitions in SVN, when you start the tool.

As you can read, the Deployment Suite for Oracle isn’t just the first database tool you meet; it’s a lot more than that! At iAdvise, we’re convinced about the added value and we believe this tool will help a lot of people in their needs. If you aren’t yet convinced about the tools, then take a minute to look at the website of Red Gate and check out the videos and testimonials of other users, I’m sure you’ll change of thoughts!

Interested to see & learn more about iAdvise?
Follow us on twitter!


OBUG Connect 2013: iAdvise presentation on ADF & Web Services

On 26 March OBUG Connect, the yearly Oracle Benelux User Group conference, will be held in Antwerp.

iAdvise will be presenting about ADF and web services.
We’ll show you how you can expose your ADF Business components as Web Services.
But also how you can consume Web Services in your application.

The presentation is session 3 in the “Middleware track”(track 7) and starts at 15.45.
We hope to see you in Antwerp!


Slides of the ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”

A few weeks ago we did an ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”.

About 186 followed the seminar online.
Those people received a link to the recorded session and the slides of the presentation.

For those who couldn’t attend, these are the slides of the presentation: ADF Immersion presentation
But the presentation was a lot more than a few slides, there was also a demo(>30 minutes).
So if you want to see the full recording, you can see all past webinars as a full ODTUG member.

If you need more info on ADF methodologies and ADF best practices or want to ask questions about these topics, check out the ADF EMG group.

Starting with ADF 11G Logging

In software development, logging is an indispensible part of the job. When developing  java programs, Log4j (Apache framework) is probably the most commonly used framework.

But when we are writing Java programs, using the Oracle ADF framework, there is another option : ADF Logger. This logger is integrated in the Weblogic enterprise manager, and gives you the flexibility to adjust your log-levels at runtime. This blog post briefly demonstrates how the ADF logging works, using a servlet that logs at all levels.

The first example is run on a remote Weblogic server. At the end of the post the same example is run within the integrated Weblogic server of JDeveloper.

First we start with viewing the loggers defined in Oracle Enterprise Manager (OEM) before our servlet is deployed :


When we select ‘Log Configuration’ we get the Log Configuration screen, where some loggers are already defined and their loglevels can be configured :


We’ll deploy a servlet that uses the ADF Logger, change the loglevels at runtime, and check the logging to see what happens.

So fire up your JDeveloper and select a new Fusion Web Application (ADF) or download the JDeveloper project from

Then create a servlet like the one below, which is a simple servlet called ‘ExecuteLogger’ that logs a message on all loglevels :

package be.iadvise.loggerapp;


 import java.util.Calendar;

 import javax.servlet.*;
 import javax.servlet.http.*;

 import oracle.adf.share.logging.ADFLogger;

 public class ExecuteLogger extends HttpServlet {
  private static final String CONTENT_TYPE = "text/html; charset=UTF-8";

 private static ADFLogger _log = ADFLogger.createADFLogger(ExecuteLogger.class);

  public void init(ServletConfig config) throws ServletException {

 public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  PrintWriter out = response.getWriter();
  out.println("<head><title>Executing logging with ADFLogging and System.out.println</title></head>");

 Calendar cal = Calendar.getInstance();
  String loggingId = Long.toString(cal.getTimeInMillis());
  out.println("<h1>Executing logging with ADFLogging and System.out.println</h1>");
  out.println("<h2>Used Logging id = "+loggingId+"</h2>");
  out.println("<p>The logging id is different for each request.</br>Use the logging id to search the log messages in the logfiles or EM for this request.");
  out.println("<p>Logger Name in EM = "+_log.getName());

  out.println("<p>****************** START LOGGING ******************");

  out.println("<table >");

  _log.finest("Loglevel finest : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel finest : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>FINEST</td><td>Loglevel finest : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // FINER
  _log.finer("Loglevel finer : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel finer : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>FINER</td><td>Loglevel finer : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // FINE
  _log.fine("Loglevel fine : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel fine : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>FINE</td><td>Loglevel fine : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  _log.config("Loglevel config : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel config : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>CONFIG</td><td>Loglevel config : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // INFO"Loglevel info : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel info : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>INFO</td><td>Loglevel info : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  _log.warning("Loglevel warning : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel warning : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>WARNING</td><td>Loglevel warning : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  _log.severe("Loglevel severe : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel severe : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>SEVERE</td><td>Loglevel severe : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");


  out.println("<p>****************** END LOGGING ******************");


Then, map this servlet in your web.xml as follows :


The line

private static ADFLogger _log = ADFLogger.createADFLogger(ExecuteLogger.class);

will create an entry in the ADF logging panel of OEM  for the class ‘be.iadvise.loggerapp.ExecuteLogger’ during the first execution of our servlet.

So generate the ear file (in JDeveloper : Application -> Deploy -> your application) , and deploy to an ear , and deploy the ear to your remote Weblogic.

Then, execute the servlet by entering following url in a browser http://server:port/appname/executeLogger

We will receive the following output where the logging id is a unique id for every request (this will help us find the log info logged for every run) :


and in our log configuration screen, the logger for our servlet is added automatically with level WARNING :


When we look to the logging itself, by selecting the following :


We see that our servlet has logged 2 lines  : level WARNING and ERROR, as the logger was default created with level “warning”.

(We look in the log files for the id generated by the servlet to get our 2 lines)


Now let’s update our log level to TRACE (finest), press apply, confirm the update, execute the servlet again , and see what happens to our logging :


confirm the update :


execute the servlet again (it generates a new logging id)


look for the generated logging :


As we have set the loglevel to trace (finest), which is the lowest level, we see all the generated loglines.

Remark : When we undeploy the application, the logger will remain visible in the log configuration screen until the managed server is restarted.

Using the integrated Weblogic server in JDeveloper

This screen shot shows you how get to the log configuration and logging screen in JDeveloper :


After executing the servlet locally, the Oracle Diagnostic Logging will show you the following :


and the analyze log :


then we change the log level :


and execute the servlet again and watch the output :


That’s it !!!

The big advantage of ADFLogging is the update of the loglevel at runtime, so whenever something seems to be going wrong, just set the loglevel to e.g. TRACE and check the logfiles. When the problem is solved reset it to WARNING or ERROR. And this without having to restart your application.

Also, being able to view and search the logfiles using the EM increases the userfriendlyness of this system. You don’t have to access the files directly on the system anymore.