Monday, October 20, 2014

ORA-20104: create_collection_from_query ParseErr:ORA-00918: column ambiguously defined

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
If you are wondering why you are getting this error message after following the example in the documentation describing procedure CREATE_COLLECTION_FROM_QUERYB2 in the APEX_COLLECTION API, the quick answer is that the sample code is flawed.

ORA-20104: create_collection_from_queryb2 Error:ORA-20104: create_collection_from_query 
ParseErr:ORA-00918: column ambiguously defined

The problem is in the missing aliases for the null columns:

    l_query := 'select empno, sal, comm, deptno, null, hiredate
              , null, null, null, null, ename, job, mgr from emp';
        p_collection_name => 'EMPLOYEES', 
        p_query => l_query,
        p_generate_md5 => 'NO');

After adding the aliases, the API call works without a hitch.
    l_query := 'select empno, sal, comm, deptno, null as n5, hiredate
              , null as d2, null as d3, null as d4, null as d5
              , ename, job, mgr from emp';
        p_collection_name => 'EMPLOYEES', 
        p_query => l_query,
        p_generate_md5 => 'NO');

 See message translations for ORA-00918 and search additional resources.

Tuesday, October 14, 2014

Apex substitution strings in reports, not always replaced with their values, guess why?

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

It's amazing to you find out how easy is to take for granted certain features of Apex for the simple fact that you have been using them for a long time without paying too much attention to certain specific details.

This morning I ran a report that I rarely use in an application and to my surprise there were some missing icons. How can it be, did I forgot to copy over the images during the last upgrade?
Did I miss some flag or inadvertently change some report attribute?
Did something change in Apex 4.2 that I was not aware of?

I quickly assessed not only that the missing icons were not missing at all, but for some reason the #IMAGE_PREFIX# substitution string was not replaced by its run-time value only in this specific report.
But why if the report has been cloned from a perfectly working one with only minor changes in the column values?

As it always happens in these cases, the first thing that comes off the top of your mind is to blame the software, no matter how improbable that could be, so I started comparing the two reports with the help of the APEX dictionary views trying to understand why one was working perfectly and the other one was failing miserably, may be some little flag makes the difference, but it turned out very quickly that Apex 4.2 wasn't the culprit here :-)

Before revealing the name of the killer, let's see how Apex deals with the substitution strings.

First of all you could define a simple classic report as follows, changing the IMG column definition from "Display text (does not save state)" to "Standard Report Column" and setting the report attribute "Strip HTML" to "No".

select htf.img('#'||'IMAGE_PREFIX#/copy.gif') img from dual
union all 
select htf.img('#IMAGE_PREFIX#/copy.gif') img from dual

The report will then display like this (I am using Firefox, on different browser it could be seen differently):

So, why does the first icon appear to be broken, while the second one displays just fine?
The reason is in that Apex report engine as part of its job is replacing #IMAGE_PREFIX# in the source SQL at some stage before executing the query. This explains why the first row is broken, it's because the string #IMAGE_PREFIX# results from the concatenation of the strings that occurs only at run time, while the second row gets its value before run-time.
Of course the same applies to queries containing functions that return values containing substitution strings at run-time or even functions returning a SQL query containing functions that return values containing substitution strings at run-time (sorry for the recursive definition, but Apex gives you also this powerful possibility).

So, what to do in these cases where the #IMAGE_PREFIX# or other substitution values are generated only at the last second?
The solution is very simple and is what I initially overlooked, but it emerged very clearly when I ran the comparison query between the attributes of the two reports, because the bad one was missing one important attribute.

By specifying #IMG# in the "HTML expression" attribute, we can indeed force Apex to perform the substitution also at run-time but be sure to have set "Strip HTML" to "No" in the report definition otherwise you won't see anything.
This powerful attribute allows for the customization of the HTML code of each individual report column and gives also the possibility of referring to the current values of other columns which can be combined with HTML tags for building snippets of HTML of arbitrary complexity.

