Fast writing of Queries with Joins in SQL Developer

When you create a report in APEX and build the source of that SQL query with the wizards you get these annoying quotes (") around the column and table names. It also takes time to do it with the wizard, time I don't have.

I tend to write my queries in SQL Developer so I can immediately fine-tune the query so it's exactly what I need. Writing your queries in a tool like SQL Developer has many more advantages, as you can look back in the history of your statements, you can immediately do an Explain Plan and format the code but even more immortantly it's very fast to write statements.

Let's say you need to write a query that joins four tables, you can just write it manually... Or your could just select the tables from the tree and drag them on your SQL Worksheet. SQL Developer will ask if it needs to join the tables for you and write the statement. It uses aliases for the tables, which is also something I like better than using the full table names in front of the columns...


Remark: you need foreign keys in place so the joins are written correctly.

As I saw many people didn't seem to know about that, I thought to quickly write something as it's definitely a time saver.

Call BI Publisher report from APEX

After my post about the integration of APEX and BI Publisher I got some questions about further integration.

Also during my presentation "Advanced PDF documents from Oracle Application Express using Oracle Business Intelligence Publisher" at Oracle Open World I got some questions about security.

If you didn't see my presentation yet, you can download it here or you can see me presenting this week at UKOUG or in London in March 2008 during the European APEX Training Days.

When Daniel sent me a mail about his thread in the Application Express Forum I decided to post some more about BIP and APEX integration.

What we want to do: link a report made in BI Publisher to a button in APEX. This need to be as automated as possible...

Why do we want to do this as APEX is providing PDF printing out-of-the-box?

The answer is that APEX is limited in printing and BI Publisher is specialized in this area. In a next version of APEX (3.1 and 4.0) the printing is enhanced, but today that's not yet the case.
If you want to include multiple queries in your APEX report or print/export it as a ppt for ex. or even just fax a report, APEX doesn't provide this functionality declarative, but BI Publisher (BIP) does.

I suppose you know how to create a report in BIP, so I won't go into detail about that. In a previous post I already explained how you can install BIP (for Windows and Linux) and link it to APEX, so that's something you won't find here.

If you don't have a BIP report yet, you find some example/demo reports when logging in into BI Publisher (for ex. an url like: http://your-server:9704/xmlpserver).
An example of a report, go to: Home (Reports) > Shared Folders > Supply Chain Management > Warehouse Inventory Report

If you call that report you need to be authenticated (known by BIP) to get the result. That's something we want to avoid as we don't want to login in BIP again when coming from APEX...

To avoid you need to login again, go to: Admin > Security Center > Security Configuration

There're some different Security Models available to you (LDAP, BIP, DB). If you would use Single Sign-On, your live would be quiet simple as you don't have to do anything. But in this article I'm concentrating on the standard BI Publisher security.

To solve my logon issue, I'm making my reports public. In BIP this is called a "Guest".
In the Security Center of BIP I name my "Guest folder" (or public folder).

I'll need to create that Guest folder which will hold all the reports I'm calling from APEX.
Go to Reports > Shared Folders and create a new Folder "Guest". In that folder I created a new report (a copy of the example I showed above).

When you call that report by clicking on it, you'll see the result. You can adapt it to see it in a different output format. Then the most important part... Click on "Link to this report" and select" Document Only". A new window will pop-up on the same page with the link to the report.
Copy that url.


Now we'll use that url in our button in APEX. Create a new button and as Target select "Url" and copy the url you got from BIP in the "URL Target" field.

If you run your APEX application and click on the button you just created, you should see the report coming from BI Publisher...