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.

Using ADF Logging in a non-ADF project

In a previous post (Starting with ADF 11G Logging), I explained how ADF logging is simple to set up, and how it will enable you to set the logging levels at runtime, without having to restart any server. When I showed this to a colleague of mine, he immedialtely popped the question : “Can’t we use this for all of our java applications, even the ones that don’t use ADF?”. Well, the answer is yes, and it turns out to be very easy. Just add the correct jar to your project and your done.

This blog will demonstrate how to get this working. I use Eclipse Juno to create a small webproject, only containing a servlet that does the logging. In fact I will use the same servlet I used in the previous post.

So I open my Eclipse , and started with a File -> New -> Dynamic Web project. Give it a name, set ‘Dynamic web module version’ to 2.5, click the  ‘Add project to an ear’ checkbox and click finish.

dyn_wb_prj

Now Eclipse has created a web and ear module for me.

Image

Now right click the web project (ADFLogging), and select New -> Servlet, give it a name, eg. TestServlet, and click finish.

Remove the generated code in the servlet, and copy the code from the servlet ‘ExecuteLogger’ from my previous post (here) and paste it in our new serlvet.

PS. : When you copied the code from my previous blog, don’t forget to set ADFLogger.createADFLogger to our current servlet class name : TestServlet.class.

We will get compile errors on HttpServletRequest,etc… and on the ADFLogger class because they are not defined in the classpath of the project. So we’ll add them in order to get our servlet compiled.  I get the 2 jar’s from a JDeveloper installation I did on my machine. We’ll only add these jar’s in order to get the servlet compiled in Eclipse. We will NOT deploy them, as they are already available on our Weblogic server.

To add the jar’s, right click on the web project, and go to Properties. In the Properties, click on ‘Java Build Path’.

buildpath

Click on ‘Add External JARs…’ , and go to the directory where you installed your JDeveloper, which in my case is : C:\Oracle\Middleware.

In that directory , get following jar’s from the sub-directory :

\oracle_common\modules\javax.servlet_1.0.0.0_2-5.jar : contains the servlet classes like HttpServletRequest/Response,etc…

\oracle_common\modules\oracle.adf.share.ca_11.1.1\adf-share-base.jar : contains the ADFLogger classes.

Now we see the the following jar’s added :

jars_added

Click OK and return to the servlet. In the servlet use CTRL-SHIFT-O to import the neccessary classes from the jar’s we just added.

Now all compile errors should be gone.

Generate the ear file as follow : File -> Export -> Ear file

Select the ear project and enter destination of the ear file

When you examine the ear, you will notice that the folder \WEB-INF\lib is empty.

As the servlet and ADFLogger jar is already available on Weblogic, there is no need to deploy it with our application.

Now deploy the ear to the Weblogic and test the servlet with following url. :

http://localhost:7101/ADFLogging/TestServlet

It will generate following output :

output

To check the logging done by this servlet :

As I used the integrated Weblogic of JDeveloper, I will look for my logs using JDeveloper, but in a production environment,

these logs can be viewed using the enterprise manager of Weblogic. For details, see my previous blog.

In the Oracle Diagnostics Logging configuration, I see my servlet after the deployment. No message level is defined, so it will take “Warning”, as this one is defined as default by the Root Logger

logger

After te execution, I see following log lines in the log analyzer.

result

So that’s it. So the bottom line is to add the ADFLogger jar to your non-ADF project, and you are ready to go !

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!

@iadvise_live

http://www.iadvise.eu

SQL Developer Dbms Output Pane gone…

I already managed to remove the Dbms Output pane a couple of times from my SQL Developer, probably by doing a keyboard combination by accident.
I am not sure how I did this and I can’t really reproduce this but sometimes I was bugged with this problem.

Problem is that I can’t get this pane back by following the ‘normal’ procedure (going to Menu-> View->Dbms-Output).
Previously I did not found a proper solution for this and I just had to reinstall the sqldeveloper all over again.
Untill now, I found a really easy way to get this pane back.

