New JDeveloper blogger

I bumped into an old university friend at the AUSOUG Melbourne conference in 2007, and something must of rubbed off. Marc Thompson has started a blog on his JDeveloper "learnens". Good luck in your new new endeavours Marc.

JDev: Global buttons in page templates revisited

Recently Frank Nimphius blogged on 2 strategies for implementing global buttons in page templates.

The first relies on the use of the ADF Binding Layer and declaratively creating bindings in each consuming web page's Page Definitions file. I'm a big fan of the declarative approach as the less Java code I have to write the better.

From what I can gather in Frank's solution, it requires the commandButtons actionListener to reference #{bindings.First.execute}, which relies on the consuming web page to have the First action within its Page Definitions file. However if we extend our page template to have its own Page Definitions file (either created in the Create JSF Page Template dialog, or by right clicking the existing Page Template, selecting Go To Page Definitions and accepting yes when prompted to create the file), the EL expression #{bindings} now refers to the page template Page Definitions, not the consuming page's Page Definitions.

As such I'd like to expand upon Frank's first option and propose a 3rd method to inject the global button functionality into the page templates via attributes and gets around this limitation.

As a disclaimer while this method works in my testing under JDeveloper 11g TP3, I have not tested it in a production system, and may not be viable in later versions of JDeveloper. Therefore it is essential you test this mechanism before using it.

What we want to do is rather than randomly putting commandButtons around our page, that we build a standard toolbar for the entire application, with Create Record and Delete Record buttons (or Next/Previous buttons from Frank's example). To satisfy this requirement we're going to:

1. Create a Page Template
2. Add two command buttons with labels Create and Delete, with a toolbar for the Template's Static Content.
3. Add a single Facet named "content".
4. Add two attributes that accept Create and Delete actions from the consuming web page.

Then we're going to retrofit an existing web page containing an <af:table> and bound Create and Delete buttons to use the Template. It will:

1. Place all its content, namely the table that shows the Events within the "content" Facet of the Page Template.
2. Define two attributes passing in the action bindings for the local page's Create and Delete actions.

Follows these steps to implement:

1. Create a new Page Template with the following characteristics:

a. Name the file sageTemplate.jsft
b. Place the file under a new directory public_html/templates
c. Call the template sageTemplate
d. Leave the Page Definitions check-box unchecked.
e. Create a Facet named "content".
f. Create 2 attributes createButton and deleteButton, of type oracle.jbo.uicli.binding.JUCtrlActionBinding, required=true

(Note: as per my discussion's on the JDev forums, I'm not yet convinced required=true does anything)

2. Once the Template is created, insert the following code into the Static Content part of the Template:

... top of page followed by Static Content ...
    </af:xmlContent>
    <af:toolbar>
      <af:commandToolbarButton
        actionListener="#{attrs.createButton.execute}"
        text="Create" id="createButtonId"/>
      <af:commandToolbarButton
        actionListener="#{attrs.deleteButton.execute}"
        text="Delete" id="deleteButtonId"/>
    </af:toolbar>
    <af:panelFormLayout
      partialTriggers="createButtonId deleteButtonId">
      <af:facetRef facetName="content"/>
    </af:panelFormLayout>
  </af:pageTemplateDef>
</jsp:root>

Note the <af:panelFormLayout> wraps the <af:facetRef>, and forces a PPR on either of the buttons being pressed.

3. Open your existing web page and add the <af:pageTemplate> tag to import the template. Place the contents of the existing page into an appropriate <f:facet> tag.

<?xml version='1.0' encoding='windows-1252'?>
<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page"
          version="2.0"
          xmlns:h="http://java.sun.com/jsf/html"
          xmlns:f="http://java.sun.com/jsf/core"
          xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
  <jsp:directive.page contentType="text/html;charset=windows-1252"/>
  <f:view>
    <af:document>
      <af:messages/>
      <af:form>
        <af:pageTemplate
          viewId="/templates/sageTemplate.jsft.jspx">
          <f:attribute name="createButton"
            value="#{bindings.Create}"/>
          <f:attribute name="deleteButton"
            value="#{bindings.Delete}"/>
          <f:facet name="content">
            <af:table ..... and so on ..... </table>
          </f:facet>
        </af:pageTemplate>
      </af:form>
    </af:document>
  </f:view>
</jsp:root>

4. Delete any Create or Delete commandButtons from the page, but ensure that the Page Definitions remain for the Create and Delete actions.

5. For the <af:table>, ensure row selection is turned on for single rows.

6. Within the <af:pageTemplate> include the following <f:attribute> tags:

<f:attribute name="createButton"
  value="#{bindings.Create}"/>
<f:attribute name="deleteButton"
  value="#{bindings.Delete}"/>

Note how the value EL expressions pass in the Create and Delete bindings, namely instances of oracle.jbo.uicli.binding.JUCtrlActionBinding.

7. Run the web page and test that the Page Template delete button works correctly. The create button is a little more tricky because you would probably would want the application to navigate to another screen. As such you could pass in via a parameter a navigation named action too.


One of the things I like about this solution is the consuming web page can be selective about what actions it wants to pass to the Page Template. For instance if we have 2 sets of Create/Delete buttons on our web page for a master-detail relationship, we can choose which pair of buttons to inject.

Thanks to Frank for posting his original solutions and giving me the spark of inspiration for this post.

Readers: why not introduce yourself?

My webcounter on this blog reveals many returning readers from around the world located in all sorts of interesting places, but they all remain pretty nameless besides their IP address (hi 148.87.1.172!).

I'd love some of my readers to leave a comment of where they're from, what they do, and their general interests.

Hopefully such a request will receive a couple of responses otherwise I'm going to look a little lonely ;)

An alternative JDeveloper forum

Oracle's own OTN JDeveloper Forum provides a good place to drop technical questions about the whole JDeveloper suite. Yet there is another less known avenue better suited to overview questions.

ODTUG maintains a number of mailing lists including the JDeveloper/Java specific ODTUG-JAVA-L list. This list among 13 odd others (including Apex) is open to the public. (If this link doesn't work, access the homepage and click on "Join an email list").

This list unlike OTN's forums is mostly silent, but on occasion explodes into activity. It is actively monitored by Oracle's JDeveloper Product Managers, and other known Oracle-JEE names such as Paul Dorsey and John Flack.

Questions tend to be less technical in nature and more of an overview which elicit many good and detailed points of view. For instance there is a current discussion on "Oracle Forms and PLSQL conversion tools [to JDeveloper]". Unfortunately if you're not a member of the list, there's no way that I know of to read those posts (can somebody correct me on this? .... is there a public and up to date aggregator?).

After you join this list you may not get any posts for sometime, but when you do, they're well worth the read. Thanks to ODTUG for making these lists public.

Advert: JDeveloper workshops in Melbourne, Adelaide, Perth ~2 weeks

It's less than 2 weeks until SAGE Computing Services in conjunction with the Australian Oracle User Group (AUSOUG) are giving you the chance to attend this whole day event on Oracle's latest JDeveloper 11g Technical Preview 3 version. The first leg of the series is due in Melbourne, Adelaide in Perth in 2 weeks time.

This 1 day workshop will introduce you to the simplicity of JDeveloper 11g's drag-n-drop facilities, wizard and editor IDE, as well as declarative programming model for quickly developing a web application using ADF Business Component and ADF Faces, without a single line of Java!

Check out the AUSOUG event web page for more information and how to register. We look forward to seeing you there.

Jonathan Lewis "down under"

To continue my down under series, this time I'm happy to report to my huge Australian reader base (hi Mum!) that Jonathan Lewis will be visiting Australia on an Eastern States roadshow during March.

I figure that most people know already from Jonathan's own blog. However while teaching JDeveloper today one of my students pointed out no other Australian Oracle bloggers post about Australian Oracle events, so it might as well be me. I can't find this event on Oracle's own events.oracle.com, so consider this a public service announcement ;)

In other Oracle Australian news, Oracle has discovered that they have in fact sold Oracle licenses to at least several organisations in Perth, Brisbane and Adelaide, and are considering sending the next speaker to anywhere besides Melbourne and Sydney in recognition that neither city are the center of the Universe.

Oracle OID performance tuning

At a client's site we were recently asked to investigate performance issues for an existing single sign-on (SSO) server on the top of an Oracle Internet Directory (OID) box. This post self-documents the issue and the solution we arrived at, and maybe useful to other readers hitting similar issues. This post isn't a particular an easily-digestible blog entry so may not be of interest to the casual blog reader.

Performance Problems

Within an existing production environment, logging into Oracle Portal using SSO for users in a defined LDAP group (lets refer to this group as Alpha) took an unacceptable amount of time (~25 sec) and needed to be addressed. The Alpha users are the predominate users of the system. Logging in as a second LDAP group (referred to hereafter as Bravo) experienced acceptable sub-second responses. Once logged in the rest of the Oracle Portal system behaved acceptably indicating a performance issue with SSO and OID.

Other performance issues were also seen, including within the OID web directory interface, selecting the Alpha group simply did not return, while selecting the Bravo group returned after a considerable amount of time.

In the case of the development environment, much faster responses were experienced compared to the production environment and the OID web directory did return in both cases.

The poor to non existent performance relating to users within the Alpha LDAP group on production was at issue and had to be addressed.

(As a sub-note to this section, there were also other speed related issues to this problem. On attempting to manually load users into OID via the PL/SQL package dbms_ldap, we discovered in development loads times for sets of 1000 members in the Alpha group went from 1 minute to 15 minutes.)

OID LDAP Characteristics

As there was a considerable difference in performance between the development and production environments, we first decided to investigate what was different between the two environments. We discovered the following:

Development LDAP Characteristics

Contains 29 groups, most of which had 10 entries or less
Group Alpha had ~25,000 entries
Group Bravo had ~2,800 entries
Login took approximately 1 second as an Alpha member.

Production LDAP Characteristics

Contains 25 groups, most having 10 entries or less
Group Alpha had ~140,000 entries
Group Bravo had ~4,900 entries
Login took approximately 25 seconds as an Alpha member.

As can be seen, the key difference between the 2 environments was the 25,000 vs 140,000 members in the Alpha group.

OID Performance Tuning Guidelines

At this time we started a search for OID tuning recommendations, and pretty quickly stumbled across the document Oracle Internet Directory Tuning and Configuration - A Quick Reference Guide September 2007.

One of the key points to take from the OID document is the 2nd paragraph:

"The out of the box OID configuration is not optimal for most production or test deployments. It is hence imperative that some basic tuning and configuration changes are performed after OID installation to achieve optimal performance and availability."

Note our emphasis on "is not optimal for most production or test deployments." I'm unsure if this comment is only mentioned within the tuning documentation; I hope Oracle has taken care to document this elsewhere.

For the OID setup we'd inherited this had not been undertaken, and as such we decided (perhaps wisely! ;) to follow the recommendations of the Oracle tuning documentation. The rest of this post described the current environment as relevant to the tuning recommendations, as well as the recommendations we decided to follow, and the steps to implement them.

Pre-Tuning Upgrade Server Characteristics

The following settings describe our development and production database server and OID settings before the tuning upgrade, as relevant to the tuning recommendations. On reading the tuning documentation the reason why these are documented here will become clear:

Development Database Characteristics

Hardware: 4 CPU 4 GB RAM
Database version: 10.1.0.3.0 EE
OID version: 9.0.4.1.0

init.ora settings:

sga_target: 1392508928 (big integer)
sga_max_size: 1392508928 (big integer)
db_cache_size: 150994944 (big integer)
shared_pool_size: 150994944 (big integer)
session_cached_cursors: 0
cursor_space_for_time: FALSE
processess: 600 (integer)
pga_aggregate_target: 463470592 (big integer)

Bitmap index:

CREATE INDEX ei_attrstore ON ds_attrstore (entryid);

Development OID Settings

orclmaxcc (Max Number of DB Connections): 2
orclserverprocs (Number of Child Processes): 1
orclecacheenabled (Enable Entry Cache): 1
orclskiprefinsql (Skip Referral Process): 0
orclldapconntimeout (LDAP Connection Timeout): 0
orclmatchdenabled (Enable MatchDN Processing): 1

Production Database Characteristics

Hardware: 4 CPU 8 GB RAM
Database version: 10.1.0.3.0 EE
OID version: 9.0.4.1.0

init.ora settings:

sga_target: 1392508928 (big integer)
sga_max_size: 1392508928 (big integer)
db_cache_size: 905969664 (big integer)
shared_pool_size: 285212672 (big integer)
session_cached_cursors: 0
cursor_space_for_time: FALSE
processess: 150 (integer)
pga_aggregate_target: 408944640 (big integer)

Bitmap index:

CREATE INDEX ei_attrstore ON ds_attrstore (entryid);

Production OID Settings

orclmaxcc (Max Number of DB Connections): 2
orclserverprocs (Number of Child Processes): 1
orclecacheenabled (Enable Entry Cache): 1
orclskiprefinsql (Skip Referral Process): 0
orclldapconntimeout (LDAP Connection Timeout): 0
orclmatchdenabled (Enable MatchDN Processing): 1

OID Tuning Recommendations Accepted

As documented in the OID tuning document, following are the recommendations we decided to follow:

Database init.ora Entries

Our database does not use automatic tuning. The following settings were therefore recommended:

db_cache_size: 1200M
shared_pool_size: 200M

In addition we accepted the following recommendations:

session_cached_cursors: 100
cursor_space_for_time: TRUE
pga_aggregate_target: 300M
processes: 500

OID configuration changes

The following tuning recommendations were accepted within the Oracle tuning documentation and are applicable to our OID environment:

• As per point 1 on page 5 of the Oracle documentation, update orclmaxcc=10 (Max Number of DB Connections) and orclserverprocs=4 (number of CPUs). The LDAP instance needs to be restarted at the end of this change.

• As per point 3 on page 5 & 6 of the Oracle documentation, LDAP referrals are not used, allowing orclskiprefinsql (Skip Referral Process) to be set to 1. Our understanding is LDAP referrals is when an LDAP entry refers to another LDAP entry in entirely different LDAP tree. As we only have one OID LDAP install in each environment, referrals are not used.

• As per point 4 on page 6 of the Oracle documentation, set the orclldapconntimeout=60 (LDAP Connection Timeout).

• As per the OID Database Statistics section, run oidstats.sql once, as we had already preloaded considerable amount of members into the Alpha group. I suspect this step is not that relevant as our 10g database will have collated statistics on the underlying table once a night anyway.

• As per the Using OID Server Entry Cache section on page 10, as there will be frequent updates to the directory in our environment, it's suggested Entry Cache is turned off (orclecachenabled=0).

• As per the Large Group Entries section on page 7, the following SQL DDL will be run:

drop index ei_attrstore;

create bitmap index ei_attrstore
on ds_attrstore (entryid, attrname)
tablespace olts_attrstore nologging compute statistics;

alter index ei_attrstore noparallel;

OID Tuning Recommendations Rejected

The following points were considered within the Oracle tuning documentation and rejected with the following reasons:

• As per point 2 on page 5 of the Oracle documentation, DIP features are currently used for the Active Directory and Portal integration.

• As per point 5 on page 6 of the Oracle documentation, in contacting the author Ajay Keni of the Oracle tuning document, Ajay noted this tuning feature is important if the depth of the BaseDN (or its individual components) is substantial. It shouldn't apply in our client's case as the baseDN is appears to be relatively short "dc=aaa,dc=bbb,dc=ccc,dc=au"

Ajay Keni gave the following description:

(start quote)
Lets say, a client sends an LDAP search with BaseDN "cn=jdoe,ou=finance,l=emea,dc=acme,dc=com".

Now, lets say as part of the search request processing OID figures out that this DN does not exist in the Directory. According to the protocol specification, OID is required to figure out whether "ou=finance,l=emea,dc=acme,dc=com" exists, if it doesn't exist, then figure out whether "l=emea,dc=acme,dc=com" and so on until "dc=com". The nearest DN in the heirarchy will be sent as the matchDN value as 'additional info' in the search result. This is the default behaviour.
If you set "orclmatchdnenabled=0", then OID will only check for the existence of the immediate parent i.e. "ou=finance,l=emea,dc=acme,dc=com" and not all the way up to "dc=com".

As you can see, this only comes into play when the baseDN of the search doesn't exist in the Directory and the change in behaviour with this setting affects the 'additional info' part of the result and not the result itself. But, in some deployments we have seen such queries coming in and depending on the depth of the DIT the processing for matchDN can be expensive.
(end quote)

Production OID Change Plan

With the above recommended changes accepted, the following documents our installation change plan:

(Note some of the following OID setting changes are dependent on the version of Oracle Directory Manager client tool you have installed. Unfortunately subsequently I've uninstalled my Oracle client and cannot give you the specific version at this time)

• Ensure nightly backups of the OID is complete.

• Notify all users that the OID server will be unavailable (and therefore all systems relying on OID and SSO for login).

• Gracefully stop the OID OAS server.

• As the database administrator, change the following init.ora parameters:

* db_cache_size: 1200M
* shared_pool_size: 200M
* session_cached_cursors: 100
* cursor_space_for_time: TRUE

• Restart the OID database.

• Restart the OID OAS server.

• Start the Oracle Directory Manager client tool, and log in as cn=orcladmin on the production server.

• Select the root level cn=orcladmin@.... node.

• Select the System Operational Attributes tab on the right hand side if not already selected.

• Set "Enable Entry Cache" (orclecachenabled) = 0 (record the current setting for rollback purposes)

• Select the Query Optimisation tab.

• Set "Skip Referral Process" (orclskiprefinsql) = 1 (record the current setting for rollback purposes)

• Set "LDAP Connection Timeout" (orclldapconntimeout) = 60. (record the current setting for rollback purposes)

• In the object tree, expand the Server Management option, then the Directory Server option, and finally select the Default Configuration Set option.

• Select the General tab on the right hand side if not already selected.

• Set "Number of Child Processes" (ocrlserverprocs) = 4 (record the current setting for rollback purposes)

• Set "Max Number of DB Connections" (orclmaxcc) = 10 (record the current setting for rollback purposes)

• Exit Oracle Directory Manager.

• Restart the OAS.

• Check that all services are back up.

• Open a telnet/ssh session to the OAS host.

• Set the environment to infrastructure database.

• cd $ORACLE_HOME/ldap/admin

• Start SQL*Plus as the ODS user.

• Run oidstats.sql (this may take a while)

• Exit

• Within OEM stop all OID instances.

• Log in as the ODS user via SQL*Plus in production, and run the following SQL:

drop index ei_attrstore;

create bitmap index ei_attrstore
on ds_attrstore(entryid, attrname)
tablespace olts_attrstore nologging compute statistics;

alter index ei_attrstore no parallel;

• Restart the OID instance within OEM.

• Log into the Production portal as Alpha user and judge performance.

• Log into the Production portal as Bravo user and judge performance.

• Notify users that the system is now available.

Production Success

On completion of the above changes, logging in as either a member of the Alpha or Bravo groups saw sub-second responses via the SSO to Portal web pages. In the case of the Alpha members this was an improvement by a factor of 25 times, and provided acceptable login times.

I'd like to thanks Ajay Keni and Vikki Lira for their assistance in writing this post.

If you read this posts, other links you may be interested in:
100M User Directory Benchmark OID v10.1.4

Oracle Portal upgrades - Self referencing portlet issue

At an existing client's site we recently migrated some working Oracle Portal PL/SQL "self-referencing" portlets we had created under Portal 9.0.4 to 10.1.4. On migrating the portlets, on resubmitting a portlet to itself we started to receive the following error messages unexpectedly:

Internal error (WWC-00006)
An unexpected error has occurred in pages: ORA-06502: PL/SQL: numeric or value error: character string buffer too small (WWC-44847)

The following describes the issue and solution for self documentation purposes and may be useful to others using Oracle Portal and planning an upgrade.

Self Referencing Portlets

In Oracle Portal it's possible to have a portlet call itself passing in parameters from its previous incarnation at runtime. Among other names this is often referred to as a self referencing portlet.

The Oracle PL/SQL Portal Developer Kit (PDK) has an example portlet demonstrating this technique called "Parameters Portlet". This portlet is a combination of the PL/SQL packages PARAM_PORTLET and PARAM_FORM_PKG as part of the PDK within the samples directory.


Basically the first time the portlet renders the Write-Your-Name-Below field is empty and no name shows in the message above. Once the user enters a name and presses the "Display in this portlet" button below, the portlet calls itself passing in the entered name, which is then displayed in the screenshot you see above.

Oracle Portal 9.0.4 URLs

Without going into the entire mechanics of how portlets call themselves, the portlet on a submit will make a call to owa_util.redirect_url passing in its own URL and the parameters needed to call itself. For example in the PDK PARAM_FORM_PKG, the method RECORD has the following call:

owa_util.redirect_url(curl=> p_back_url || '&' || p_reference_path || '.p_my_name_is=' || p_my_name_is);

Basically this method allows the portlet to call itself, where a URL is passed in with a number of URL parameters, with the URL end result something like this under a pre-10.1.4 Portal installation:

http://yourserver:7778/portal/page?_pageid=155,1 &_dad=portal&_schema=PORTAL &14008_PARAM_PORTLET_6176685.p_my_name_is=Chris

(Please note the URL has been split with spaces in this blog to force new lines on wrapping for readability)

You'll note that the URL that the owa_util.redirect_url constructs places the parameter p_my_name_is at the end of the URL. The prefix it adds before the parameter p_my_name_is name (namely in this example 14008_PARAM_PORTLET_6176685) is to assist Portal in tracking the parameter back to a particular user portlet instance (remembering that multiple users may be accessing the Portlet at any one time)

The remainder of the URL before our parameter is what Portal needs to work out what page to return to, or more specifically where the Browser should go to, in order to redisplay the appropriate portlet with the passed in parameters. In particular note Portal passes in the _pageid, _dad and _schema parameters before the parameter we added.

Finally and most importantly note how each parameter key-value pair is delimited by an ampersand, while the first parameter is preceded by a question mark indicating the start of the parameter list within the URL.

Oracle Portal 10.1.4 URLs

As of version 10.1.4 and onwards Portal has changed the default URL syntax for a page, removing the page number syntax and using hard page names, predominately to allow bookmarking in browsers without the page ID changing underneath. The resulting URL syntax in a 10.1.4+ install should look something like the following:

http://yourserver:7777/portal/page/portal/MyPage? 2585_PARAM_PORTLET_62557797.p_my_name_is=chris

(Again URL has been padded with spaces to force wrapping)

Again you'll note the parameter p_my_name_is is prefixed by 2585_PARAM_PORTLET_62557 to help Portal track the parameter value against a particular user. The actual numbers are immaterial and something Portal uses, not developers.

You'll also note within the Portal 10.1.4 code the previous _pageid, _dad and _schema parameters no longer exist, instead we have an actual page name to refer to.

The Problem

However the owa_util.redirect_url code we migrated from Portal 9.0.4 to 10.1.4 will generate the following URL:

http://yourserver:7777/portal/page/portal/MyPage &2585_PARAM_PORTLET_62557797.p_my_name_is=chris

(Split for readability again)

Note the ampersand rather than the required question mark between the page URL and our first URL parameter. This mistake has occurred because the pre 10.1.4 code assumes that on adding our parameter, a number of parameters will exist on the URL beforehand, of which the first will place the URL parameter delimiter question mark.

In turn this partially explains the error messages you will see in a 10.1.4 and later Portal. Effectively Portal is looking for an object name of something like " MyPage&2585_PARAM_PORTLET_62557797.p_my_name_is=chris". Portal doesn't realise that the last part of the URL is actually a parameter. It's a case of Portal doing what you told it to do, rather than what you want it to do. Unfortunately the error message is a poor one here, because rather than reporting the equivalent of No-Such-Object-Found, the URL is obviously overflowed some internal PL/SQL string buffer and raised an exception when grabbing our wrong-named object.

Fix for 10.1.4+

As shown as of 10.1.4 onwards, because there are no parameters in the preceeding URL, we need to change our call to owa_util.redirect_url to insert a question mark instead such as:

owa_util.redirect_url(curl=> p_back_url || '?' || p_reference_path || '.p_my_name_is=' || p_my_name_is);

If we decided to add further parameters, they would be delimited by further ampersands.

Fix for any Portal Version

The easiest manner to fix this problem is to rewrite the owa_util.redirect_url call as follows:

IF instr(i_page_url, '?') > 0 THEN
v_param_deliminator := '&';
ELSE
v_param_deliminator := '?';
END IF;

owa_util.redirect_url(curl=> p_back_url || v_param_deliminator || p_reference_path || '.p_my_name_is=' || p_my_name_is);

Post Xmas JDeveloper gift - training license restriction lifted

Oracle has been kind enough to give another small JDeveloper gift, just a tad late for Christmas. Grant Ronald has kindly advised me that Oracle has removed the "[You may not] - use the programs to provide third party training" restriction from JDeveloper's license conditions.

This change currently applies to the production release of JDeveloper, specifically 10.1.3.3. It does not apply to the 11g TP3 release as this is a beta product. I'm advised once 11g goes gold (production) this same exemption will apply.

Check it out for yourself on the 10.1.3.3 download page by selecting the license agreement hyperlink. A direct link to the license agreement is here.

The significance of the relaxation of this specific license restriction is that it now puts JDeveloper on equal footing to Oracle Express Edition (XE) and Oracle Application Express (APEX). This may stir up enterprising training organisations to deliver JDeveloper workshops around the world much like the APEX Evangelist crew. And yet again it shows how Oracle has broken its traditional mold and is offering all these tools for (near) free. I've no complaints with that. I like free.

As a disclaimer my employer SAGE Computing Services an Australian Oracle training company offers a 5 day JDeveloper 10.1.3 ADF training course, as well as 1 day overview training course, both of which I'm the author of. No doubt you can see why I'm interested in this license change.

Thanks to Grant Ronald with his assistance on this post, and letting me pull his ear.

+1 pint for Grant ;)

AUSOUG 2008 Conference Dates

I received my "speaker thank you" letter for my 2007 AUSOUG conference presentation last week. One tidbit of information in the letter that other speakers and delegates may be interested in is the 2008 AUSOUG Conference Dates. To quote the letter:

"The total number of people attending the conference series (Perth and Melbourne) exceeded our 2006 attendances making the 2007 series a most successful one to date. We have high expectations for the 2008 Conference Series that will be held at Sheraton Perth Hotel 6-7 October 2008 followed by Conrad Jupiters, Gold Coast from 13-14 October 2008."

This puts the AUSOUG conference series once again just after OOW which will be in September this year. I've also been trying to confirm one rumour I've heard that the NZOUG is planing a New Zealand conference just after the AUSOUG conference, which is a great plan to pick up any international speakers flying home from the East Coast of Australia.

Regards the Gold Coast conference, for those who don't know Australia, the Gold Coast is approx 1-2 hours drive south of Brisbane, the capital of Queensland on the (North) East Coast of Australia.

If (assuming I'm invited back) and when I receive the AUSOUG call for papers I'll publish that here too.

Tag! I'm it dag-nab-it!

Having been tagged by Dan Norris, here are 8 random things about me:

• Before I started down the heady road of IT, for 6 years I used to push trolleys, scrubbed meat rooms, and cut fruit and veg at the local "SupaValu" supermarket. My most favourable memory was finding a tree frog in the lettuce bin. My least favourite task was emptying the meat room's grease pit once a week. The most important lesson I learned: while the customer is always right, they're only buying milk and bread, so it's pretty trivial all round really.

• I'm a "2.5 generation Australian", with Scottish heritage before that. In fact along with my Scottish surname Muir, my middle name is Campbell, which was my mother's maiden name (oops, there goes one of my password security questions!). My Scottish heritage probably explains my long pockets.

• My parents were early outback teachers in central Australia, teaching at aboriginal settlements like Yuendumu (pronounced u-n-da-moo).

• Before I reached high school, I'd attended 11 primary schools in and around Australia.

• I've lived and worked in Pittsburgh USA, London and Marlow on Thames UK.

• I've been a programmer all my adult working life, and started out many years ago working on a C++ real-time distributed database for Westrail, a defunct Western Australian State government railroad agency. The project was eventually canned after millions of dollars of tax payers' money and several years development, a whole generation of programmers burnt out, and probably the best learning exercise ever on how not run an IT project. The term IOCS still lives in infamy in programmer circles in Perth.

• I'm a sucker for British comedy, including the Goons, Yes (Prime) Minister, Douglas Adams, Monty Python, Terry Pratchett, Rumpol of the Bailey, Coupling, Green Wing, Black Books, and so on. "If it ant British, it ant funny."

• I've 4 pleasures in my life, of which my partner Jenny and my toddler daughter Emily are the 2 most important, while cycling and reading are the other simpler 2. I'm still working on a daring plan to combine all 4, but might have to wait until Emily is old enough to read and Jenny will agree to do all the work on a tandem.

I'm going to cop out like Justin and only "tag" 6 bloggers as I know a couple others I would have chosen are already "it". So tag, you're it: Andrejus Baranovski, Doug Burns, Tim Hall, Frank Nimphius, Grant Ronald, and Shay Shmeltzer.

ORA-04044: procedure, function, package, or type is not allowed here

In maintaining an existing system, we recently came across an interesting Oracle error in a PL/SQL module "ORA-04044: procedure, function, package, or type is not allowed here." This error raised itself when opening a cursor using dynamic SQL. As usual we all swore black and blue that we hadn't made any changes to the underlying schema, yet the dynamic SQL was now failing. It was the day before Christmas and we weren't happy! The problem had to be solved. I'll document it here for future reference and others to make use of.

The following SQL*Plus spool demonstrates the scenario where the error occurred:

SQL> CONN alpha/alphapwd@somedb
Connected.
SQL>
SQL> CREATE TABLE some_object
  2 (somefield NUMBER);

Table created.

SQL> GRANT SELECT ON some_object TO bravo;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM some_object FOR alpha.some_object;

Synonym created.

SQL> CONN bravo/bravopwd@somedb
Connected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE test
  2 IS
  3   TYPE ref_cursor_type IS REF CURSOR;
  4   c_ref_cursor ref_cursor_type;
  5   v_sql VARCHAR2(1000) := 'SELECT 1 FROM some_object';
  6   v_dummy NUMBER;
  7 BEGIN
  8   OPEN c_ref_cursor FOR v_sql;
  9   FETCH c_ref_cursor INTO v_dummy;
 10   CLOSE c_ref_cursor;
 11 END;
 12 /

Procedure created.

SQL> EXEC test;

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE some_object
  2 IS
  3 BEGIN
  4   NULL;
  5 END;
  6 /

Procedure created.

SQL> EXEC test;
BEGIN test; END;

*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
ORA-06512: at "BRAVO.TEST", line 8
ORA-06512: at line 1

SQL> DROP PUBLIC SYNONYM some_object;

Synonym dropped.

SQL> EXEC test;
BEGIN test; END;

*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
ORA-06512: at "BRAVO.TEST", line 8
ORA-06512: at line 1

As you can see, the first time under which the method bravo.test containing our dynamic SQL is executed, the code works fine. However at a later date another coder introduced a procedure bravo.some_object which conflicts with alpha.some_object.

Normally the database would complain that you can't create same named objects and this protects developers against silly mistakes and having to be explicit about the object type they're selecting against. However as the objects here exist in different schemas, the database is happy with this and lets the developer proceed.

Once the some_object procedure is created and the bravo.test is run, it's dynamic SQL attempts to resolve SELECT 1 FROM some_object, and bravo.test discovers some_object is a procedure in the local schema (remembering the database's preference is to use local schema objects first), not the table from the original alpha schema. The database thus throws ORA-04044 at runtime, basically saying it wants a table or view, not a procedure, function, package or object type.

You can see from the developer's point of view the confusion when this error occurs, as nothing has changed in the bravo schema besides introducing a new procedure. Why would adding a new object cause this problem? And potentially this is even more confusing as the creation of the procedure could have been weeks ago, entirely forgotten, but only now the dynamic SQL is falling over.

On encountering this scenario we had a slightly more complicated problem to debug. In our case the alpha schema had been granted execute privileges on bravo.test. When the alpha schema executed bravo.test ORA-04044 is still thrown. However if you extract the SQL statement from the dynamic SQL call and run it within the alpha schema, the SQL statement will run fine! Talk about a red herring.

Our initial mistake was to forget about that PL/SQL modules are executed with definer-rights by default, meaning they are executed in the schema of the PL/SQL module owner, in test's case bravo, rather than alpha. As soon as we ran the SQL statement in the bravo schema, the same ORA-04044 occurred.

The obvious hack to fix this problem is to change the dynamic SQL statement to prefix the table name some_object with the schema dot notation (eg. SELECT 1 FROM alpha.some_object). In this way regardless if the dynamic SQL is run in the bravo schema, it's guaranteed to retrieve results from the alpha schema.

However some developers and DBAs don't like prefixing queries with schema dot notation for flexibility. On thinking about this I guess an alternative solution that will reduce the chance of the problem occurring is to ensure your object names have some sort of prefix or postfix notation. For example p_ for procedures, f_ for functions, _tbl for tables etc. This sort of notation I note is falling out of favour at a number of sites, but in this situation could have saved us much time debugging.

I'd also be interested in other methods anybody thinks would have avoided this issue in the first place.

Steven Feuerstein "down under"

For those "down under" in Australia who appreciate Steven Feuerstein PL/SQL offerings, Steve will be presenting in both Sydney and Melbourne for Quest Software on the 17th and 21st of January respectively.

It's a shame Steve can't make it to other Australia cities after coming all this way, but Oracle doesn't sell any licenses outside of Sydney and Melbourne anyhow, so it would be a pointless exercise I guess.

Feel free to wear *all* your Toad gear for the event too. I know I have 3 Toad hats, 7 Toad t-shirts, 4 Toad boxer shorts, 2 pairs of Toad socks.......

Check out the event and registration details here.

Disclaimer: this has nothing to do with AUSOUG. I just thought some local readers would like to know.