Monday, November 24, 2008

The DBMS_PROFILER companion for Oracle Apex is (finally) available

Always check out the original article at for latest comments, fixes and updates.

The DBMS_PROFILER companion for Oracle Application Express that i presented at Oracle Open World 2008 is finally available for download.

Since i came back from OOW, i spent most of my spare time testing and checking the installation and deinstallation scripts, which was a non trivial task for an application that can work in a variety of possible configurations, although it is supposed to install and uninstall itself in an almost point and click fashion.
I've also invested some time improving the look and feel of the application compared to the initial version and writing the on line help for each page. There are also a few new features compared to the preliminary version i presented in San Francisco, most of them deal with automatic installation checks, auto-testing and migrating existing data.

Most of the working details are covered by the on-line help inside the application, whilst the installation details are given in the download page, so i am not going to write this stuff twice here, but let me recap the main goals of this application:
  1. to provide a human understandable format for the output of Oracle's built-in PL/SQL profiler;
  2. to minimize the cost/benefit ratio of using the DBMS_PROFILER for optimizing your PL/SQL code. That's the easy bit as this tool costs zero to you ;-)
  3. to take charge of some annoying operations like checking if the repository user was granted the debug privilege, checking if all the required objects are properly installed, providing a sample procedure to perform a test and so on.
  4. to show you that Apex is cool for developing productivity tools like this one.
When i presented the application at OOW i remarked the fact that it took very little time to develop a working application with Oracle Apex: that statement still holds today for an application created for internal use without all the bells and whistles that are necessary when you want to distribute the software to the general public. As you can easily guess, writing the help and designing the installation/deinstallation scripts plus all the auto-check functionality is a time consuming task that is usually not necessary when the application is only used by selected individuals or within a small development team.

Hopefully i'll find the time in the next days to come up with some video about installing and using this tool, but for now, this is it.

Although i spent quite a lot of time checking, testing, changing, amending and so on, the current version should be considered a beta release.

See more articles about Oracle Application Express or download tools and utilities.


Glog said...

I eagerly anticipate reading additional documentation.

I am atthe point where I can run the application but
entering a schema login results in the error message "Access denied by Application security check
Error you must log on as to use this application "

Byte64 said...

i'd say that you installed the application in schema X but then you are trying to log on as user Y.
The only exception to this case is when user X = PROFI, in which case you should be able to log on using a different database account.

Does this scenario apply to your case?

Thank you,

Glog said...

YY_PROFILER_PKG is in the PROFI schema. I logged in to APex as Profiler_Admin and logged in to the application as PROFI.

&APP_SCHEMA looks undefined when the erorr message is displayed.

I will try and reinstall. APEX is new to me.

Byte64 said...

i'm going to see why APP_SCHEMA is empty, but probably i gave you an incomplete answer initially.

The installation in the PROFI schema/PROFILER workspace makes sense if you need to share the repository among several users.

As i outlined in the installation page, *after* installing the application in the PROFILER workspace, you should install other instances in other workspaces associated to the schema where the procedures to be profiled are located. This is really necessary only if the following applies:
1. you want to log on as user X because you locked out the account PROFI.
2. you want to be able to perform certain operations from within this application, like granting privileges, checking privileges and so on.

If you don't need to do those operations from the GUI, but you prefer to execute them manually, you can log on as PROFI (supposing you have not locked the account), because it will be sufficient to inspect the content of the repository.

Alternatively you can install the application in the schema, associated to a particular workspace, which is called LOCAL mode, that way you'll be able to perform all operations as user X and with the maximum privacy.

If you need further assistance, you can contact me through this page.

Thank you,

Byte64 said...

there is still one detail worth noting:

if you are going to install the application in other workspaces and you still want to use a single repository, do not install supporting objects, so, when asked: "do you want to install supporting objects for this application?" answer "no".


yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles