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.