APEX and eBusiness (EBS or APPs)

Traditionally EBS-extensions are built in tools like Oracle Forms, Reports and BI Publisher. Most E-Business developers master these traditional skills (e.g. PL/SQL, workflow).
Oracle now propagates a shift towards Java (Oracle Applications Framework (OAF) and Oracle Application Development Framework (ADF)), however this requires to refocus of the current E-Business developers toward java development (object orientated).  If the developer’s background are the basic Oracle skills (SQL and PL/SQL and even Oracle Forms), then APEX is the perfect tool to web-enable your skills. No Java knowledge needed.

At the moment alternatives like APEX are not widely used in the E-Business area. Maybe due to the following reasons:

  • E-Business  customers are not yet aware of the possibilities of APEX within E-Business;
  • E-Business customers are not yet aware of the lower costs of APEX development in e-business;
  • E-Business  customers are not yet aware that the tool is free as it comes pre-installed with Oracle XE and 11g;
  • APEX consultancy companies do not have the E-Business knowledge.

Since the very beginning we, at iAdvise, believed in the possibilities of APEX as a valid “alternative” platform for building cost-effective, open, reliable E-Business customizations and extensions.
We think of different use-cases where APEX can be used for custom development:

  • Custom data collection, for which today Excel is often used
  • Building New applications, in support of business processes not covered by standard APPS
  • Reporting and data analysis, when necessary with real time access to EBS data
  • Modernization of custom Oracle Forms applications

Already in 2008 we did a modernization exercise for existing APPs functionality. The customer wanted a more efficient way for manually introducing invoices in the system. We developed a small Quick Entry application, in APEX 3.0. Via a small wizard,  the user can enter the necessary data. Following screenshot shows Step 2 of the wizard where one or more order lines could be defined.

Add invoice lines

Quick Entry AR Invoices: Step 2

When pressing Finish at the last step, the newly created invoice is added in the APPs tables via the standard APIs. We have also foreseen the possibility to switch to the ‘Open Interface (OI)’ mode: instead of using the APIs, the data is in inserted in the available Open Interface tables.

Since last year, Oracle also officially declared APEX as a valid tool to extend Oracle APPs by publishing the already famous white paper that gives a very good explanation how you can integrate both environments.

In the BeNeLux the interest is growing for this combination. Therefor we are happy with the OBUG initiative to organize on the 15th of February an APEX-EBS combi-SIG. You can subscribe for this event via the OBUG site.

We will be there !

Using a database link with heterogeneous services in APEX

Last weeks I ran into some issues with APEX, so let’s take some time to write something about them.

The most recent is about heterogeneous services (also referenced as HS). Here is the case:
We have an Oracle DB instance with some APEX applications and in one of those applications I need data from a MS SQL server database. Not a very good idea, but the set-up couldn’t be changed and both databases can handle it, so I gave it a try.

Our DBA installed heterogeneous services and created a database link between the two databases. Querying the necessary views was pretty easy (I only needed a couple of synonyms for ease of maintenance) and data came in faster than expected.

The next step was to query these views with APEX.
Because I created Oracle views on the views of MS SQL server, this was also very straightforward for APEX.

After a while however, there were issues in other applications and also the APEX builder itself raised errors while uploading files and importing applications. The following error returned:

ORA-02047: cannot join the distributed transaction in progress

, usually accompanied by some other ORA error codes. Not a very descriptive error message. A colleague pointed out to me that it has something to do with database links and heterogeneous services.

So, what is the problem?
APEX starts a new database session for my application. In this session the database link to the MS SQL server database is opened and data is queried. Since APEX constantly changes and re‑uses database sessions (this technique is called session pooling), the database session is released after a while and put back in the session pool until another application needs a new/another session. Because the database link in my session is only used for querying, there is no line of code that closes the database link so it remains open, even when the session is put back into the session pool. When another application takes this session from the pool, there might be issues with specific transactions, like setting a context (e.g. setting NLS parameters), because Oracle wants to perform this operation on both the Oracle database and the linked MS SQL server database. Because an NLS parameter is set when uploading a file, the end user receives the ORA-02047 error.

What can one do to prevent this from happening? There are several solutions for this issue:

  1. Don’t use database links with heterogeneous services. This is the easiest solution but isn’t always possible.
  2. Only access data over a DB link with HS in a package, function, procedure,… and close the DB link at the end of your block of code, which is not always possible either.
  3. Close any open database link, or only the specific one, when you put your session back into the session pool.

For the third solution, something interesting is implemented in APEX 4.1.

At application level, you can define some PL/SQL code that should be executed when initializing or returning a session from/to the session pool.

You can find this section under Shared Components  >  Security Attributes. All the way at the bottom is the section ‘Database Session’:

