Weblog for the AMIS Technology corner
About the author : Lucas Jellema
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, ADF, Java in various shapes and forms and many other things. Author of ...
More by Lucas Jellema
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( 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, cttIdfrom 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.
