XFILES (Part 2) – APEX Newbie Enhancement Requests

Its not yet “the breeze” I hoped for in regard to “Installing XFiles” on APEX. I am missing parts of the source that XFiles in APEX is build upon and although I know where I can get it, it is not easy to implement it “The APEX” way. For example XFiles is depending on XDB Utilities, packages and methods in PL/SQL, that makes life easier while working with XMLDB and/or have implemented some of the best practices ideas of the XMLDB Development team and others. To do it, installing those, the “neat way”, it should be created in a different Oracle schema and not in the APEX workspace user owner (other database schema’s might enjoy them as well in the database).

A DBA View on Things…

APEX doesn’t have, AFAIK as a newbie, yet a proficient way to make this easy to install, or at least what I thought could be done, via a single packaged install. First of all, as said, there are dependencies to other “outside” packages and privileges. APEX doesn’t checks this but just fails and show a nice report with the failed statements (nice implementation) but would it not be even nicer to have a pre and post installation workspace import pages that checks on criteria needed and/or set by the creator of the APEX application before and afterward, the workspace SQL file is even imported. Ever have used, for example the latest, Oracle Software installer… That kind of thing but be a nice asset. There is now to much stuff, I hope that I actually implement correctly / as the APEX application creator, mend it to be.

An example. Apparently this XFiles demo application has been progressed in his source since the mentioned OOW 2008 Mark & Carl demo. It needs the database compatible parameter to be set to0 Oracle Database version to actually work in respect to demonstrated the Application Security for APEX build upon XMLDB Access Control List Security (ACL’s) features, as demonstrated by Mark during Oracle Open World 2009 (Managing XML Content with Oracle XML DB and Oracle Application Express).

The first page, the XFiles APEX login page, hints this security feature implemented (check box with “Application Principle”)…

APEX Xfiles Login Page with Application Security features

Click on the picture to enlarge

Apex Newbie Enhancement Requests

So wouldn’t it me nice to have a “Check Install Workspace Requirements” – Web Page that before the Workspace will be imported, checks on the requirements in regards to privileges, database feature settings and others and show an overview with failed or success and/or even a “Shall I implement it for you?” (give me some username/password combinations…)”. See the great Oracle Software installer improvements in Oracle 11gR2 for more “line of thinking”.

Also, as said, a pre and post web page that would enable you to execute pre- and post SQL statements via different user schema’s, for example, this XFiles APEX application needs the possibility to have access to Oracle Advanced Queuing methods / privileges, granted before the import of the Workspace script can be executed. The APEX owner schema, in my case the one installed during a Oracle 11gR2 Linux demo database, APEX version 3.2 , is a masqueraded almost full DBA look-a-like account anyway, so maybe some of those scripts could be executed via the APEX application owner and/or the correct database schema.

Finally, lets be honest, APEX is now a grownup (especially what I have seen from the APEX 4.0 application) software application development environment that has be taken seriously, so I would opt for a small enhancement request that DOESN’T create the default EMP, DEPT tables, demo APEX application etc anymore BY DEFAULT (and not the other way around that I as an APEX admin have to disable the functionality) while creating a workspace. As a DBA it is especially annoying that during the workspace creation even a tablespace gets created by default if a new user account is given to work with.


