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

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<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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.