CUSOR / SYS_REFCUSOR Problems/ BUG on Oracle 19c

Question

we have several problems with Cursors and SYS_REFCURSOR. In Version 12.2.0.1 everything works fine. We installed a fresh system with 19.8.0.0 and nothing was migrated.

We use a function to concatenate each value per row given by a query into a string as comma separated list. The query returns an CURSOR Object and the function has a SYS_REFCURSOR variable. Within the function the rows are fetched and concatenated.

CREATE OR REPLACE FUNCTION JOIN_1(P_CURSOR SYS_REFCURSOR, P_DEL VARCHAR2 := ',') RETURN VARCHAR2 IS
    V_VALUE  VARCHAR2(4000);
    V_RESULT VARCHAR2(4000);
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    LOOP
      FETCH P_CURSOR
        INTO V_VALUE;
      EXIT WHEN P_CURSOR%NOTFOUND;
      IF V_RESULT IS NOT NULL THEN
        V_RESULT := V_RESULT || P_DEL;
      END IF;
      V_RESULT := V_RESULT || V_VALUE;
    END LOOP;
    CLOSE P_CURSOR;
    RETURN V_RESULT;
  END JOIN_1;

Problem 1 "ORA-01031"

We use this query to illustrate our first problem:

SELECT * FROM (SELECT JOIN_1(CURSOR (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE ROWNUM < 15),  ',') GV FROM DUAL);    

We get an ORA-01031 error by accessing USER_TAB_COLUMNS!

We change the query the to

SELECT * FROM (SELECT JOIN_1(CURSOR (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE ROWNUM < 15),  ',') GV FROM DUAL);    

We have no Error! What is oracle doing here?

Problem 2 "ORA-01001" Now we use ALL_TAB_COLUMNS and we concatenate with a CASE WHEN expression values

SELECT CASE
         WHEN LENGTH(GV) = 1 THEN ' '
         ELSE GV
        END GV
  FROM (SELECT JOIN_1(CURSOR (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE ROWNUM < 15),  ',') GV FROM DUAL);

We get an ORA-01001. What is oracle doing here? Lost CURSOR?

We add the /*+ NO_MERGE */ option

SELECT CASE
         WHEN LENGTH(GV) = 1 THEN ' '
         ELSE GV
        END GV
  FROM (SELECT JOIN_1(CURSOR (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE ROWNUM < 15),  ',') GV FROM DUAL);

We have no Error! Why?

When we use the hint /*+ OPTIMIZER_FEATURES_ENABLE('12.2.0.1') */ all works fine.

We contacted the oracle support. We hope this BUG we be resolved soon.

I hope this is helpful for others.

Do you have similar problems?

Kind regards

Marcel