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:
(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.
However the owa_util.redirect_url code we migrated from Portal 9.0.4 to 10.1.4 will generate the following URL:
(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 := '&';
v_param_deliminator := '?';
owa_util.redirect_url(curl=> p_back_url || v_param_deliminator || p_reference_path || '.p_my_name_is=' || p_my_name_is);