0 
0 I have a query: an in line view, built from two simple queries unioned together, that forms the starting point for a hierarchical (connect by) query. Nothing up my sleeves:
with nodes as
( select bgg.id id
, bgg.naam node_label
, -1 parent_id
, 0 ctt_id
from pgi_v_begrotingen bgg
and bgg.id = 7837
UNION ALL
select bpt.id id
, pgi_util.TREE_CATALOGUSPOST_NUMMER_NAAM(bpt.ctt_id) node_label
, nvl(bpt.bpt_id, bpt.bgg_id) parent_id
, bpt.ctt_id
from pgi_v_begrotingsposten bpt
where bgg_id = 7837
)
select node_label NodeLabel
, level
, id Id
, parent_id ParentId
, ctt_id CttId
from nodes
connect
by prior id = parent_id
start with parent_id =-1
order
siblings by pgi_util.TREE_CATALOGUSPOST_sort(ctt_id)
yet it fails, depending on the environment, either with an ORA-03113: end-of-file on communication channel (SQL*Plus) or java.sql.SQLException) OALL8 is in an inconsistent state. (from ADF BC).
It turns out that
the call to the PL/SQL function pgi_util.TREE_CATALOGUSPOST_NUMMER_NAAM(bpt.ctt_id) at that point in the query, causes the problems. When I rewrite the query – with help from Anton – to have the PL/SQL call made outside the hierarchical query:
with nodes as
( select bgg.id id
, bgg.naam node_label
, -1 parent_id
, 0 ctt_id
from pgi_v_begrotingen bgg
and bgg.id = 7837
UNION ALL
select bpt.id id
, pgi_util.TREE_CATALOGUSPOST_NUMMER_NAAM(bpt.ctt_id) node_label
, nvl(bpt.bpt_id, bpt.bgg_id) parent_id
, bpt.ctt_id
from pgi_v_begrotingsposten bpt
where bgg_id = 7837
)
, tree as
( select node_label NodeLabel
, level lev
, id Id
, parent_id ParentId
, ctt_id CttId
from nodes
connect
by prior id = parent_id
start with parent_id =-1
order
siblings by pgi_util.TREE_CATALOGUSPOST_sort(ctt_id)
)
select case cttid
when 0
then nodelabel
else pgi_util.TREE_CATALOGUSPOST_NUMMER_NAAM(cttid)
end nodeLabel
, lev
, id
, parentId
, cttId
from tree
all of a sudden it does complete, as it should. Apparently, whatever is going on inside that function conflicts the query plan and execution picked by the CBO in the first attempt.
