Apex tree region warning: Tree root ID "0" not found

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

I was testing a recently created tree region in an Oracle Application Express application when upon displaying the page, instead of nice tree diagram, i got the following message:

Warning: Tree root ID "0" not found.

The first thing that came to my mind was to check if the underlying hierarchical query was returning any results, may be a typed something wrong, but i executed the query in SQL developer without a hitch.
Then i started wandering through the tree components in search of light.
I must say that i'm not using trees every day, so it was a while since the last time i built one. Struggling to understand what's wrong with my tree, fortunately the bulb lighted on suddenly: let's switch to page debug mode and see if i can get any clues from the output. Bingo.
Here is what i found:

Warning: Tree root ID "0" not found.
ORA-06550: line 3, column 100: PL/SQL: ORA-00904: "A2": invalid identifier
ORA-06550: line 2, column 22: PL/SQL: SQL Statement ignored
ORA-06550: line 22, column 64: PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 22, column 15: PL/SQL: Statement ignored

This stack of errors brought my attention to the fact that in my tree source query i didn't include neither column A1 nor A2. Apex documentation is a bit vague about these two additional columns, the Application Express User's Guide through version 3.2 mentions only ID, PID, NAME and LINK columns and the helper text at the bottom of the query source field contains the following information (see picture):

According to my tests the only correct and working syntax is that shown in the sample SELECT statement of the picture, if you do not include columns A1 and A2 you'll get the warning message instead of the tree (please correct me if I'm wrong).
As shown in the picture, if you do not use columns A1 and A2 for some reason inside the tree template, then set them to null.

Addendum
This message can appear in translated forms as well:

Avvertenza: ID radice struttura "0" non trovato
Advertencia: No se ha encontrado el identificador de raíz del árbol "0".
Warnung: Baum-Root-ID "0" nicht gefunden.
Avertissement : ID de racine d'arborescence "0" introuvable
Advertência: ID da raiz da árvore "0" não encontrado.
編集 警告: ツリー・ルートID「0」が見つかりません。
경고: 트리 루트 ID "0"을(를) 찾을 수 없습니다.
警告: 找不到树根节点 ID "0"。


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

ORA-31181: PL/SQL DOM handle accesses node that is no longer available

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

This morning I'm going for an easy one.

...
 l_doc      := DBMS_XMLDOM.newDomDocument(cl => l_clob);
 l_rootElem := DBMS_XMLDOM.getDocumentElement(doc => l_doc);
 DBMS_XMLDOM.freeDocument(l_doc);
 l_rootNode := DBMS_XMLDOM.makeNode(elem => l_rootElem);  -- this is line 436


 DBMS_XMLDOM.freeNode(l_rootNode);
...
end;
/
ORA-31181: PL/SQL DOM handle accesses node that is no longer available
ORA-06512: at "XDB.DBMS_XMLDOM", line 2554
ORA-06512: at "XDB.DBMS_XMLDOM", line 2572
ORA-06512: at "TAMEME.TAMEME", line 436
ORA-06512: at line 6

This error is easily explained: you cannot free the object containing the DOM document until you have finished processing all the objects derived from it.
In the code snippet above, procedure DBMS_XMLDOM.freeDocument must be moved after DBMS_XMLDOM.freeNode.
In other words, the call to DBMS_XMLDOM.makeNode is out of scope.

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

Heap size nnnnnK exceeds notification threshold (2048K)

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

Lately I'm working a lot with XDB.
At the same time, having some spare time to investigate a few outstanding "issues", i decided to search the web for information about alert log messages like the following:

Memory Notification: Library Cache Object loaded into SGA
Heap size 2208K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==

I wasn't particularly worried as this message is marked as a notification, so i was pretty sure that it was just informational stuff, however i wanted to learn more about and see what others say about it.
I quickly found a useful article on Frits's blog.

The suggested workaround, even if this isn't a serious issue as per Oracle Support note, consists in increasing the threshold level by executing the following statement:

alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile;

followed by a db restart.

The value of 8Mb has no special meaning, i guess we can increase or decrease it as necessary, because the concept of threshold depends on how much memory is allocated for the SGA: the bigger the SGA, the higher the threshold, but i don't recommend making any arbitrary assumptions here, it's probably better to adjust the value one step at a time, just to reduce the frequency of the notification.

If we work a lot with large objects in memory and the alert log is cluttered by many of these notifications, but the performances of the database are still ok, we might want to increase this value to avoid logging useless entries. For example, if most of the notification messages refer to objects in the range between 2 and 4Mb, then it makes sense to set the threshold around 4Mb, so that we can still "watch" any objects above that level.

We are interested in peaks, not in the average size objects, but what average size means depends on our applications.

ORA-27040: file create error, unable to create file

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

It's not the first time i write about the stack of errors returned by data pump operations and ORA-31641 in particular, however the error messages may vary slightly with the operating system. For instance the following error stack is returned on XE running on top of Ubuntu, whereas the previously linked page refers to an error condition occurring on XE for Windows.

expdp user/pwd directory=EXPDIR dumpfile=dump.dp tables=emp,dept nologfile=Y

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/flavio/documents/dump.dp"
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

The problem is caused by the missing privileges of user oracle on the directory pointed to by EXPDIR.
EXPDIR is pointing to /home/flavio/documents.

ls -ld /home/flavio/documents

drwxr-xr-x 4 flavio flavio 4096 2009-04-10 19:00 /home/flavio/documents/

As you see the directory belongs to user flavio, group flavio, moreover no write privileges are granted other than to the directory owner. Now, as Oracle processes are running under the user oracle, in order to be able to dump a file in this directory we have two possibilities:

1. to change the other group permissions on the directory i.e. by executing chmod o+w documents.
   
2. to add user oracle to group flavio and grant write permissions on documents to group flavio by executing sudo usermod -a -G flavio oracle; chmod g+w documents.

Note that option #1 will work instantly while option #2 requires a db restart. If you do not restart the db, you will still get the permission denied error. In either case, the db user must have READ and WRITE permissions on EXPDIR or hold CREATE ANY DIRECTORY privilege.

Whether the optimal solution is either of the two options above it really depends on the application security requirements.
In the end may be it's better to create a public directory where everybody can dump files freely or create a shared directory EXPDIR where only oracle can create files and users belonging to a datapump group can read them. This approach avoids tampering with the existing permissions on user's private folders and it doesn't require a db restart.

Final notes: while looking for the Optimal Flexible Architecture document, i realized that dpdump is nowhere to be found in the installation documents through version 11gR1. This directory is created at time of installation and it is an OFA directory, no matter what the books say or don't. Directory object DATA_PUMP_DIR, which is the default directory for data pump operations, indeed maps to dpdump.
If i remember correctly in 10gR2 the directory object is automatically created at installation time whilst in 10gR1 you need to create it manually.

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

Will ever Apex run on MySQL?

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

It looks like Larry Ellison had some leftover in his pocket this morning...

ORA-01791: not a SELECTed expression

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

You may get the following error when you combine DISTINCT and ORDER BY clauses in a query as follows:

select distinct
agent,
application_id
from apex_workspace_activity_log
order by view_date;

ORA-01791: not a SELECTed expression

The problem is caused by the presence of column view_date in the ORDER BY clause that is the expression mentioned in the error message. The term expression is probably used because one can specify expressions in the ORDER BY, not just simple columns.

The query indeed will run without problems if the ORDER BY clause contains SELECTed columns or expressions:

select distinct
agent,
application_id
from apex_workspace_activity_log
order by application_id;

or

select distinct
agent,
application_id,
trunc(view_date)
from apex_workspace_activity_log
order by application_id, trunc(view_date);

Note that it's also possible to specify expressions based on the SELECTed columns that are not explicitly calculated in the SELECT list:

select distinct
agent,
application_id,
application_name,
trunc(view_date)
from apex_workspace_activity_log
order by upper(application_name), application_id, trunc(view_date);

But in the end, is either of these alternate queries returning an acceptable result as far as your application is concerned?
How can i turn the original (illegal) query into an acceptable form?

Over the years I've seen people using the DISTINCT clause quite arbitrarily and most of them had an MS-Access background...
Don't ask me if the two things were correlated ;-)
It looks like they just want to be sure that there are no duplicates in the resulting recordset, so they add DISTINCT even if they don't really need to do that. If that is the case, then the solution is easy, just get rid of this keyword.
However the query above seems to be an illegal representation of a legitimate (albeit strange) requirement:
i'd like to retrieve unique pairs of application IDs and user agents in order of appearance in the log.
If this is the correct interpretation, then DISTINCT is useless and GROUP BY is the answer.

select agent, application_id
from (select
       agent,
       application_id,
       min(view_date) d
       from apex_workspace_activity_log
       group by agent, application_id)
order by d;

In conclusion I'd say that ORA-01791 is either the symptom of a trivial error or the ringing bell of an ill-designed query.

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

Oracle 10gR2 is finally available for Mac OS X on Intel

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

If you didn't hear the latest news, Oracle 10gR2 for Mac OS X is officially available for download.
Thanks to Scott Spendolini for reporting this exciting news.

Awaiting to finish the download and to begin the installation on Mac OS X 10.5.6.

Updated April 14. If you are primarily interested in getting Oracle Application Express to work on Mac OS X natively, then you'll have to wait until Oracle releases the missing server components as Apex is not yet supported. See Scott's posting.

LD_LIBRARY_PATH is not set! in alert_XE.log

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

This morning i discovered the following messages in the alertXE.log, located in the bdump folder of a xubuntu virtual machine:

Fri Apr 10 15:01:15 2009
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
...

After a quick search on the web, i read this posting from Mohammad, so i concluded i was not the first one to see this error...
He suggests to set the variable LD_LIBRARY_PATH in the .bash_profile or .profile of the oracle user, however i preferred to change the main startup script located in /etc/init.d containing the settings for most of the Oracle variables, as shown in the image:



After fixing oracle-xe, the alert_XE.log file doesn't show anymore the warning messages:

Fri Apr 10 15:06:43 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
...

Someone may argue that changing the script does not guarantee against future installations (or re-installations), however hopefully Oracle will fix the bug in the meantime, so Oracle 11XE will not be affected by this flaw.

In the end i'd be curious to know which functionality was affected by the bug because this Oracle instance has been running for quite some time without showing any particular problems.