Interestingly enough the on-line help for "HTML Expression" doesn't mention the fact that Apex will perform the evaluation of built-in substitution strings as part of the processing of this piece of data, which is a life saver if you are generating HTML on-the-fly.

Another example of how useful Apex dictionary views can be when you need to find out what's wrong between two apparently identical report definitions...

Thursday, October 09, 2014

How many page views can Apex sustain when running on Oracle XE?

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

While some people think that hosting a web site on top of Oracle XE can be hardly considered much more than a joke, I can claim instead, basing on my personal experience of the last 5 years, that for small sites it can be a perfectly reasonable choice, provided you have the technical skills required to maintain it.

Here are some figures collected last night when a new type of bot hit one of my websites with a somewhat intense flow of requests compared to the average.

Status log starting from 2014/10/08 20:00:00 to 2014/10/09 04:00:00 +00:00
TimeframeApp IDUser AgentHit count
10/08 20:00XXXXXMozilla/4.0 (compatible; ICS)3902
10/08 20:00XXXXXother agents with hits < 10046
10/08 20:00XXXXX*** Partial Hits by App ***3948
10/08 20:00
*** Partial Hits ALL Apps *** 3948
10/08 21:00XXXXXMozilla/4.0 (compatible; ICS)6381
10/08 21:00XXXXXMozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)103
10/08 21:00XXXXXother agents with hits < 10017
10/08 21:00XXXXX*** Partial Hits by App ***6501
10/08 21:00
*** Partial Hits ALL Apps *** 6501
10/08 22:00XXXXXMozilla/4.0 (compatible; ICS)8149
10/08 22:00XXXXXother agents with hits < 10039
10/08 22:00XXXXX*** Partial Hits by App ***8188
10/08 22:00
*** Partial Hits ALL Apps *** 8188
10/08 23:00XXXXXMozilla/4.0 (compatible; ICS)8360
10/08 23:00XXXXXother agents with hits < 10052
10/08 23:00XXXXX*** Partial Hits by App ***8412
10/08 23:00
*** Partial Hits ALL Apps *** 8412
10/09 00:00XXXXXMozilla/4.0 (compatible; ICS)8420
10/09 00:00XXXXXother agents with hits < 10030
10/09 00:00XXXXX*** Partial Hits by App ***8450
10/09 00:00
*** Partial Hits ALL Apps *** 8450
10/09 01:00XXXXXMozilla/4.0 (compatible; ICS)8424
10/09 01:00XXXXXother agents with hits < 10073
10/09 01:00XXXXX*** Partial Hits by App ***8497
10/09 01:00
*** Partial Hits ALL Apps *** 8497
10/09 02:00XXXXXMozilla/4.0 (compatible; ICS)8413
10/09 02:00XXXXXother agents with hits < 10084
10/09 02:00XXXXX*** Partial Hits by App ***8497
10/09 02:00
*** Partial Hits ALL Apps *** 8497
10/09 03:00XXXXXMozilla/4.0 (compatible; ICS)1071
10/09 03:00XXXXXother agents with hits < 10029
10/09 03:00XXXXX*** Partial Hits by App ***1100
10/09 03:00
*** Partial Hits ALL Apps *** 1100

*** Total Hits ***53593
App IDApex UserPage IDHit count
XXXXX*** Total Hits for user ANONYMOUS ***
XXXXX*** Total Hits for app XXXXX ***
No errors found in 53593 log entries 

The blu dots belong to the Apache web server that acts as a proxy, the orange dots are the XE11g database server with Apex 4.2.

The Apache web server runs on a T2 micro instance on Amazon EC2, while the database server is a T2 small instance.

As you see the XE machine is handling pretty well the load which amounts more or less to an average of 2.36 requests per second in the busiest period.

The CPU load of course depends on the type of page visited, in this case most of the pages were not running expensive queries.

The spike at 22:00 hours UTC recorded a daily batch job activity that starts at midnight CET.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles