About displaying images using APEX_UTIL.GET_BLOB_FILE_SRC in non trivial situations

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

In a perfect situation, when we need to display an inline image inside an Apex report, we might simply pick the BLOB column and apply the special formatting required in these cases, I mean that weird format mask containing a list of column attributes separated by colons where each member represents a column name in the table being queried:

SELECT ...
       dbms_lob.getlength(thumbnail) as image,

       ...
  FROM image_table...

 
The format mask specified in the column attributes of the report is something like:

Note also the call to dbms_lob.getlength that for some reason is still NOT properly documented and it is absolutely necessary to make work this type of reports otherwise you will incur into the rather obscure error message when you try to run the report:

report error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error 

But as I said, sometimes we are not in a perfect situation.
We might have some rows with an image and some without images.
How do we display an alternate image in case the BLOB value is null?

SELECT ...
       nvl2(thumbnail,
          '<img src="'||APEX_UTIL.GET_BLOB_FILE_SRC ('P4_X',id)||'" />',
          '<img height="72" src="#IMAGE_PREFIX#1px_trans.gif" width="72" />'
       ) as image,

       ... 

 FROM image_table...

the SQL above displays the BLOB image stored in image_table only if the value is not null (it might still be an empty BLOB, but that's another story and you'd better to avoid this further annoyance), otherwise it displays a transparent image that you could replace with anything that suits better your needs, say an icon with a question mark or whatever.

Now, the problem with a report column defined in a SQL statement like this is that you can no longer use Apex's built-in report image formatting, but you need to add some additional pieces here and there.

The first requirement comes from APEX_UTIL.GET_BLOB_FILE_SRC itself: the first parameter must be the name of a page item (P4_X in the code above), type FILE BROWSE, containing the format mask specifying a list of columns, similar to the picture above, but without the Blob table.

Now, where does Apex take the name of the table if it doesn't ask me to specify one here?
It will be soon clear as you try to run the page, because it will throw the following run-time error.

Error: No corresponding DML process found for page 4

Contact your application administrator
Technical Info (only visible for developers)

    is_internal_error: true
    apex_error_code: WWV_FLOW_NATIVE_ITEM.NO_DML_PROCESS_FOUND
    component.type: APEX_APPLICATION_PAGE_ITEMS
    component.id: 2568302329371214
    component.name: P4_X

This means that Apex is expecting to retrieve the name of the table from a built-in Row Fetch process where you specify the table name and it's primary key(s).

But wait a minute, I am not using a built-in Row Fetch in this page because I am running a report.
Never mind, you can create a *fake* Row Fetch process by specifying never as a condition in the process attributes.

After adding the fake Row Fetch process, the report will magically start working.

However there is one last refinement to be done:
the FILE BROWSE page item we added is probably undesired, visually speaking.
If that is the case, you need to add something like style="display:none;" in the HTML Form Element Attributes to hide it from the user.

I wish we could have a more streamlined version of APEX_UTIL.GET_BLOB_FILE_SRC in a future release of Apex such that we could supply all the necessary parameters without having to resort to this kind of tricks.

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.

ORA-02291 on FLOWS_030100.WWV_FLOW_FND_GU_INT_U_FK

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

You may get the following bizarre error message when invoking the Oracle Application Express API procedure: APEX_UTIL.CREATE_USER

ORA-02291: integrity constraint (FLOWS_030100.WWV_FLOW_FND_GU_INT_U_FK) violated
- parent key not found

For instance, suppose you have a registration page where the user can enter his/her data and the apex user is created with an after submit process as follows:

begin
apex_util.create_user(
p_user_name     => :P12_USER_NAME
,p_first_name    => :P12_NAME
,p_last_name     => :P12_SURNAME
,p_email_address => :P12_EMAIL
,p_web_password  => :P12_PASSWORD
,p_group_ids     => apex_util.get_group_id('web_users')
);
end;

Error ORA-02291 that we've got is caused by a combination of factors:

• the value returned by the expression apex_util.get_group_id('web_users') must be not null
• the user name must be already existing

