Why did my stored procedure stop working after upgrading Apex?

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

While most Apex applications will continue to work normally after upgrading Oracle Application Express to the latest version, there can be some applications that do not work as expected under all circumstances.
How do we know which applications are the most likely to fail?
This question can be easily answered: any apex installation based on EPG (the embedded PL/SQL gateway) is a very good candidate for troubles if you omit to migrate the contents of function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL, located in the relevant apex schema of the version being upgraded.
Given the fact that Oracle 11G and Oracle XE are shipping with Apex and EPG, there should be quite an audience facing this problem sooner or later.

For instance, say you are upgrading from version 3.1.2 to 3.2.1: the current apex schema is FLOWS_030100, while the new schema is going to be APEX_030200.
Now, if you customized function FLOWS_030100.WWV_FLOW_EPG_INCLUDE_MOD_LOCAL in order to allow for certain stored procedures to be invoked from an URL, you must be aware that after upgrading Apex, the PL/SQL body of the function will be reset to the "factory" state, because Apex doesn't "merge" it with the modified version in the current apex schema.

Unfortunately Oracle Application Express Installation Guide doesn't mention or, better said, doesn't remind us to migrate this stuff manually from the previous version in case of upgrade, which can be done quite trivially by saving the function as a text file.
I think that it would be wise to include some reminder about this in the post-installation requirements, you know, sometimes we tend to forget something AS I JUST DID.
Probably it would be possible to issue some warning at the end of the upgrade after checking if the function had been modified by the user, a task for the Oracle Apex team :-)

Funnily enough the documentation encourages the administrator to drop the old apex schema once the upgrade has been successfully tested, in order to reclaim unused space, with the result of wiping out the customizations we did to WWV_FLOW_EPG_INCLUDE_MOD_LOCAL.

A typical symptom that there is something wrong with stored procedures invoked from URLs is when you get HTTP 403, as i described time ago, but in the case of procedures downloading images you won't probably see any explicit error, for instance Firefox was simply returning the ALTernate text for missing pictures (which was the ultimate reason as to why i began searching for this problem).

Not all was lost because i had backups and so on, but it's always a waste of time to restore a db just to find out which functions were previously authorized, assuming that you are not keeping a written record of these procedures elsewhere.

So here comes the fatal question: when do we decide if the apex upgrade was carried out successfully? Are we sure that we tested each and every nuance of our application(s), including those features that are probably not simply exercised by navigating through the pages?
A very typical usage for custom stored procedures, before the introduction of declarative blob support was indeed to provide download links for documents and images held in tables, which could mean that some old application may not work properly because we didn't take the time to rewrite those links using the new declarative approach. These cases are probably the most easy to spot because there will be something missing in the page. If custom PL/SQL procedures invoked from URLs are meant instead for submitting data, then they are more likely to be forgotten or overlooked during the testing phase because it's easy to think that they aren't touched by the apex upgrade.

So, is there any way to mitigate the problem or fix it for once?
I fear there isn't. The only sensible place for checking the problem is inside the installation/upgrade script and if it doesn't, then it is up to us to remember to do this extra step.

Hopefully the Apex team will come to the rescue in a near future.

When APEX_UTIL.STRING_TO_TABLE is not enough

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Yesterday i needed to find a way to ensure that a list values entered manually by a user were unique.
If these values came from a table, it would be fairly easy to build a query returning DISTINCT values, but in this case values are pulled in as comma separated values.

This type of problems are suitable for set operations using PL/SQL collections because we are dealing with a limited, although unspecified amount of values that are not permanently stored as individual rows in a table.
PL/SQL comes with an array of operators for manipulating collections as sets and you can find a few basic examples in PL/SQL Language Reference manual. The functional description of each set operator can be found in the SQL Reference instead. The links refer to Oracle version 11gR2 but these functions are available at least since version 10gR1. There is also a nice tutorial by Steven Feuerstein discussing these functions in an old Oracle Magazine issue, so you may want to check it out.

It is worth noting that such set operators work only on PL/SQL nested tables, but not on associative arrays or varying arrays and this makes a difference as we shall see.

