OBIEE 11G – Error in Importing Metadata

Recently I start creating a repository (11g version) in offline mode and I want to import metadata which failed by the error ‘The connection has failed’.. I have searched for a solution and I will explain this in the next sections.

We can import some metadata from the Oracle BI Administration Tool > File > Import > from Database …  In the Import dialog box, we have to select a connection type, in my case ‘OCI 10g/11g’. Next step is to enter a Data Source Name (orcl) and a username and password from which you want to import the tables.

Import Metadata

By clicking on the ‘next’-button it troughs an error ‘The connection has failed..’ This was very weird because my Oracle DB and Listener were up-and-running. I had checked this before to make a connection via SQLdeveloper.

At this point I had to find a solution before I could go further on my repository modeling. On Oracle forums I found a nice tip which solves the problem.

The root cause can be found in the following directory:


There you will find a file named ‘user.cmd’ respectively ‘’. When you open the .cmd file you will see that you can set a TNS_ADMIN. This was not done earlier and this causes the problem of importing metadata through the wizard.

Solution: you have to set the TNS_ADMIN to an appropriate path such as <biee11>\Oracle_BI1\network\admin. This is the location where your tnsnames.ora is stored.

Edit User Command

Afterwards, you have to save the .cmd file and try again to import some metadata in the Administration Tool.

Note: maybe you have to close the Administration Tool or restart your BI-services. For me it was sufficient to restart the Administration Tool and everything was working fine!

Important remark: when you are creating Dashboards & Answers or you are working in Online mode and you want to retrieve some data for a certain table by selecting the option ‘View Data’, you will also get the error: ‘The connection has failed..’. This problem will only occur when your tnsnames.ora is not stored in the Oracle_BI1 directory.

Pictogram: APEX Developer – Plug-In Developer

Not every APEX developer should be a Plug-In developer.
The APEX developer uses the Plug-In a Plug In developer has created.

Following photo shows the pictograms we used during our presentation @ OBUG APEX SIG :

Pictogram Plug-In developer APEX developer

Pictogram Plug-In developer APEX developer

During the demo we showed our little green man while we created and elaborated the plug-in via the shared components.

When we implemented the plug in in a page, we showed the pictogram of the little red man.

As you can see the red guy brings different elements of the puzzle together: standard components (red) and a plug-in (the green piece).


Today iAdvise has given a presentation during the OBUG APEX SIG at Oracle Belgium. The title of the presentation was “APEX Plug Ins: How to use them, How to build them.“.

The goal of the session was to give a general introduction on

  1. What are plug-ins ?
  2. How can you use them as an APEX Developer.
  3. How can you write them as an PLUG In Developer.

Especially part 3 of the presentation was a real challenge. We did a live demonstration of how you can gradually build up a new plug in from scratch. In 5 steps Stijn Van Raes demonstrated how to construct a “Clearable Text Item” plug-in. It is not the most complex plug-in, but it allowed us to describe every single step, pointing out the existing Plug-In API’s that you may use when writing your code. At every step, the necessary PL/SQL code was explained,  the resulting options for the APEX Developer demo’ed and  the effect at run time for the end user. Where relevant the resulting HTML code was inspected by using Firebug.

Because there is not yet that much documentation on those Plug-In API’s and their usage –  unless in the internal PL/SQL Packages of the APEX engine itself or in existing plug-ins–, we decided to make a kind of APEX4.0 Plugin Cheatsheet which lists the most important API procedures and functions and the different record types they use.

For the people who were interested, it was also possible in the afternoon to attend a hands-on session and try out everything we explained in the morning. We have elaborated two tutorials that repeated every single step with some extra explanation and the necessary code snippet. Everybody who attended the hands-on finished both tutorials without any problem.

PL/SQL Challenge V2

For over a year I play every day the PL/SQL Challenge, as I mentioned already in previous posts.

Today there’s version 2 of the website.
You can read about it on the PL/SQL Challenge blog.
Great effort of Steven Feuerstein and his team!

So, if you want to test your PL/SQL knowledge, compete against collegues/other PL/SQL programmers or you want to know/learn more about PL/SQL… Check it out!