Oracle APEX Builder Plugin v1.7 release!

Oracle APEX Builder Plugin version 1.7 is now available!

Not heard about this plugin yet? It's a Firefox/Internet Explorer extension to enhance the usability/productivity of the Oracle APEX Builder IDE. It adds

• Resizeable Textareas
• Keyboard shortcuts
• "Set" operations
• Seamless integration of the ApexLib Framework
• and much more!
  

So what's new?

Do you also have a screen which is bigger than 800x600 for your Oracle APEX development? Have you ever noticed that it looks like that the textareas on the Oracle APEX Builder pages where we enter our SQL statements, PL/SQL code,... are designed to fit for this resolution? A lot of space is unused next to the textareas. It would be much better if the textarea would use it so that we can put a little bit more code into a line.


That's exactly what the new version of the plugin does. It adjusts the width of the textareas to use this unused space!


Another new feature is the "Repad sequence numbers" configuration for the "Set" feature of the plugin, which you can use to renumber the sequence numbers of your page items, ... in the bulk edit pages. Thanks again Anthony Rayner for providing the configuration!

Note: With this release I have also upgraded the internally used jQuery library to the newest release. Unfortunately a few features have been removed in the newest version. Please check the "Upgrading the Plugin" section in the HowToInstall file for details. It was also necessary to update the configuration files for the ApexLib integration, please get the new set from the ApexLib download page. And don't forget to clear the browser cache when to have replaced the config files!

Get it now!

Download it from the Oracle APEX Builder Plugin homepage and check the HowToInstall file for all the necessary steps to install it.

What is planned for the next release?

I'm currently playing with the integration of an editor which offers syntax highlighting, auto indent, search/replace, bracket matching, full window editor, ... instead of the boring textareas.

I'm currently looking for beta testers to test and to give input. The following is a screen capture of the integration. So if you are interested, send me a mail. You can find it in my blog profile.

« ... Read full posting ... »

Updated Statement of Direction for Oracle APEX 3.1 and 4.0

It looks like that the Statement of Direction for Oracle Application Express (APEX) has been updated.

Based on the update, Oracle APEX 3.1 will contain now much more features than in the original Statement of Direction, which was posted a few weeks ago. One of the exiting new features will be the "Interactive reporting region types" which Carl Backstrom already demonstrated in his Oracle Application Express (APEX) 4.0 - Preview Video. There where some rumors that it would already be part of 3.1, but only as PL/SQL API's. As it looks now, it's fully integrated into the Oracle APEX Builder environment. That should make the handling much easier. I think the enhancement of the Oracle APEX mail API's to handle attachments, will also help a lot of people. Too bad that the custom item types and the error handling improvement didn't make it into 3.1. But read on to find out what's coming up.

Oracle Application Express 3.1

The next major release of Oracle Application Express will be 3.1. Application Express 3.1 will incorporate the following:

• Interactive reporting region types which natively integrate Web 2.0 features to filter, break, sort data, etc.
• Improved PDF report layout
• Enhanced print attributes allowing for more control of PDF document structures
• Optional runtime-only installation, which will install the minimum number of database objects and grant the minimum number of privileges to run Application Express applications in a production environment
• PL/SQL API to manage a runtime installation of Application Express
• Documented and supported Application Express JavaScript libraries
• Improved calendar region type supporting AJAX requests
• New PL/SQL API to send attachments in e-mail
• Enhanced integration with Oracle SQL Developer for MS Access to Application Express migration
  

Oracle Application Express 4.0

Oracle Application Express 4.0 will focus on declarative support for Web 2.0 features in Application Express, as well as the extensibility of the Application Express framework. Application Express 4.0 will incorporate the following:

• Extensible item framework, enabling developers to "plug in" their own custom item types
• Declarative support for AJAX validations
• Declarative support for cascading select lists and other similar item types
• Improved tabular forms, including support for validations, integration with collections, and additional item types
• New item/region types, including DHTML calendar and AJAX tree
• Improved error message handling
• Numerous functional and performance improvements
  

« ... Read full posting ... »

Blogger finally listened to my prayers!!!

Just noticed that Blogger/Blogspot has finally added a comment follow-up notification!

Man I was waiting for that feature! It's really hard to keep track of all my comments on the other Blogspot blogs. This new feature will make it a breeze!

Maybe they will finally also add a "Teaser" feature to the blogging software, so that I don't have to use the silly workaround anymore I'm currently using...

« ... Read full posting ... »

SQL embedded into PL/SQL

A few days ago I spoke with another developer about SQL embedded into PL/SQL code and how function calls are handled in the WHERE clause of SQL statements. There is sometimes confusion who (PL/SQL engine or SQL engine) is executing it, I thought it's a good idea to write a posting about it.

Last year I have already blogged about it in Caution when using PL/SQL functions in a SQL statement, but that was in the context of writing a stand alone SQL statement for a report, ... but what actually happens if you have a SQL embedded into PL/SQL like the following example procedure code in a package

PROCEDURE processEmps
  ( pDepartmentId IN NUMBER
  )
IS
BEGIN
    FOR rEMP IN
      ( SELECT EMPLOYEE_ID
          FROM EMPLOYEES
         WHERE SALARY > getAvgDeptSalary(pDepartmentId)
      )
    LOOP
        NULL;
    END LOOP;