If either condition above is false, APEX_UTIL.CREATE_USER won't return any errors, even if the user name is already existing. If the user is already existing, it seems that the current user attributes are preserved (that is they are not updated with the values supplied in the call, fortunately), but no error is raised whatsoever, which seems to me rather odd, in the end I'd like to know if a certain user name is already present in the same workspace, but it looks like you have to manually check this condition beforehand, by means of a NOT EXISTS validation or a query on the apex dictionary view APEX_WORKSPACE_APEX_USERS.

This behavior has been seen on Apex 3.1.2.

See message translations for ORA-02291 and search additional resources or read more Oracle Apex related articles.

Extending supported languages for "since" date formats in Apex

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

Although Apex (3.0.1) comes with 9 optional languages (German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese) that you can install to localize Apex on-line help plus some locale information like the "SINCE" date format mask, the developer can freely create applications for languages not comprised in this set (see the list of all supported language codes).

The package APEX_UTIL contains several useful procedures and functions and one of these packaged functions is:

APEX_UTIL.GET_SINCE(date);

This function returns a string representing in words the elapsed time since a certain date (<= SYSDATE) specified as a parameter. A typical usage is for indicating how old a certain entry is, for instance news, blog postings, forum messages, emails and so on. Note that Apex's SINCE date format mask embeds the function at the report attributes level, so you don't need to explicitly include it in the query source normally. Although APEX_UTIL.GET_SINCE is a globalized function, translated versions of the text require to install apex in target language(s), so it might come in handy the following apocryphal version that makes use of Apex's globalization techniques, that is by means of translatable text messages. This can be especially useful if you need to translate the strings in one of the languages that are not directly supported by Apex.
Bear in mind however that a separate copy of each translated message will be required for each installed application.

Download get_since.sql source file.

Another difference is in that i adopted a somewhat relaxed concept of time to describe how old is a certain date, but you can change the function to suit your needs by increasing, reducing or modifying the time ranges.

See an example of Apex's standard function compared with the custom version.

Besides English, I've loaded Spanish and Italian translations in the sample application, but you must have the browser language set accordingly in order to see them.
All other languages will display messages in English.

You can use the linked script to initialize the English text messages for a certain application or modify it for entering additional translations, just change the language code and the text.

These messages can be edited individually from the Text Messages page in the globalization section of the shared components within Apex.
Note also that you don't need to create a translated version of the application for displaying the text in the desired language.

Finally, a quick tip for dealing with a report query that includes this custom function, as the most typical situation is a news report sorted by descending publication date (see the picture below):

1. the query must contain both the column containing the date value as well as the result of get_since (in the picture columns PUBDATE and AGE respectively).
   
2. Check the sort option for the date column and leave unchecked the sort option for get_since result (as pictured above). This setting is necessary to sort items properly because get_since returns a string that would result in a meaningless alphabetical sort.
   
3. You can sort the report basing on the real date column without displaying it in the report. In this fashion you'll get a report displaying just how old are the entries, starting from the most recent to the oldest.
4. Remember that you can combine column values as you prefer, you can even group different columns inside the HTML Expression property of the report column attribute page (see picture below), the current column value can be manipulated using the #COLUMN_NAME# syntax (see #AGE# in the example below), in this way you can create reports formatted exactly as you want.
   

Different ways of counting clicks in Apex 3.0

As of Apex version 3.0 there is the possibility of enabling the click count declaratively for List entries, a component whose typical purpose is to display a list of external URLs or application page links.

This new declarative approach requires the setting of a couple of properties in the Application Builder page where you edit a single List entry (see the picture below).
The path to the page is Application Builder / Shared Components / Navigation / Lists / List Entries / Create Edit List Entry

The advantage of this new feature is in that you don't need to build the URL on your own and a couple of required parameters are automatically taken care of by Apex (the user and the workspace id).

In Apex 3.0 there is also the possibility of querying a view called APEX_WORKSPACE_CLICKS, for a full fledged report containing all the available information regarding these events.
Indeed the columns provided are more than those displayed in the built-in report accessible from the Monitor Usage Page of Oracle Application Express.

