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

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

Comments are closed.