END processEmps;

(Note: getAvgDeptSalary isn't a very good example, because you could also do that with SQL only, but it's just an example)

Very simple. A function in the WHERE clause which uses the procedure parameter pDepartmentId. What would you expect how the code/SQL is executed?

Do you expect the code is executed in the following order?

1. Procedure processEmps is called
2. Function getAvgDeptSalary gets executed by the PL/SQL engine, because the PL/SQL engine has already all input parameters for the function call. No values from the query are needed. The result gets stored into an internal temporary bind variable.
3. Context switch to the SQL engine which executes the query on EMPLOYEES, restricted to SALARIES greater than the value in internal temporary bind variable.

Sorry you are wrong!

The hole SQL statement is handed over to the SQL engine, which then does a call to the getAvgDeptSalary function. That could be one time if you are lucky and the optimizer is clever enough, but it could also be for each row which is processed by the query!!! So it's the same processing as already described in my Caution when using PL/SQL functions in a SQL statement! You can also easily identify that behavior by yourself, because there are some restrictions in the usage of function calls in the WHERE clause:

• You can only use public functions.
• You can't use the non-positional syntax (at least in < 11g) for the function parameters.

That's because the SQL engine can only use public PL/SQL functions and can't handle the non-positional syntax for parameters.

So if you have somewhere in your code such function calls (like the V or NV functions in Oracle APEX), be aware that this could be a performance bottleneck! Do the function call before the SQL statement and use a variable to restrict the query.

« ... Read full posting ... »

Oracle APEX Developer of the Year... It's official!

Yesterday the new issue of Oracle Magazine has been published which contains the Oracle Magazine Editors Choice awards for 2007. It's official now, I've been named Oracle Application Express Developer of the year 2007! :-)

I was really honored to have been nominated for this award and it was quite hard to keep quiet for the past months, because I was already told during the ODTUG Kaleidoscope conference in June. So it had the same "problem" as John Scott, the last years winner. But I managed to keep quit :-)

It was a really interesting experience! I had to give an interview and they sent Ton Hendriks, a professional photograph from the Netherlands to take some pictures. Like John wrote, it was a little bit surreal. We took some pictures at my workplace and then we went to the Naturhistorisches Museum of Vienna to take some outdoor pictures. So I was posing in this public spot, sweating because it was a really sunny day and a photographer was lying on the ground and taking pictures of me. So you can imaging how the tourists looked when they walked by. :-) Too bad that there is no PDF version available of the Oracle Magazine, because the HTML version doesn't show the picture. I have seen the picture and I think Ton did a really good job!

Thanks again to the Oracle APEX development team for nominating me. I really appreciate that! A big thank you goes also to Sphinx IT Consulting for supporting me in my open source and community efforts.

You don't know what Oracle Application Express (APEX) is? Check out apex.oracle.com!

Update as of 23-Nov-2003: Added the picture from the Oracle Magazine.

« ... Read full posting ... »

A "hidden" gem of Oracle Application Express

Oracle Application Express (APEX) has a "hidden" gem (ok, there are a lot of others too) which in my opinion isn't widely used by the developers.

Be honest, how often do you use the little "View" select list on page definition?

You should, because it has several useful entries which can make your life easier! But let's have a quick look at the different options.

Events

That's a really useful feature for Oracle APEX beginners, because it shows you for the current page the flow of events when a page is processed or rendered. If you check the "View All" radio option it even shows you the events which would fire if you would have defined them. That helps you to identify which event you have to use so that it's executed at the right time. I think that's an invaluable feature to get used to the different events of Oracle APEX!

BTW, it also incorporates the events of page 0 and from application level. I'm not sure if that was always the case, but at least in 3.0.1.x everything is included to get a complete picture.

A screen capture of one of the pages of the ApexLib demonstration application showing the different events.


Objects

Need to know which tables, views, packages, ... are used/referenced by the current page? That option will show all of them. The function is really clever how it get the effected objects. It's creating temporary stored procedures for all the code blocks and checks the data dictionary to get the references to the different objects.

Another nice side effect of this function is that you can determine if all your PL/SQL and SQL code is compilable, because it will also show you all the code blocks where a parsing error occurred.

History

Quick info who last modified the page. Very useful in a multi developer environment.

Export

Have you screwed up the page? Use the export page feature in combination with the "As of x minutes ago" feature to get the old version back.

Enhancement request: Why not add an option "Undo" with the "As of x minutes ago" which restores the page to that point in time?

Groups

Are you using the page group feature? If you don't, you should! Create them with the "Page Groups" link in the "Tasks" sidebar of the overview of all the pages. The assignment is done in the page attributes. But back to that feature. It will allow you to quickly navigate to the other pages which are in the same group!

Referenced

Shows you all objects which are referencing this page. But be aware that it doesn't show all of them, there seems to be a bug with report column links and it will not show you the reference if you have created a manual link in an HTML region or in a SQL statement.

Last visited pages

The last few entries of the select list contain the last few visited pages for quick navigation.


So what do you think? Have I raise your attention to take a closer look at this feature so that you use it more often in your daily development?

Also see

• Find invalid PL/SQL code in your Oracle APEX application

« ... Read full posting ... »