First of all exit all your sqldevelopers sessions.
Go to the directory where the sqldeveloper is installed (the numbering of the directories could be different from one version to another, but it will look similar to this)
cd /home/vallafr/.sqldeveloper/system3.1.07.42/o.ide.11.1.1.4.37.59.48

there remove the windowinglayout.xml file
rm windowinglayout.xml

This file contains your ‘custom’ sqldeveloper layout. By removing this the sqldeveloper layout will be reset to the original settings.

Then restart your SqlDeveloper
in sqldeveloper go to Menu -> View -> Dbms_Output
And tadaaa  there it is again :)

APEX & mobile seminar

On May 12 iAdvise hosted the “APEX and mobile seminar”.
With a turnout of more than 50 customers and interested developers, it indicates that mobile development is a real hype and the demand for mobile applications is rising.

This was the starting point for Stijn who started with an overview of the current situation and evolution of mobile applications.
It became clear that as a developer you can’t ignore mobile devices in the future.
He continued with explaining the challenges in mobile development and which guidelines and strategies could help in choosing the right technology for mobile applications.

Bart took the word and focused on developing web application for mobile devices using APEX.
By combining APEX with JQuery Mobile, HTML5 and CSS3, one could develop a mobile web application rather fast and simple.
Also implementing specific mobile behaviour(eg. “swiping”) and generating extra content on a tablet are rather simple using APEX.

After Bart, Jan showed a demo about the opportunities on offering APEX applications to users as a native app.
This sounds rather strange, but is possible. He had worked out a demo using PhoneGap, a javascript library which create the communication between a web application and the API of the mobile device.
The demo showed how the APEX application from the other demos was wrapped in a native app.
This makes it possible to add a new contact in the contact list of Jan’s IPhone.

After the demos, they gaves us a look at the future of mobile development with APEX, what we could expect in APEX 4.2 and how PhoneGap will take its place in this future.

At the end Johan Byl of Hestia showed us some points which had to be taken in mind when using mobile devices in a company.
This was also very interesting for application developers.
When developing applications, they’re not always keeping in mind that there will be a maintenance phase.
Eg. The support for the application will be allright, but what about the devices itself?
How will we get the application on all the mobile devices of our company?
Can everybody connect to the company network with his or her device?
Or should we prepare a different infrastructure/security?
These and a lot of other questions were explained and answered by Johan and showed us there are a lot of things we have to think over again before putting a mobile application in production.

At the end, a lucky attendee won an IPad3.
For him, iAdvise and regarding the positive feedback for others as well, it was a succesful seminar.

If you missed this seminar, there will another one at the office of our dutch colleagues in Breda, The Netherlands on May 24 2012.

Click here to subscribe.

An interview with…Steven Feuerstein

Some time age one of my colleagues, An V., played with the idea to start a company magazine.  Everybody liked the idea and our manager gave a green light to create “De Poreiflash”.  Why it’s called “De Poreiflash” is a long story and I have to kill you when you don’t work for iAdvise and I tell you this.

But to keep the story short, she asked colleagues to contribute and asked me if I could write something about PL/SQL.
Sure I wanted to do that!  But about what in PL/SQL?  There so much to talk about…
I got “carte blanch”, so I started looking for topics: hints and tips, must know features, technical solutions,…
What else comes up when thinking about PL/SQL?
Indeed…Steven Feuerstein.

Wouldn’t it be cool to start with an interview with Steven Feuerstein?
I contacted Steven and he was prepared to answer some questions for “De Poreiflash”.
The first issue of “De Poreiflash” appeared two weeks ago.  An did a great job with our company magazine, it looks fantastic!
And here’s the interview how it appeared in “De Poreiflash”.
Somethings are already outdated: in the meantime Steven became a grandfather!
Congratulations Steven!

Q. Steven Feuerstein, the person with a PL/SQL obsession.  Introduction is probably not needed for people working with Oracle. Maybe a short one?

Sure. Well, yes, I think I do qualify as having an obsession about PL/SQL. I’ve written 10 books on the language and have spent most of my professional life since 1994 reading about, writing about, writing and generally wrestling with PL/SQL. Along the way, I’ve gathered some awards and related projects: I’ve been developing software since 1980, spent five years with Oracle (1987-1992), and have served as PL/SQL Evangelist for Quest Software since January 2001.

