Cannot perform a dml operation inside a query ошибка

SQL> DROP TABLE t PURGE;
Table T dropped.

SQL> CREATE TABLE t (
  2      c1 INT GENERATED AS IDENTITY
  3  );
Table T created.

SQL> INSERT INTO t VALUES ( DEFAULT );
1 row inserted.

SQL> INSERT INTO t VALUES ( DEFAULT );
1 row inserted.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM t;
   C1
_____
    1
    2

SQL> CREATE OR REPLACE FUNCTION f RETURN INT AS
  2      retval INT;
  3  BEGIN
  4      INSERT INTO t VALUES ( DEFAULT ) RETURNING c1 INTO retval;
  5      RETURN retval;
  6  END f;
  7  /
Function F compiled

SQL> SELECT f FROM t;
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "DONGHUA.F", line 4

SQL> WITH x AS (
  2      SELECT /*+ materialize */ f FROM t
  3  )
  4  SELECT *
  5  FROM x;
   F
____
   4
   3

DROP TABLE t PURGE;

CREATE TABLE t (
    c1 INT GENERATED AS IDENTITY
);

INSERT INTO t VALUES ( DEFAULT );
INSERT INTO t VALUES ( DEFAULT );
COMMIT;

SELECT * FROM t;


CREATE OR REPLACE FUNCTION f RETURN INT AS
    retval INT;
BEGIN
    INSERT INTO t VALUES ( DEFAULT ) RETURNING c1 INTO retval;
    RETURN retval;
END f;
/

SELECT f FROM t;

WITH x AS (
    SELECT /*+ materialize */ f FROM t
)
SELECT *
FROM x;

У меня внутри package есть следующее, и это вызывает ошибку:

ORA-14551: cannot perform a DML operation inside a query

Код:

DECLARE 
    CURSOR F IS
        SELECT ROLE_ID 
        FROM ROLE 
        WHERE GROUP = 3 
        ORDER BY GROUP ASC;

BEGIN
FOR R IN F LOOP

DELETE FROM my_gtt_1;
COMMIT;

 INSERT INTO my_gtt_1
  ( USER, role, code, status )
(SELECT 
 trim(r.user), r.role, r.code, MAX(status_id)
FROM 
  table1 r, 
  tabl2 c
WHERE 
      r.role = R.role
  AND r.code IS NOT NULL
  AND c.group = 3
  GROUP BY 
  r.user, r.role, r.code);

  SELECT c.role,
                  c.subgroup,
                  c.subgroup_desc,
                  v_meb_cnt
                  INTO record_type
           FROM   ROLE c
           WHERE c.group = '3' and R.role = '19'
           GROUP BY c.role,c.subgroup,c.subgroup_desc;

  PIPE ROW (record_type);



END LOOP;

END;

Я вызываю такой пакет в одной из своих процедур …:

OPEN cv_1 for SELECT * FROM TABLE(my_package.my_func);

Как я могу избежать этой ошибки ORA-14551?

К вашему сведению, я не вставил весь код в цикл. В основном внутри цикла я ввожу данные в GTT, удаляю данные из GTT, а затем выбираю данные из GTT и добавляю их к курсору.

Смысл ошибки совершенно ясен: если мы вызываем функцию из оператора SELECT, она не может выполнять операторы DML, то есть INSERT, UPDATE или DELETE, или, действительно, к этому приходят операторы DDL.

Теперь опубликованный вами фрагмент кода содержит вызов PIPE ROW, поэтому очевидно, что вы вызываете его как SELECT * FROM TABLE (). Но он включает операторы DELETE и INSERT, поэтому явно не соответствует уровням чистоты, требуемым для функций в операторах SELECT.

Итак, вам нужно удалить эти операторы DML. Вы используете их для заполнения глобальной временной таблицы, но это хорошие новости. Вы не включили какой-либо код, который действительно использует GTT, поэтому трудно быть уверенным, но использование GTT часто не требуется. Более подробно мы можем предложить обходные пути.

