Another 5 neat 12c features for Oracle developers

In this post I will put 5 other new 12c features in the spotlight (in addition to the features of a previous post), that really makes the 12c an improvement against the previous versions of the Oracle database.

To get this result I listed up all the major new features and wanted to know my top 5 features that would make my life easier(as a developer), excluding the features from the previous  post (I certainly would have added the sequence modification(feature 1)), when doing development on an Oracle database.

  1. Top end query -> I really like this feature and I’m still wondering why it took Oracle so long before creating it. It is something I could have used a lot in the past, but instead I had to create far too complicated, not as nice readable queries to achieve this. How does it work? Well it’s very easy and it’s readable and can be used in a wide variety of cases. Some examples:
    Only get the first 3 rows:
    select * from X order by id
    fetch first 3 rows only;

    Skip the first 3 rows and get the next 3 rows:

    select * from X order by id
    offset 3 rows fetch next 3 rows only;

    Get the first 50% of records

    select * from X order by id
    fetch first 50 percent rows only;

    Get the first 3 rows together with the records equal to these department id’s

    select * from emp order by deptno
    fetch first 3 rows with ties;

    If you want the capture the last rows, you can obviously change ‘first’ with ‘last’…

  2. In the 12c database the use of 32767 characters for a VARCHAR2 in SQL is now available instead of the maximum of 4000(this is also the case for RAW and nvarchar2).
    We all have been waiting a long time for this one and before we had to use the clob datatype.
    But beware this is not an out of the box feature, you will have to execute the lines below before this is enabled :
    shutdown immediate
    startup upgrade
    alter system set max_string_size=EXTENDED scope=both;
    Shutdown immediate

    More info can be found on:

  3. The invisible column is a feature of which I was wondering where I could use it for.
    Well it could be handy when you are adding a column to your table, but you don’t want any existing code to be impacted by it.
    Another case where it could be useful, is when using audit columns.  Columns as the creation_dt, update_dt, user_creation and user_update will only be of any added value when you would like to audit a certain column.
    Packages with inserts, updates, references to this table will not be impacted by the creation of this column.
    On the other hand there is also a risk that you forget that this column is in there, because you have to explicitly call for it (a describe or select * will not show this column). You can create invisible columns like this:


    If you want to make the column visible again, use this:


    In summary it could be handy, but don’t forget this column or it will pollute your table.

  4.  The with clause inline plsql feature is also something that I think is very welcome.
    It will make it possible to create a procedure or function inside your select statement instead of having to create this in a package or function. Oracle also says that this will optimize the performance against having to call a schema procedure/function(I still have to test this).
    A little example:

    FUNCTION fnc$_add_one(p_num IN NUMBER) IS
    RETURN p_num+1;
    SELECT fnc$_add_one(1)
  5.  Most of the time I use the ANSI way of writing for a left outer join, but the oracle way of writing left outer joins is still often used by many of the oracle developers.
    But there was one thing that you could do in ANSI, that you couldn’t in the oracle way.  You couldn’t write multiple tables on the left of an outer join, untill12c…
    In 11g and before when coding something like this:
    select *
    from a,b,c
    where =
    and =
    and = c.id2(+);

    This resulted in -> ORA-01417: a table may be outer joined to at most one other table

    In 12c this will work, also the ANSI solution obviously still works both on 12c and on 11g

    select *
    from a
    JOIN b ON ( =
    LEFT OUTER JOIN c ON ( = AND = c.id2);

Together with the previous post this makes 10 reasons why you should start to use the Oracle 12c database :-)

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.