Почему не могу вставить новую запись из запроса с функцией объявленной в WITH клаузе (CTE)?

Question

Есть такой CTE запрос, который работает:

with function f return number is
begin
    return 1;
end;
t (id, name) as (select f, 'aaa' from dual)
select * from t
/

        ID NAM
---------- ---
         1 aaa

Когда пытаюсь вставить новую запись, используя запрос выше:

create table t1 (id int, name varchar2 (8))
/

insert into t1
    with function f return number is
    begin
        return 1;
    end;
    t (id, name) as (select f, 'aaa' from dual)
    select * from t

/

То получаю следующую ошибку:

Error report -
SQL Error: ORA-32034: unsupported use of WITH clause
32034. 00000 -  "unsupported use of WITH clause"
*Cause:    Inproper use of WITH clause because one of the following two reasons
           1. nesting of WITH clause within WITH clause not supported yet
           2. For a set query, WITH clause can't be specified for a branch.
           3. WITH clause cannot be specified within parenthesis.
*Action:   correct query and retry

Подсказки в тексте ошибки не помогают понять, что же можно исправить.

Это вообще не поддерживается, или как-то можно исправить запрос?

Answer 1

Согласно документации with_clause:

If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.

The WITH_PLSQL hint only enables you to specify the WITH plsql_declarations clause within the statement. It is not an optimizer hint.

Если запрос, содержащий WITH с PL/SQL декларацией, не является запросом самого верхнего уровня, то запрос верхнего уровня должен содержать подсказку WITH_PLSQL:

insert /*+ with_plsql */ into t1
   with function f return number is
   begin
       return 1;
   end;
   t (id, name) as (select f, 'aaa' from dual)
   select * from t
/

1 row inserted.

В PL/SQL блоке эта конструкция пока возможна только как динамический запрос:

begin 
    execute immediate q'[insert /*+ with_plsql*/ into t1
        with function f return number is
        begin
            return 1;
        end;
        t (id, name) as (select f, 'aaa' from dual)
        select * from t
    ]';
end;
/

PL/SQL procedure successfully completed.

БЛОГ НА HUSL

Почему не могу вставить новую запись из запроса с функцией объявленной в WITH клаузе (CTE)?

Ответы (1 шт):