I am an Oracle ACE Director and write regularly for Oracle Magazine, which named me the PL/SQL Developer of the Year in both 2002 and 2006. I am also the first recipient of ODTUG’s Lifetime Achievement Award (2009).

In 2010, I started the PL/SQL Challenge, an online, daily PL/SQL quiz (now also offering weekly APEX, SQL and logic quizzes). In 2011, I launched PL/SQL Channel, a library of over 27 hours of detailed training on Oracle PL/SQL.

Q. How did you enter the Oracle world and got obsessed with PL/SQL?

I “fell” into the world of Oracle entirely by chance. I came out of university a Fortran programmer and ended up at Abbott Laboratories as a consultant, during which time I was introduced to databases on DEC10s and DEC20s. From there, I moved to CNA (insurance) in downtown Chicago and that was an incredibly boring job, though I did spend more time with a non-relational database while there.

So, bored out of my mind, with a newborn baby eating me out of house and home, I decided to look for a new position, and came across a Help Wanted ad looking for a person with relational database experience. Well, one of those words was familiar! So I got hold of an article on relational databases (Codd’s 12 rules), memorized that, and went to the interview. Turned out to be for a pre-sales job with Oracle. They were more interested in my ability to speak to large groups than my technical background. In any case, I was hired – the main issue for me was deciding whether or not I wanted to take a job that required wearing a suit and tie every day.

I spent a couple of years following salespeople around to accounts and doing the dog-and-pony shows for them with SQL*Plus, SQL*Forms, etc. Presales was interesting and sometimes exciting, but programming was way better, so I would constantly dabble with the Oracle tools, building little apps for my co-workers to use. This developed into TeamSell, a sales support application that caught the eye of Mike Fields, head of U.S. Sales in the early ’90s. I was drafted to join a small dev team and we started building some very cool SQL*Forms apps to support the U.S. sales force.

Then Larry canned Mike, and I was told to go back out on the road to help sell Oracle. I said no thanks, and took the first consulting job I was offered. Two years later, I saw an appeal on CompuServe for Oracle authors and I thought “Why not? I can write.” So I wrote Oracle PL/SQL Programming, which was the first independent text on PL/SQL, and it changed my life. From that point on, I was virtually a full-time student of the PL/SQL language, researching, writing, teaching, building code, etc. As I mentioned earlier, once you can structure your own time, all it takes is discipline­and reasonably good typing skills­and you can accomplish an awful lot!

I can still remember thinking as I signed the contract with O’Reilly that stipulated a book of 400 pages: “Is there really that much to write about PL/SQL?” 1200 pages later, we had to cut 400 pages to publish the first edition. Those 400 pages were all about writing PL/SQL in Oracle Forms and Reports. We should have published that immediately as a “side book.” Ah well….

Q. What’s your current job?

I am the PL/SQL Evangelist for Quest Software, have been since 2001. What this means, for the most part, is that Quest gives me lots of freedom to continue to explore the language, and support the worldwide PL/SQL community. Of course, I also do some product development, design and marketing. But I do think that Quest deserves lots of credit and a big thank you from PL/SQL developers around the world for making it possible for me to maintain my expertise and help other developers around the world.

Q. Other activities?

Ah, well, I used to play racquetball, go on lots of long bicycle rides, all sorts of things. Now, as I move through my sixth decade on Planet Earth, I find myself spending more time than ever in front of my laptop (believe me, it is not easy to write five new PL/SQL quizzes each week!) and THAT IS NOT A GOOD THING. So I struggle to keep my body in decent shop and avoid the problems associated with lots of typing and lots of staring at screens. But soon I will have a major new activity: I will be a grandfather by early November. Very exciting!

Q. About the PL/SQL Challenge…  What is it?

