Calling a PL/SQL Function at the wrong time gets me into trouble – the dreaded ORA-0113 End of Comunication Channel with a hierarchical query

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<br />( select bgg.id   id<br />  ,      bgg.naam node_label <br />  ,      -1 parent_id<br />  ,      0 ctt_id<br />  from   pgi_v_begrotingen bgg<br />  and    bgg.id = 7837<br />  UNION ALL<br />  select bpt.id id <br />  ,      pgi_util.TREE_CATALOGUSPOST_NUMMER_NAAM(bpt.ctt_id) node_label<br />  ,      nvl(bpt.bpt_id, bpt.bgg_id) parent_id   <br />  ,      bpt.ctt_id<br />  from   pgi_v_begrotingsposten bpt<br />  where  bgg_id = 7837<br />  )<br />  select node_label NodeLabel<br />  ,      level<br />  ,      id Id<br />  ,      parent_id ParentId<br />  ,      ctt_id CttId<br />  from   nodes<br />  connect<br />  by     prior id = parent_id<br />  start with parent_id =-1<br />  order<br />  siblings by   pgi_util.TREE_CATALOGUSPOST_sort(ctt_id)<br />&nbsp;

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<br />( select bgg.id   id<br />  ,      bgg.naam node_label <br />  ,      -1 parent_id<br />  ,      0 ctt_id<br />  from   pgi_v_begrotingen bgg<br />  and    bgg.id = 7837<br />  UNION ALL<br />  select bpt.id id <br />  ,      pgi_util.TREE_CATALOGUSPOST_NUMMER_NAAM(bpt.ctt_id) node_label<br />  ,      nvl(bpt.bpt_id, bpt.bgg_id) parent_id   <br />  ,      bpt.ctt_id<br />  from   pgi_v_begrotingsposten bpt<br />  where  bgg_id = 7837<br />  )<br />, tree as  <br />(  select node_label NodeLabel<br />  ,      level lev<br />  ,      id Id<br />  ,      parent_id ParentId<br />  ,      ctt_id CttId<br />  from   nodes<br />  connect<br />  by     prior id = parent_id<br />  start with parent_id =-1<br />  order<br />  siblings by   pgi_util.TREE_CATALOGUSPOST_sort(ctt_id)<br />)<br />select case cttid<br />       when 0<br />       then nodelabel<br />       else pgi_util.TREE_CATALOGUSPOST_NUMMER_NAAM(cttid)<br />       end nodeLabel<br />,      lev  <br />,      id<br />,      parentId<br />,      cttId<br />from   tree<br />&nbsp;

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.