Блог

у нас есть несколько проблем с курсорами и SYS_REFCURSOR. В версии 12.2.0.1 все работает нормально. Мы установили новую систему с 19.8.0.0, и ничего не было перенесено.

Мы используем функцию для объединения каждого значения в строке, заданной запросом, в строку в виде списка, разделенного запятыми. Запрос возвращает объект CURSOR, а функция имеет переменную SYS_REFCURSOR. Внутри функции строки извлекаются и объединяются.

 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;
  

Проблема 1 «ORA-01031»

Мы используем этот запрос, чтобы проиллюстрировать нашу первую проблему:

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

Мы получаем ошибку ORA-01031 при доступе к USER_TAB_COLUMNS!

Мы меняем запрос на

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

У нас нет ошибки! Что здесь делает oracle?

Проблема 2 «ORA-01001» Теперь мы используем ALL_TAB_COLUMNS и объединяем со СЛУЧАЕМ, КОГДА значения выражения

 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);
  

Мы получаем ORA-01001. Что здесь делает oracle? Потерян КУРСОР?

Мы добавляем опцию /* NO_MERGE */

 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);
  

У нас нет ошибки! Почему?

Когда мы используем подсказку /* OPTIMIZER_FEATURES_ENABLE(‘12.2.0.1’) */, все работает нормально.

Мы связались со службой поддержки oracle. Мы надеемся, что эта ОШИБКА будет устранена в ближайшее время.

Я надеюсь, что это полезно для других.

У вас есть похожие проблемы?

С уважением

Марсель