Apex Extension

About a month ago I saw a mail coming by where a colleague of mine had integrated the jQuery plug-in Codemirror into a text area for the end-user. I hadn’t seen this plug-in before, but I really liked the feel of it.

I’m sure all of us can recognize the fact that we miss tab indentation in  APEX, or that we would like to see key words, numbers and strings in color, or how about some line numbers? Perhaps line numbers that hold into account that the line number of an error in a PL/SQL APEX process is never quite the same as the place of the error. This last one is because APEX places BEGIN and END; automatically around PL/SQL processes. Convenient for the developer so he can develop faster, not so much when it comes down to debugging.

Since we can not change the APEX Application Builder, it would be nice to find another solution? And that solution exists: take a look at the following screenshots:

Image

Image

So how did I do it? A little “thinking outside the box” did the trick. While we might not want to mess with Apex directly, we can use browser Extensions and add-ons, to alter the looks, safely from a distance. So I created a small Chrome Extension that integrates CodeMirror into the APEX Text Areas.

Chrome Extensions are created with HTML5 and JS so once you have the basics of your Extension set up you can convert an Apex Text Area like this:
var vTextArea=document.getElementById(“F4000_P4312_PROCESS_SQL2″);

vMyEditor = CodeMirror.fromTextArea(vTextArea, {

mode: “text/x-plsql”,
tabMode: “indent”,
tabSize : 2,
lineNumbers: true,
firstLineNumber: 2,
lineWrapping: true
});

I wrapped this in a “$(document).ready(function(){});” to make sure it is executed after loading all plug-ins.

I hope I showed you all that you can do allot with a little imagination, and I welcome any questions or suggestions.

Joni Vandenberghe

Oracle Forms community at Oracle Open World 2012

Less than 3 weeks before the biggest Oracle event in the world kicks off: Oracle Open World 2012!

I already wrote about some interesting events: ADF EMG Sunday and the Oracle Benelux Architectural event.

In case you’re into Oracle Forms, you can add an extra event to that: The Oracle Forms Community Event.

After the session of Mia Urman and Grant Ronald “CON5639 - Give Your Forms a Face-lift: Tips and Tricks for Oracle Forms GUI Modernization“(Tuesday, Oct 2, 11:45 AM – 12:45 PM – Marriott Marquis – Foothill F) there will be an “Ask the experts” event.
Here we will answer questions from the audience with Grant Ronald (Oracle Forms Product Manager) as our moderator and a panel of experts(with Lucas Jellema, Oracle Ace Director, as one of them) both solution providers and consultants.

I will be one of the experts too:
– 14 years of Oracle Forms experience
– Presented on several conferences and seminars on Forms and Forms Modernization(OOW, UKOUG, …)
– Presenting this year on OOW: UGF3783 – Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World

You can register and add questions using this form.

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.

Gartner Report: “Modernization and Migration Strategies for Oracle Forms”

Grant Ronald brought this Gartner Report to attention on his blog: “Modernization and Migration Strategies for Oracle Forms“.

It’s great to see that our message brought to our customers and at our presentations is confirmed by this Gartner analysis

  • Upgrade to the latest version – At the moment this is Oracle Forms 11gR2
  • Modernize and integrate
  • Migrate(ADF, APEX, Open Source, …)

If you want more information on this topic, you can read my article on All Things Oracle: “What’s Your Choice for Oracle Forms?“.

OGH APEX Dag 2012

This monday I gave a presentation at the OGH APEX Day about a Forms to APEX migration together with my colleague Ronny Boeykens.

Everything went smoothly, except when my laptop decided to install some windows updates and restarted out of the blue :).

Our presentation was kind of a follow-up on the ‘Forms Modernization’ presentation of my colleague Gert Poel, where they illustrated what the possibilities are when you want to modernize your Forms applications.

In our presentation we explained that APEX can be a worthy substitute for Forms, by showing some examples of a business case.

OGH APEX Dag - Can APEX replace forms

OGH APEX Dag - Can APEX replace forms

I also saw some interesting presentations during the day:

First off was the keynote by Patrick Wolf, where he gave a nice glance at the features we can expect in APEX 4.2.
The things that stood out the most for me were:

  • jQuery mobile will be fully integrated.
  • It will be possible to attach multiple themes to one application, so you can have a mobile and desktop theme in your application.
  • Subscription will be possible on theme level: Thank you!
  • The max. number of items on a page will be raised to 200. Offcourse Patrick noted that when you have more then 100 items on your page, you seriously have to consider if your page is still usable!
  • You will be able to download packaged applications from the Oracle Cloud.

Next up was the presentation of Roel Hartman, that showed some interesting things you can do with HTML5. Especially the websockets, where you can push data from your database to your webpage was really cool. Also the local storage caught my eye.

I also liked the presentation of John Scott, that demonstrated the possibilities of the error handling feature in APEX 4.1. I’m still waiting for the result of the last demo though :).

A big thank you to the organization, everything was perfect! I really enjoyed the day and saw some interesting things.

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.