One of the key columns of this view is CLICK_ID, but as you can easily see yourself, if you enable the "automatic" click counting of Apex 3.0, that column will always be empty.

Now, a typical requirement is to reconcile the click with some session information recorded in the access log and i find that column CLICK_ID is a good candidate for tracking clicks, because, typically, the category should only contain a more generic string.

Oddly enough, the Apex 3.0 click count feature doesn't provide a way of setting the click id property available in the Z function (parameter p_id), which the closest candidate for storing a unique click identifier in my humble opinion.

You can see in the picture above that the first property enables the counting and the second one allows you to set the count category, but there is no entry point for the click id.

I have a fancy theory about the lack of this parameter that i'll explain later on.

So, if you don't mind knowing more about who clicked on a certain link, then you can take advantage of this new simplified feature, but if you want more information, then you must stick to the traditional Z function (APEX_UTIL.COUNT_CLICK) or use some dirty trick like storing all the information you need in the click category, a practice that i would just recommend as a last resort.

In order to use Z, the new attributes must be left blank and the URL must built as shown in the picture below.

You can see also that i store the session ID in the p_id parameter of Z which is enough for my user tracking purposes.


I omitted parameter p_user because this is a public page and that information is practically useless in this case.

Having logged the session id in the CLICK_ID column allows me to identify very precisely in APEX_WORKSPACE_ACTIVITY_LOG the internet user who clicked on the link and do my own statistics on who-did-what.

And now for the fancy theory.

If you didn't noticed it before, there is a strange discrepancy between the type of p_id in function Z (a VARCHAR2 parameter) and the numeric type of CLICK_ID in the view APEX_WORKSPACE_CLICK.

What happens if one passes an alphabetic string like ABCD as p_id to Z?
I did some tests and i was ready to get an error message like ORA-01722 while querying the view but nothing of the kind happened, the click was not logged altogether.

Clearly this fact could be exploited by someone to hide the click.
In order to avoid such vulnerability one might want to wrap function Z with a custom procedure where a a checksum or a verification code are automatically created by the system and an attempt to forge a different URL can be detected immediately. Another useful feature that such wrapper function could provide is the obfuscation of the underlying URL.

In conclusion i don't know if parameter p_id was not mapped to any page property in the Edit List Entry page and column CLICK_ID was not included in the standard Apex report on purpose, but as long as you pass numeric values to p_id using function Z, the report will correctly show them in the CLICK_ID column.

Edited September 5,2007:
Check out also this newer blog posting!

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

The requested URL was not found on this server

I was trying out APEX function "Z", a so-called shorthand version of function HTMLDB_UTIL.COUNT_CLICK, an API tool that enables an APEX developer to easily count clicks that users make to external sites (links that lead the user to different web sites), when i got the following message from the server:

the requested URL /pls/htmldb/.z was not found on this server

At first i was puzzled because i remember that i already got this function running previously, so how could it be that "Z" was no longer there?

After checking all possible views, dropping and re-creating synonyms in all possible manners, invoking it with or without the schema prefix, function Z was still baffling me with its tedious "URL not found" message.

To make it more frustrating, i tried out its "verbose" equivalent HTMLDB_UTIL.COUNT_CLICK and it worked like a charm!

Then, suddenly and unexpectedly, there was a light at the end of the tunnel.
A thought came through my mind and it was telling me: "check out the parameters!"

Even if the very same parameters were working correctly with HTMLDB_UTIL.COUNT_CLICK, a quick review of Z's parameters showed that p_workgroup wasn't existing at all, being replaced by its "equivalent" p_company.

Needless to say, as soon as i renamed the wrong parameter, Z started working too.

Most likely the reason behind the parameter names inconsistency is what is normally called "backward compatibility problem".

There was a time when probably only function Z was existing, a sort of jurassic htmldb.
Then, in a subsequent release or era, a packaged version of the API was provided, but for some reason, the parameter was renamed (or evolved...).
Then, HTMLDB was made available to the public and it was too late to change it.
And now you know what a typical "backward compatibility quirk" looks like.

Happy counting!