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
Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

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.

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

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 & PL/SQL), Service Oriented Architecture, BPM, ADF, JavaScript, Java in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on conferences such as JavaOne and Oracle OpenWorld. Presenter for Oracle University Celebrity specials.

Comments are closed.