Связано ли это с этот другой ваш вопрос? Если да, то следовали ли вы моему совету проверить тот ответ, который я дал на аналогичный вопрос?

Для полноты можно включить операторы DML и DDL в функцию, вызываемую в операторе SELECT. Обходной путь — использовать прагму AUTONOMOUS_TRANSACTION. Это редко бывает хорошей идеей и, конечно, не поможет в этом сценарии. Поскольку транзакция является автономной, вносимые ею изменения невидимы для вызывающей транзакции. В данном случае это означает, что функция не может видеть результат удаления или вставки в GTT.

Ошибка означает, что вы ВЫБИРАЕТЕ из функции, которая изменяет данные (УДАЛИТЬ, ВСТАВИТЬ в вашем случае).

Удалите операторы изменения данных из этой функции в отдельный SP, если вам нужна эта функция. (Наверное, я не понимаю из фрагмента кода, почему вы хотите удалить и вставить внутри цикла)

С тем, что бы использовать
 Insert into SomeTable (Id, ….. )  values ( GetNewId(«SomeText»), ….. )

где
create or replace function GetNewId(p_NAME in varchar) return number is
 Result number;
begin
 select SEQ_ND_ID.NextVal into Result from dual;

 insert into Tab_FOO(ID, NAME) values (Result, p_NAME);
— Естественно, ORA-14551: cannot perform a DML operation inside a query

 return(Result);
end GetNewId;

Читаю совет
   Myfunction looks like this

но я хочу именно
>> DML operation inside a query
например, insert into … select GetNewId(«SomeText»), остальные поля

Можно «вывернуться»?

Аудит не предлагайте, это не то.
Требование — заносить сквозные ID в таблицу, с комментариями. Причем, эта таблица первична. Нельзя никуда вставить новый ID? предварительно не занеся в нее.. FK-PK, короче

> Нельзя никуда вставить новый ID?

? = ,

Подозреваю, что ерунду хотят.
Но такое требование, не знаю зачем это надо

>  Insert into SomeTable (Id, ….. )  values ( GetNewId(«SomeText»),
>  ….. )

values (select GetNewId(«SomeText») from dual,

прагма афтономус транзакшон

внутри функции

> прагма афтономус транзакшон

так заработало.
+ ниже

> values (select GetNewId(«SomeText») from dual,

Раньше не работало, было так: Вариант1
create or replace function MF_ARGUS.NewND(p_NAME in varchar) return number  is
 Result number;
begin
 select MF_ARGUS.SEQ_ND_ID.NextVal into Result from dual;
 insert into MF_ARGUS.tabfoo values (result, p_NAME);
 return(Result);
end NewND;

преписал как: Вариант2
create or replace function MF_ARGUS.NewND(p_NAME in varchar) return number
 as PRAGMA AUTONOMOUS_TRANSACTION; Result number;
begin
 select MF_ARGUS.SEQ_ND_ID.NextVal into Result from dual;
 insert into MF_ARGUS.tabfoo values (result, p_NAME);
 commit;
 return(Result);
end NewND;

как сказал выше, Вариант 2 заработал..
НО,
вернул назад(случайно, так получилось, не в том окне нажал)

Сейчас откатилось к варианту1, еще раз привожу
create or replace function MF_ARGUS.NewND(p_NAME in varchar) return number
 is Result number;
begin
 select MF_ARGUS.SEQ_ND_ID.NextVal into Result from dual;
 insert into MF_ARGUS.tabfoo values (result, p_NAME);
 return(Result);
end NewND;

Пробую
insert into mf_argus.tabfoo2 (FOO, NAME)
values (MF_ARGUS.NewND(«test»), «test2»);
Все ok..

insert into mf_argus.tabfoo2 F (FOO, NAME)
select MF_ARGUS.NewND(«test2»), T.NAME from applic.T_TOWN T;
Все ok..

А раньше не работало. Я ж не придумал ошибку ORA-14551:, она была..