One of the most common operations involving collections in Oracle Application Express is to take some delimited string (by commas, semicolons, colons or spaces typically) and convert into a collection or viceversa using function APEX_UTIL.STRING_TO_TABLE (or its counterpart APEX_UTIL.TABLE_TO_STRING). Unfortunately the result of function STRING_TO_TABLE is APEX_APPLICATION_GLOBAL.VC_ARR2, that is an associative array, not a PL/SQL table, which means that an attempt to use the SET function on it will lead to the following run time error:

PLS-00306: wrong number or types of arguments in call to 'SET'

This error can be puzzling at first because the difference between a PL/SQL table and an associative array indexed by positive integers is very subtle and the only way to say what kind of collection type is APEX_APPLICATION_GLOBAL.VC_ARR2 is to look at its definition, which can be not so straightforward for an unprivileged user. So, once we are aware of the fact that APEX_APPLICATION_GLOBAL.VC_ARR2 is an associative array, we are basically stuck at the original problem.
How do we move forward?

The options are the following:

1. stick to the apex STRING_TO_TABLE function and copy the values one by one into a PL/SQL nested table, using a FOR...LOOP structure.
2. write you own STRING_TO_TABLE function and return a PL/SQL table instead.
3. find another way of quickly transforming the associative array into a PL/SQL table other than #1.
4. normalize the values yourself instead of using SET.
   
5. ?
   

I opted for option #2 because from a performance point of view is the most effective probably and it also gave me some additional flexibility, but let's review each point first.

Option #1 saves me from rewriting a function but it forces me to perform a not so efficient extra loop that it's also requiring extra memory allocation.
Option #2 is probably comparable in terms of memory and speed although i hate to "reinvent the wheel", unless this is absolutely necessary. It gives me also the advantage of further customization, like space trimming and case insensitive or sensitive comparison.
Option #3 cannot be performed with CAST, so i guess there is no easy way of doing it unless we fall back to option #1, but i do not exclude it could be done in another fashion.
Option #4 is probably the worst choice because i need to replace an efficient piece of C code with my own PL/SQL logic based on a search loop.
Option #5 is an open question, if you know a better way of doing this, please let me know.

So, after rewriting my custom STR2TAB function, i could finally leverage the power of SET function and simply return the result in the following manner:

DECLARE
str  varchar2(1000) := 'A,B,C,A,D';
TYPE nested_typ IS TABLE OF VARCHAR2(255);
nt1   nested_typ;
nt2   nested_typ;
PROCEDURE print_nested_table(p_nt nested_typ) IS
  output VARCHAR2(128);
BEGIN
  IF p_nt IS NULL THEN
     DBMS_OUTPUT.PUT_LINE('Results: ');
     RETURN;
  END IF;
  IF p_nt.COUNT = 0 THEN
     DBMS_OUTPUT.PUT_LINE('Results: empty set');
     RETURN;
  END IF;
  FOR i IN p_nt.FIRST .. p_nt.LAST
  LOOP
     output := output || p_nt(i) || ' ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Results: ' || output);
END;

FUNCTION str2tab(
 p_text      in varchar2,
 p_separator in varchar2 default ',')
return nested_typ
as
 l_string        varchar2(32767) := p_text || p_separator;
 l_data          nested_typ := nested_typ();
 n               binary_integer;
begin
 loop
 exit when l_string is null;
    n := instr( l_string, p_separator );
    l_data.extend;
    l_data(l_data.count) := substr(l_string, 1, n-1);
    l_string := substr(l_string, n + length(p_separator));
 end loop;

 return l_data;
END;
BEGIN
nt1 := str2tab(str);
nt2 := set(nt1);
print_nested_table(nt2);
END;
/

Results: A B C D

In conclusion, apex function APEX_UTIL.STRING_TO_TABLE does an excellent job when you are taking the values as they have been typed in, but if you need to work on the values as a set, then you'll need to come up with your own custom function.

See message translations for PLS-00306 and search additional resources.