Interesting that this came up. We are having what looks like a similar
problem (Oracle 12.1.0.2 64bit on Linux) with what we think is a memory
issue.
Here's my case:
CREATE OR REPLACE TYPE NCLOB_TAB_TYPE AS TABLE OF NCLOB;
CREATE OR REPLACE FUNCTION get_nclob_concat (
in_nclob_tab IN nclob_tab_type,
in_delimiter IN VARCHAR2 DEFAULT ',')
RETURN NCLOB
IS
l_nclob NCLOB;
l_nvarchar2 NVARCHAR2 (16000);
l_need_nclob BOOLEAN := FALSE;
l_delimiter NVARCHAR2 (1) := in_delimiter;
BEGIN
FOR i IN in_nclob_tab.FIRST .. in_nclob_tab.LAST
LOOP
IF l_need_nclob
THEN
l_nclob := l_nclob || l_delimiter || in_nclob_tab (i);
ELSE
BEGIN
l_nvarchar2 := l_nvarchar2 || l_delimiter || in_nclob_tab (i);
EXCEPTION
WHEN VALUE_ERROR -- ORA 6502
THEN
l_need_nclob := TRUE;
l_nclob := l_nvarchar2 || l_delimiter || in_nclob_tab (i);
END;
END IF;
END LOOP;
This function is then called millions of times from SQL statement:
insert /*+ APPEND */ into rg2_wrk_part_item_rd_l_li
SELECT x.item_id, -- a NUMBER(10)
get_nclob_concat (
CAST (MULTISET ( SELECT y.text
FROM isz_ww_WRK_PART_ITEM_RD_L y
WHERE y.item_id = x.item_id
order by y.lang_id) AS nclob_tab_type)
,chr(10)) AS text
FROM (
select distinct item_id
from isz_ww_WRK_PART_ITEM_RD_L) x
What we see while monitoring this is that the behavior, sometimes goes
very, very bad (which is expressed as very long execute times 40 hrs or
more) while at other times it seems to run okay (~20 minutes). We have
even simultaneously started two of these statements and see one that runs
in 20 minutes and the other goes for 40+ hours. We tried writing this a
number of different ways in an attempt to avoid the underlying issue. At
first we were passing in an nclobs and just concatenating them. Then we
thought since almost all of our data is NVARCHAR2 length, why not write it
the way I've shown it here concatenating NVARCHAR2s instead of NCLOBs.
That helped but did not solve the problem. Next attempt we changed the
input type from an NCLOB to a NVARCHAR2 array, that also helped but did not
solve the problem. We are stumped. By the way, we have had a ticket open
with Oracle support on this since mid November 2015. So far no help. It's
been hard to convince Oracle that there is a problem because eventually the
statement finishes successfully.