The PL/SQL Challenge is my latest and greatest effort to (a) coalesce an active, engaged, global community of PL/SQL developers, and (b) “download” my PL/SQL expertise from my brain into a format that is accessible to all developers and will be around long after I am gone. There are lots of sites that offer Q&A for PL/SQL and SQL; I am not interested in providing another of those. Instead, I think that developers are like “normal” human beings: we like to compete, we like to win prizes, we like to be recognized, we like to be challenged.

The PL/SQL Challenge does all of that: you take daily, weekly and monthly quizzes, competing with hundreds (and hopefully soon thousands) of other Oracle technologists. Of course, you don’t have to “compete”. You can simply take the quizzes (using, by the way, a nickname that does not reveal your true identity) as another way to deepen your knowledge of PL/SQL and related technologies like SQL and APEX.

So today it is a totally free quiz-driven learning experience. Soon, we will make it possible for you to join the PL/SQL Challenge by paying a small annual membership fee. Members will be able to utilize special features such as Practice Makes Expert, in which you can take past quizzes as often as you like, thereby sharpening your command of the PL/SQL language, and Quizbook, which will allow you to export quizzes as PDF “books” that you can use when away from the website – or to present to your manager to show her how much effort you are making to improve your technical skills.

If you haven’t registered and tried out the PL/SQL Challenge, I strongly encourage you to do so!

Q. Why should one participate?

Why not? It just takes a few minutes a day, and you are sure to either learn something new or demonstrate to everyone that you are well-versed in that particular feature of PL/SQL. When you play, you have the chance to win some prizes (ranging from O’Reilly ebooks to $250 Amazon.com gift cards).

Q. Some tips about developing PL/SQL?  Eg. on performance or hardcoding.

If you are not thoroughly comfortable with and applying everything on the list below, then you have a clear set of next steps when it comes to your PL/SQL training:

  • Collections
  • BULK COLLECT and FORALL
  • Autonomous transactions
  • Function result cache
  • DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
  • DBMS_UTILITY.FORMAT_ERROR_STACK
  • DBMS_UTILITY.FORMAT_CALL_STACK
  • Table functions
  • NOCOPY

Q. Can you recommend books, websites, presentations,… about PL/SQL?

There are lots of excellent Q&A forums for PL/SQL, with the OTN forum being about the best. Stackoverflow.com also attracts lots of PL/SQL-related questions.
Another great site for PL/SQL developers is oracle-developer.net, provided by Adrian Billington.
Tim Hall’s ORACLE-BASE.com is also an excellent all-around resource for Oracle technologists.
I do encourage your readers to check out all the sites and resources I’ve been putting together in recent years:

Q. With your books and presentations you inspired a lot of PL/SQL developers. How does that feel?

It feels great! I expect that many companies have indirectly benefited from my writings. But what I find most rewarding is the impact I have had on the lives of individual programmers. It is not terribly uncommon to have a person walk up to me at a conference or training and offer up a variation on this story, told to me back in 1998: “I was a union electrician at a steel mill in northern Indiana. When I got laid off, I went back to school and studied Oracle programming. I got hold of your book and it changed my life. Now I have a great job, my wife doesn’t have to work and can raise our kids.” Ah, that is so satisfying!

Now, just to make sure there is no misunderstanding: I am not saying that I think everyone should follow a lifestyle in which the man works and the wife stays home with the kids. :-)

But to have a  developer tell me his or her life was changed by my writing? Wow, what could be better? So, dear readers, don’t ever hesitate to send me an email (mailto: steven@stevenfeuerstein.com) or tell me in person about the impact my work has had on your life. I can’t get enough of that! :-)

Q. A last message to our readers?

I just turned 53 years old. I’ve been writing software rather intensively since 1980. Thirty years of programming, thirty years of (largely) sitting in front of keyboards and screens.

It’s been a great life (and I expect lots more good years to come), but I feel more and more of late that I haven’t maintained enough of a balance. There’s a very big world out there beyond my laptop, and I should be experiencing more of it.

So that will be my last message for your readers: Enjoy writing your software, but don’t let it consume your life!

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:

<biee11>\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup

There you will find a file named ‘user.cmd’ respectively ‘user.sh’. 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.