In earlier versions of APEX, something similar was already available for VPD (Virtual Private Database) but this only allowed execution of code at session initialization. The APEX development team has now expanded this functionality for any PL/SQL in general, so not only VPD-related, and both at session initialization and returning.

For my issue, all I had to do was write some code to close the database link when it was still open. I created a function in my application database schema which returns the number of open database links with a specified name:

CREATE FUNCTION is_dblink_open(dblink  IN VARCHAR2)
RETURN INT
AS
  l_cnt   INT;
BEGIN
  SELECT COUNT(db_link) INTO l_cnt
  FROM v$dblink
  WHERE db_link = dblink;
  RETURN l_cnt;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;

Before you create this function, don’t forget to grant access to v$dblink:

GRANT select ON v_$dblink TO my_app_schema;

Execute with SYS. Note the v_$ instead of v$! (more info: http://goo.gl/euxcs).

All that was left is to use this function in the Database Session Clean Up Code:

DECLARE
  l_dblink VARCHAR2(100) := 'MY_SQL_SRV_DBLINK';
BEGIN
  IF is_dblink_open(l_dblink) > 0 THEN
    DBMS_SESSION.close_database_link(l_dblink);
  END IF;
END;

Or if you want to be sure that all open database links are closed, you could use:

BEGIN
  FOR l_db_link IN ( SELECT db_link AS name FROM v$dblink ) LOOP
    DBMS_SESSION.close_database_link(l_db_link.name);
  END LOOP;
END;

Special thanks to my colleague Jan for pointing out to the HS problem and to Patrick Wolf for helping me on this issue through the Oracle APEX forum (read the full post here: http://goo.gl/vVoeb).

This way, I hope to help out other people too when they run into this issue.

Oracle Open World – Larry’s keynote: some announcements

Larry talks first about Fusion Apps.
After a controlled test period, it’s now officially available.

And now for really the first announcement…
Larry Ellison: “Everybody has a cloud, we too need a cloud”
So here it is Oracle Public Could.
Great to see Apex is strongly supported.

You can move your application and data from your datacenter to the cloud and back without a problem.
The Oracle Cloud is based on industry standards(Java, XML, Web services,…).
Pricing is based on monthly subscription, and that’s all, no extra licenses.

A second announcement: Oracle Social Network.
Collaborate, share, application integration, BI Navigation, …
He even gave a live demo of the Oracle Social Network.

All things Oracle

There’s a new Oracle source available:  All Things Oracle.

The aim of All Things Oracle is to provide a gateway to the wealth of information and material available for Oracle developers and DBAs.
The site brings articles and other resources of Oracle experts.
Just to name a few:

All very experienced experts that will bring interesting articles!

I will also contribute to this site.
My specialities are SQL, PL/SQL, Forms and Forms Modernization, so expect articles on these topics in the near future on All Things Oracle.

Get triggering element in dynamic action

Last week, I ran into a problem with a dynamic action in APEX 4.0.

I have a series of 8 images of persons which I generate with PL/SQL. They are all speakers on a conference. Every image is clickable and should insert the name of the speaker in the database.

I gave a class “speaker” to every image. The ID of the image is the primary key of the person in our database. By doing this, I am able to create a dynamic action which I can bind to the JQuery selector ".speaker".

In this case, I wanted to use AJAX to perform my actions so I created an AJAX callback insert_speaker which contained the PL/SQL block with the insert command. Based on the PK of the person, the name of the speaker will be inserted into the database, together with some extra information.

In my dynamic action, I added a true action of type “Execute JavaScript code”. Here, I ran into a problem: how do I get the ID of the image the user clicked on?

On the internet, I found two solutions with JQuery to get the ID of the triggering element of an event:

$(this).attr("id");

and

event.target.id;

There is a little difference between these two, found it out here, so I tested both and concluded I needed event.target.id.

I completed the JavaScript block with the AJAX request, the ID parameter and the other lines and tested the page.
Everything worked perfectly. Well done, I tought…
…untill I tested my page with Mozilla Firefox. Firebug returned an error ‘event is not defined’.

Hmm…and it all worked fine in Google Chrome (I use this browser for application development)? Strange.
After a while of searching, I figured out there is another solution, something specific to APEX:

this.triggeringElement.id;

Sounds logical, but it isn’t when you have no idea this line can be used in APEX.

The only official documentation I could find about this, is the help text of the textarea labeled “Value” in the When block of a dynamic action.
Help text

When I changed

event.target.id;

to

this.triggeringElement.id;

in my code, the page works fine in both Google Chrome and Mozilla Firefox.

For further development with APEX 4.0: make sure you test your pages in multiple browsers!
The example above states  very well that not all browsers interpret code the same way!

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).