Would have liked to actually be able to show some of the pages currently, but I can’t yet do to trying to figure all the dependencies and gather all the extra PL/SQL packages, so I can create a “Installation Guide” pdf for this application, based on the Oracle 11gR2 Linux or hopefully somewhere in the future a Oracle 11gR2 XE environment. The current stage is that the next page after login crashes with statements that refer to not accessible or not installed packages (the latter). So this is a bit frustration due to my hope that I could already could show you some of the pages regarding its look and feel etc. You could have a look at Managing XML Content with Oracle XML DB and Oracle Application Express in the meanwhile :-(

Keep you posted


Marco Gralike Written by:


  1. November 6

    Hi Marco,
    It is a good practice to create an export for an application (not workspace) when you want to distribute it. When you navigate to Shared Components there is a link “Manage Supporting Objects” to the far right. There you can define all the tasks you complain about (prerequisites, validations, installation scripts etc). You should try that.
    BTW The last link in your post doesn’t seem to work.

  2. November 6

    There is were the “newbie” part comes in, although my gut feeling says that the option you mention is probably not good enough for, for example, checks and grants executed via the SYS user.

    On the other-hand, maybe Mark and I are still to illiterate on the subject and I got the wrong files delivered šŸ˜‰

  3. November 6

    Hi Marco,

    Did you already look at the Supporting Objects feature? That allows you to essentially include scripts (to check for the existence of objects, create objects, seed data etc)? You can also include pre-requisite checks for the application etc.

    Obviously it can’t do anything like granting permission to you (as that would need to be done via the usual DBA grants etc), although I think a lot of what you mention is already available for Supporting Objects.


  4. November 6

    No John, on this part I disagree. The Supporting Objects part is the part that lacks possibilities, and possibilities are the ones, I am not complaining about, but want, if possible, some more sophisticated methods for.

    My car has windows and years ago it had a knob that would able you to open the window. What I am asking for is a knob that I can push so that the process is automated. There is a difference, especially if it prevents faults / enables a more fault tolerant set-up

    Thats why I called it “an enhancement” request. I am a newbie on APEX and the writings here are my experiences so others can avoid my “mistakes”. In all, there is room for APEX improvement, right?

  5. November 6

    Hi Marco,

    Perhaps my comment came across wrong? I certainly don’t disagree with –

    “In all, there is room for APEX improvement, right?”

    Absolutely…100% agree with that, you’ll hear no complaints from me on that one (and there are some huge changes coming in 4.0).

    At ODTUG I also discussed with Joel Kallman that I’d like to see the Supporting Objects be more declarative, rather than the manual task that it is right now. For example, it would be very nice if APEX included the current version (using DBMS_METADATA this is trivial) of your packaged code when you exported it, rather than a version from 3 months ago that you forgot to manually update.

    Again, I don’t disagree with you one bit that the situation could be simplified. But as I hadn’t seen mention of Supporting Objects in your original post I wasn’t sure if you were already aware of it or not.

    Also, for some reason my post didn’t appear on your blog until after Roel etc had replied (even though there were no other comments when I posted it)…so perhaps that added to a bit of confusion šŸ˜‰


  6. November 6

    No worries šŸ˜Ž

    For me its my first real adventure with APEX so I just am trying to write it down “as it feels”… all good suggestions for this newbie are appreciated…

  7. Michael Roessler
    November 7

    In my opinion we seem to be addressing two separate issues as one. Perhaps we could simplify by separating them?

    1) You would like to see added functionality in APEX, and,

    2) You would like to successfully export and import the XFILES application to begin further work on it.

    Let’s find a method of allowing #2 to progress at a rate faster than #1.

    Now, I may have misunderstood so jump in and correct me if needed – but it seems to me as though you are challenged to identify all the database objects required to run XFILES in its current form. Perhaps this is made more challenging by the inability to ask the original author just how he built the app – so you get stuck in a situation where dependent database objects across multiple schemas are hard to identify and include in an export. You are therefore relying on APEX to discover for you and export automatically all those unidentified, dependent database objects.

    An APEX application is just a (smart) method of exposing data/sql/plsql in the database. The XFILES application as developed may not be aware of certain dependency relationships among the database objects or initialization parameter requirements for XML DB.

    This may indicate that the first step before running the XFILES app in a new workspace/database is to rediscover manually those dependency requirements.

  8. November 8

    I see myself as proficient in dealing with database issues since I started off with Oracle V6 and have been a DBA since (including fiddling around and/or managed UNIX systems, the first Oracle Web Servers V2 and onwards, including the first “APEX” offspring, like the “Oracle Web Server killer” and HTMLDB, etc)

    So yeah, I know what you are trying to explain and yeah, I still think that there is room for improvement and yeah I really like APEX, but I know what I am talking about and it could be implemented more efficient.

  9. Michael Roessler
    November 9

    Indeed you are knowledgeable and experienced. I think we all believe you know what you are talking about. Your input into APEX would surely be useful in future iterations. I simply thought that by focusing on the XFILES app and its implementation as a goal separate from improving the future of APEX could assist your goal of building a packaged app around XFILES. My input is intended to assist your goal, not challenge it.

    Best regards.

  10. November 9

    I knew/thought so, but I just wanted to wanted to clarify my position.

    I don’t want to spend too much time on “proving that I like APEX”, during every small piece of criticism or enhancement proposal that pops in my mind while working with this 3.2.0 version (standard issue Oracle V11. 43 bit on Linux)

Comments are closed.