Search database objects with a variable in oracle

Something I really often use is this select:

SELECT distinct type,line,name,text
FROM user_source
WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%’)
SELECT distinct ‘column’ type,null line,table_name||’.’|| COLUMN_NAME name,null
FROM user_tab_columns
WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%’)
SELECT  DISTINCT ‘table’ type,null line,object_name name,null
from user_objects
WHERE object_type IN  (‘TABLE’,’VIEW’)
AND lower(object_name) LIKE(‘%’||lower(:search_source)||’%’)
ORDER BY type,name,line

This select will show you most of the database objects in a schema(columns, table,view,code), where this ‘search_source’ string is used.

You can add this into you sqldeveloper as well, as a user defined report.
To accomplish this you will have to do the following steps:
1) Open sqldeveloper
2) go to Reports tab(If you don’t see this tab go to the Menu choose view->Reports
3) right-click ‘User Defined Reports’ -> add Report -> give name and put the sql code as above into SQL input screen(you can leave the rest as default)
You can use this Search in sqldeveloper now.

ApEx 4.0: Map not showing data points correctly

In ApEx 4.0, when working with a map region (World without Greenland) (Map world/world_wo_gr.amap) you might experience that not all data points are correct.
For example: when you work with the map “World without Greenland” and you choose a query with Series Type of Bubble, Switzerland is positioned in America while Spain is in the ocean.

ApEx 4.0 data points not showing correctly

Oracle recognizes the bug (9950531) and will supply a fix in upcoming patch 4.0.1.
Meanwhile as a workaround the next action is suggested: setting “Series Type” to “Map”, and the countries will be correctly labeled

Oracle Forms 11g and Apex using external events

I remembered an old post of Roel Hartman where he integrated a form into apex.
He used a part of OraFormsFaces by Wilfred van der Deijl: the CommunicatorBean.
Using this CommunicatorBean forms could react on “external messages”.
Now with Forms 11g reacting on external events, this CommunicatorBean isn’t necessary any more(sorry Wilfred).

This is how I did it using external events…
First things first: set up the advanced queueing mechanism.
Check this tutorial which includes setting up advanced queueing.
I created a little form based on emp(nothing fancy)…

The new feature in forms:

With the following properties:

What should forms execute when this event happens?
This has to be specified in the When-event-raised trigger.

In this case we retrieve the payload and extract the empno from it.
The empno is used to set the default where clause on the block.
When there’s an empno on the queue, forms will query that employee.
That’s all for the forms part…
Now I created a little Apex page with two regions:

The Employee details will be our form.
So I put our form in the HTML using an iframe:

Using a “select list” it’s possible to select an employee.

This is the result:

Now the purpose of the select list is to choose an employee and show the detail information in our form.
In order to do this, the select list calls a javascript function.

This javascript function is created in the HTML header

The code behind this:

<script language=”JavaScript” type=”text/javascript”>
function getEmployee (){
var emp = $x(‘P2_EMPNO’);
// send request
var ajaxRequest = new

// get response
ajaxResult = ajaxRequest.get();
ajaxRequest = null;

This javascript function calls an application process and uses the empno as parameter.
The application process put the empno on the queue.

When changing the select list, the form is queried

This is a solution to integrate forms into another application whether it’s Apex, ADF or another web applicaton.
When it can put something on the queue, forms can react on it.
And yes, I could do it using the javascript feature in Forms 11g. I know…
And for Apex it’s probably a better solution, as we can skip the AQ part and make calls to and from forms in Javascript.

Oracle Open World 2010: Forms in the Middle of Middleware

People attending Open World to have a closer look at fusion technologies and how to integrate them in your existing applications, need to check out the following session:

  • ID#: S315945
  • Title: Oracle Forms in the Middle of Middleware with Oracle Product Management
  • Track: Application Servers, Application Grid, and Development
  • Date: 22-SEP-10 Time: 13:00 – 14:00
  • Venue: Marriott Marquis Room: Salon 9

Together with Grant Ronald we will talk you through the possible scenario’s to modernize your existing forms applications. After each scenario we will demo the functionalities and showcase some of the success stories we’ve conducted together with our benelux customers.

The different scenario’s include upgrading to 11g to use the event-driven architecture, integrate with existing applications such as apex, .net, google maps, bpel, …

In other words if you’re using fusion technologies such as BPEL, OSB, ADF, … you can easily integrate these with your existing forms applications using the new features provided in 11g.

Oracle Fusion Middleware 11g support(including Forms 11g)

Since we’re talking about Forms 11g, the first question we get from customers is “Until when is Forms supported?”.
I saw the blog post of Gerd Volberg and thought is was interesting to share this with our readers.
Extended support for Forms 11g(and reports): June 2017.

Complete information on Fusion Middleware support: Lifetime support Middelware

And maybe it will go further as I mentioned in a previous post.

APEX 4.0: Export feedback, take it to the next level

Team development is one of the new features of APEX 4.0, one option to use with this feature is Feedback. In short, Feedback allows users to easily report “bugs” for an application. (Like we APEX developers can program bugs… NOT!).

There are already 2 blog posts that cover the way you can add a feedback page to your application and how to access your feedback as developer.

This blog post will describe how you would export your feedback from a production system to the development system where you as developer try to make/solve things. After changes are made you want to inform your users what has been modified…

1. Setting your environment variables
The first thing you have to check before you want to export/import feedback, is the name of your workspace. If you use the same name for a workspace in both environments then we will have to distinct the development environment from the production environment. If you don’t do this, the export/import feature for feedback will not work. You can distinct the environments by altering the Feedback Synchronization Source Identifier, this will default be the name of your workspace. Changing the identifier can be done by using the Internal workspace -> Manage Workspace -> Workspace Details -> Edit Workspace Information.

Continue reading

Oracle BI EE 11g Launch Day!

Oracle Business Intelligence Enterprise Edition 11g, the new, industry-leading technology platform for business intelligence, which offers:

- A powerful end-user experience with rich visualisation, search, and actionable collaboration
- Advancements in analytics, OLAP, and enterprise reporting, with unmatched performance and scalability
- Simplified system configuration, life-cycle management, and performance optimisation

A first impression of the new BI Suite:

OBIEE 11g Lauch Website on

OBIEE 11g – New Features Overview

OBIEE 11g – Technical Overview paper

OBIEE 11g – New Interface Screenshots

Unfortenately still no news on the official product launch date.

Will be continued..

ApEx 4.0: Date Picker Item Minimumdate and Maximumdate

One of the big improvements of ApEx 4.0 in my opinion is the rebirth of the date picker Item. The old version of the date picker was a popup that opened a calender, it was ok but a lot of developers already used something more sexy like the jQuery Ui Datepicker. This required some custom development and was’t out of the box functionality.

In ApEx 4.0 the date picker got a new look and feel based on the jQuery Datepicker. It opens a lot faster and is overall more simple to use. You don’t have to create a validation anymore to check if the user has specified a valid date, this is done automatically.

Some new properties are available for the date picker:

Continue reading